第7章 ADO.NET进阶

7.1连接池

7.1.1什么是连接池

建立一个数据库连接是一件非常耗时（消耗时间）耗力（消耗资源）的事情。之所以会这样，是因为连接到数据库服务器需要经历几个漫长的过程:建立物理通道（例如套接字或命名管道），与服务器进行初次握手，分析连接字符串信息，由服务器对连接进行身份验证，运行检查以便在当前事务中登记等等。我们先不管为什么会有这样的机制，存在总是有它的道理。既然新建一条连接如此痛苦，那么为什么不重复利用已有的连接呢？
实际上，ADO.NET已经为我们提供了名为连接池的优化方法。连接池就是这样一个容器：它存放了一定数量的与数据库服务器的物理连接。因此，当我们需要连接数据库服务器的时候，只需去池（容器）中取出一条空闲的连接，而不是新建一条连接。这样的话，我们就可以大大减少连接数据库的开销，从而提高了应用程序的性能。

7.1.2连接池的工作原理

7.1.2.1 创建连接池

需要说明的是，连接池是具有类别区分的。也就是说，同一个时刻同一应用程序域可以有多个不同类型的连接池。那么，连接池是如何标识区分的？细致的讲，是由进程、应用程序域、连接字符串以及windows标识（在使用集成的安全性时）共同组成签名来标识区分的。但对于同一应用程序域来说，一般只由连接字符串来标识区分。当打开一条连接时，如果该条连接的类型签名与现有的连接池类型不匹配，则创建一个新的连接池。反之，则不创建新的连接池。
一个典型的创建连接的实例：

//创建连接对象1using (SqlConnection conn1 =new SqlConnection(  "Data Source=bruceou-PC;Initial Catalog=MyData;Integrated Security=True"))    {            conn1.Open();       }//创建连接对象2using (SqlConnection conn2 =new SqlConnection(  "Data Source=bruceou-PC;Initial Catalog=MyDatabaseNew;Integrated Security=True"))   {           conn2.Open();      }//创建连接对象3using (SqlConnection conn3 =new SqlConnection(  "Data Source=bruceou-PC;Initial Catalog=MyData;Integrated Security=True"))  {            conn3.Open();      } 

上面实例中，我创建了三个SqlConnection对象，但是管理时只需要两个连接池。细心的朋友，可能早已发现conn1与conn3的连接字符串相同，所以可以共享一个连接池，而conn2与conn1与conn3不同，所以需要创建新的连接池。

7.1.2.2 分配空闲连接

当用户创建连接请求或者说调用Connection对象的Open时，连接池管理器首先需要根据连接请求的类型签名找到匹配类型的连接池，然后尽力分配一条空闲连接。具体情况如下：
 如果池中有空闲连接可用，返回该连接。
 如果池中连接都已用完，创建一个新连接添加到池中。
 如果池中连接已达到最大连接数，请求进入等待队列直到有空闲连接可用。

7.1.2.3 移除无效连接

无效连接，即不能正确连接到数据库服务器的连接。对于连接池来说，存储的与数据库服务器的连接的数量是有限的。因此，对于无效连接，如果如不及时移除，将会浪费连接池的空间。其实你不用担心，连接池管理器已经很好的为我们处理了这些问题。如果连接长时间空闲，或检测到与服务器的连接已断开，连接池管理器会将该连接从池中移除。

7.1.2.4回收使用完的连接

当我们使用完一条连接时，应当及时关闭或释放连接，以便连接可以返回池中重复利用。我们可以通过Connection对象的Close或Dispose方法，也可以通过C#的using语句来关闭连接。

7.1.3连接池的重要属性

连接池的行为可以通过连接字符串来控制，主要包括四个重要的属性：
 Connection Timeout：连接请求等待超时时间。默认为15秒，单位为秒。
 Max Pool Size: 连接池中最大连接数。默认为100。
 Min Pool Size: 连接池中最小连接数。默认为0。
 Pooling: 是否启用连接池。ADO.NET默认是启用连接池的，因此，你需要手动设置Pooling=false来禁用连接池。
还是看一个实例来理解连接池的属性吧。代码如下：

using System;using System.Data.SqlClient;using System.Diagnostics;using System.Linq;using System.Text;using System.Threading.Tasks;namespace 连接池{    class Program    {        static void Main(string[] args)        {            //默认是开启连接池，关闭连接池            string str = "Data Source=bruceou-PC;Initial Catalog=MyData;Integrated Security=True;Pooling=false";            //开启连接池            SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();            connStr.DataSource = " BRUCEOU-PC";            connStr.InitialCatalog = "MyData";            connStr.IntegratedSecurity = true;            connStr.Pooling = true; //开启连接池            //connStr.MinPoolSize = 0; //设置最小连接数为0            //connStr.MaxPoolSize = 50; //设置最大连接数为50                         //connStr.ConnectTimeout = 10; //设置超时时间为10秒            int i = 0;            Stopwatch sw = new Stopwatch();            sw.Start();            while (i < 1000)            {                using (SqlConnection conn1 = new SqlConnection(str))                {                    conn1.Open();                }                i++;            }            sw.Stop();            Console.WriteLine(sw.Elapsed.Milliseconds);            sw.Reset();            sw.Restart();            i = 0;            while (i < 1000)            {                using (SqlConnection conn2 = new SqlConnection(connStr.ConnectionString))                {                    conn2.Open();                }                i++;            }            sw.Stop();            Console.WriteLine(sw.Elapsed.Milliseconds);            Console.ReadKey();        }    }}

代码参看【附件/01_连接池】，运行效果如下。

图1

7.1.4连接池异常与处理方法

当用户打开一个连接而没有正确或者及时的关闭时，经常会引发“连接泄露”问题。泄露的连接，会一直保持打开状态，直到调用Dispose方法，垃圾回收器（GC）才关闭和释放连接。与ADO不同，ADO.NET需要手动的关闭使用完的连接。一个重要的误区是：当连接对象超出局部作用域范围时，就会关闭连接。实际上，当超出作用域时，释放的只是连接对象而非连接资源。好吧，还是先看看一个实例吧。

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;namespace ConnectionPool{    class Program    {        static void Main(string[] args)        {            SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();connStr.DataSource = " BRUCEOU -PC";connStr.InitialCatalog = "MyData";connStr.IntegratedSecurity = true;            connStr.MaxPoolSize = 5;//设置最大连接池为5            connStr.ConnectTimeout = 1;//设置超时时间为1秒            SqlConnection conn = null;            for (int i = 1; i <= 100; ++i)            {                conn = new SqlConnection(connStr.ConnectionString);                try                {                    conn.Open();                    Console.WriteLine("Connection{0} is linked",i);                }                catch(Exception ex)                {                    Console.WriteLine("\n异常信息:\n{0}",ex.Message);                    break;                }            }            Console.Read();        }    }}

为了使结果更明显，我特地将最大连接数设置为5，超时时间为1秒。运行后，很快得到以下结果。

图2
从上面的结果我们很明显的知道，连接出现了异常。由于连接池中连接数量已经达到了最大数并且没有空闲的连接，因此需要等待连接直到超时。当超过超时时间时，就出现了上述的连接异常。因此，我必须再次强调，使用完的连接应当尽快的正确的关闭和释放。
代码参看【附件/02_连接池异常】

7.2 防止SQL注入

 SQL注入简介
SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库。
 SQL注入攻击的总体思路
1.寻找到SQL注入的位置；
2.判断服务器类型和后台数据库类型；
3.针对不通的服务器和数据库特点进行SQL注入攻击。
 SQL注入攻击实例
比如在一个登录界面，要求输入用户名和密码，可以这样输入实现免帐号登录：
用户名： ‘or 1 = 1 –
密 码：
点登陆,如若没有做特殊处理,那么这个非法用户就很得意的登陆进去了.(当然现在的有些语言的数据库API已经处理了这些问题)
这是为什么呢? 下面我们分析一下：
从理论上说，后台认证程序中会有如下的SQL语句：

String sql = "select * from user_table where username=' "+userName+" ' and password=' "+password+" '";

当输入了上面的用户名和密码，上面的SQL语句变成：

SELECT * FROM user_table WHERE username='’or 1 = 1 -- and password='’

分析SQL语句：
条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；

然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，用户轻易骗过系统，获取合法身份。
这还是比较温柔的，如果是执行

SELECT * FROM user_table WHEREusername='' ;DROP DATABASE (DB Name) --' and password=''….

其后果可想而知…
 应对方法
【方法一】
在Web.config文件中， < appSettings>下面增加一个标签：如下
代码如下：

< appSettings>< add key="safeParameters" value="OrderID-int32,CustomerEmail-email,ShippingZipcode-USzip" />< /appSettings>

其中key是 < saveParameters>后面的值为”OrderId-int32”等，其中”-“前面表示参数的名称比如：OrderId,后面的int32表示数据类型。
【方法二】
在Global.asax中增加下面一段：
代码如下：

protected void Application_BeginRequest（Object sender, EventArgs e）{String[] safeParameters = System.Configuration.ConfigurationSettings.AppSettings["safeParameters"].ToString（）。Split（','）；　　for（int i= 0 ;i < safeParameters.Length; i++）{　　String parameterName = safeParameters[i].Split（'-'）[0];　　String parameterType = safeParameters[i].Split（'-'）[1];　　isValidParameter（parameterName, parameterType）；　　}}public void isValidParameter（string parameterName, string parameterType）{　　string parameterValue = Request.QueryString[parameterName];　　if（parameterValue == null） return;　　if（parameterType.Equals（"int32"））{　　  if（！parameterCheck.isInt（parameterValue）） Response.Redirect（"parameterError.aspx"）；　　}　　else if （parameterType.Equals（"USzip"））{if（！parameterCheck.isUSZip（parameterValue）） Response.Redirect（"parameterError.aspx"）；　　}　　else if （parameterType.Equals（"email"））{　　  if（！parameterCheck.isEmail（parameterValue）） Response.Redirect（"parameterError.aspx"）；　　}}

【方法三】
　　使用字符串过滤类。
代码参看【附件/03_登陆】。

7.3导出导入数据

7.3.1导出数据

 图形界面导出数据
【第一步】右击鼠标，【任务】->【导出数据】，进入欢迎界面单击【下一步】即可。

图3
【第二步】选择数据源。

图4
【第三步】选择目标。

图5
【第四步】

图6

图7

图8
单击【下一步】保存即可。
 SQL语言导出数据
代码如下：

7.3.2导入数据

 图形界面导出数据
【第一步】右击鼠标，【任务】->【导入数据】，进入欢迎界面单击【下一步】即可。

图9
【第二步】选择数据源。

图10
【第三步】选择目标。

图11
【第四步】

图12

图13

图14
单击【完成】即可。
 SQL语言导入数据

using System;using System.Collections.Generic;using System.IO;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace _04_导入数据{    class Program    {        static void Main(string[] args)        {            string str = "Data Source=bruceou-PC;Initial Catalog=MyData;Integrated Security=True";            using (StreamReader reader=new StreamReader("1.txt"))            {                string line= reader.ReadLine();//第一行列名读完了,不要了                using (SqlConnection con=new SqlConnection(str))                {                    con.Open();                    string sql = "insert into UserLogin values(@UserName, @UserPwd)";                    SqlParameter[] ps = {                              //告诉数据库 我的参数中存的值要以nvarchar类型存到表中                              new SqlParameter("@UserName", System.Data.SqlDbType.NChar),                              new SqlParameter("@UserPwd", System.Data.SqlDbType.NChar)                                        };                    using (SqlCommand cmd=new SqlCommand(sql,con))                    {                        cmd.Parameters.AddRange(ps);                        while ((line = reader.ReadLine()) != null)                        {                            string[] txts = line.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);                            //把参数用什么值替换                            ps[0].Value = txts[1];//名字                            ps[1].Value = txts[2];                            cmd.ExecuteNonQuery();                          }                    }                }            }            Console.WriteLine("导入数据成功！");            Console.ReadKey();        }    }}

代码参考【附件/05_导入数据】

本章参考代码：

http://download.csdn.net/download/u013162035/10110394

【注意】本章实现的语言是C#，还涉及到windform等，博主默认笔者已经有这些基础了，博主使用的开发环境是win7+SQL Server2012+VS2012，要是现实本章的代码，需要笔者根据自己的情况配置相应的开发环境，有任何问题请@博主。