管理数据库连接不得不注意的地方

对于Asp.Net应用程序，当面对大量用户的请求，我们的服务器是否有能力从容应对，一方面取决于我们服务器硬件设备的处理能力，更重要的一方面则取决于数据库连接的管理是否合理。（这里代码是否合理，暂不考虑，假定它合理。）

 

本文主要讨论数据库连接的管理，即连接池的管理需要注意的问题，并通过代码实例进行分析。

 

注意一：

连接池中包含打开的可重用的数据库连接。在同一时刻同一应用程序域中可以有多个连接池。

一个连接池是通过一个唯一的连接字符串来创建。连接池是根据第一次请求数据库连接的连接字符串来创建的，当另外一个不同的连接字符串请求数据库连接时，将创建另一个连接池。因此一个连接字符中对应一个连接池而不是一个数据库对应一个连接池

 

//代码1

  private string strCon = "Data Source=192.168.24.251;Initial Catalog=Test;Persist Security Info=True;User id=sa;Password=123456;";

     SqlConnection sqlCon = new SqlConnection(strCon);

     sqlCon.Open();

//代码2

 private string strCon = "Data Source=192.168.24.251;Initial Catalog=Test;Persist Security Info=True;User id=“user1”;Password=123456;";

     SqlConnection sqlCon = new SqlConnection(strCon);

     sqlCon.Open();

//代码3

 private string strCon = "Data Source=192.168.24.251;Initial Catalog=Test;Persist Security Info=True;User id=sa;Password=123456;";

     SqlConnection sqlCon = new SqlConnection(strCon);

     sqlCon.Open();

 

 

如果分别运行上述三段代码，会建立几个连接池？答案是2个，代码1新建连接池，代码3与代码1的连接字符串一样，不再新建，与代码1共享一个连接池。代码2与代码1，代码3的连接字符串不一样，则会新建一个连接池

 

所以，为了保证某些连接对象属于一个连接池，连接字符串不能有任何变化，包括大小写，包括空格，都不能有任何变化。  

 

注意二：

ADO.Net默认打开了连接池，不需要再进行手工配置，但也要有相应的连接字符串配合,才能真正提高程序效率。下面谈一下最重要的几个参数。

 

看下面这个连接字符串

"Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;pooling=true;max pool size=1000;min pool size=200;"

 

连接池的工作原理可以用三句话简单概括：1、如果有未用连接可用，返回该连接；2、如果池中连接都已用完，创建一个新连接添加到池中；3、如果池中连接已达到最大连接数，请求进入等待队列直到有空闲连接可用

了解了基本原理我们很容易理解这些参数：

       pooling 表示是否打开连接池，默认为打开，关掉时需要 pooling = false；

    min pool size 表示连接池最少保存几个连接对象，连接池新建的时候，自动创建的空闲连接；默认为0

    max pool size 表示连接池最多保存几个连接对象，最大值不能超过32767，也不能小于最小值或者0。默认为100

 

还有一个Connect Timeout参数，当连接被返回到池时，将其创建时间与当前时间作比较，如果时间长度（以秒为单位）超出了由 Connection Lifetime 指定的值，该连接就会被销毁。Connection Lifetime是用于集群数据库环境下很有用，用于强制执行运行中的服务器和刚置于联机状态的服务器之间的负载平衡。小规模系统并不常用。

 

怎么理解？

例如一个应用系统的中间层访问一个由3台服务器组成的集群数据库，该系统运行一段时间后发现数据库的负荷太大而需要增加第4台数据库服务器。如果不设置Connection Lifetime，你可能会发现新增加的服务器很久都得不到连接而原来3台服务器的负荷一点都没减少。这是因为中间层的连接一直都不会销毁而建立新的连接的可能性很小（除非出现增加服务器之后数据库的并发访问量超过增加前的并发最大值）。

    注意：Connection Lifetime很容易让人产生误解。不要认为Connection Lifetime决定了一个连接的生存时间。因为只有连接被释放回连接池的时刻（Close连接之后）才会检查Connection Lifetime值是否达到而决定是否销毁连接，而连接在空闲或者正在使用的时候并不会检查Connection Lifetime。这意味着绝大多数情况下连接从建立到销毁经过的时间比Connection Lifetime大。另外，如果Min Pool Size为N (N > 0),那么连接池里有N个连接不受Connection Lifetime影响。这N个连接会一直在池里直到连接池被销毁。

    （2）当发现某个连接对应的“物理连接”断开（这种连接称为“死连接”），例如数据库已经被shutdown、网络中断、SQL Server的连接进程被kill、Oracle的连接会话被kill，该连接被销毁。“死连接”出现后不是立刻被发现，直到该连接被占用来访问数据库的时候才会被发现。

    注意：如果执行Open()方法时候Data Provider只需从连接池取出已有的连接，那么Open()并没有访问数据库，所以这时候“死连接”还不能被发现。

 

注意三：在利用连接池功能时，调用SqlConnection.close()方法关闭连接。如果不显式地关闭连接，当前正使用的连接就不会被放入到连接池中，虽然最终Ado.net会回收这些未关闭的连接，不过那可能需要很长的时间，并且无法确定连接到底会在什么时候回收。

 

代码4：

//常规做法

    SqlConnection con = new SqlConnection(strConn);

    try

    {

       con.Open();

       //进行各种数据库操作

    }

    catch(Exception ex){ //处理异常 }

    finally

    {

       con.Close();

       con.Dispose();

    }

 

 

    这个过程很繁琐，每次都要在操作完毕后保证连接对象的关闭和资源释放。在打开连接池特性以后，finally 中的内容，其实是将连接对象的状态置为关闭，然后放回到连接池中。既然系统知道要放回连接池，那有没有什么更好的方法呢？

 

//代码5

    using(SqlConnection con = new SqlConnection(cs))

    {

       try

       {

          con.Open();

          //进行各种数据库操作

       }

        catch(Exception ex){//处理异常 }

    }

 

    .Net 中的 using 语句，不光能导入命名空间，还能在程序体内，局部使用某个对象。像上边代码，con 的作用域只有 using 对应的大括弧这么大。using 可以在对象作用域结束时，自动调用 con.Dispose()将对象释放，所以以上代码中，没有 con.Close() 和 con.Dispose()，同样可以释放资源，放回连接池，省了 finally 和手工关闭的麻烦。

但同时需要注意，using 既然是在结束作用域时是自动调用对象的 Dispose()方法，那就是说不是什么类型的对象都可以用 using 的方式自动释放，必须要实现 IDispose 接口。

而且再使用事务的时候，这种做法也并可取，如果在该作用域把连接释放，那当进行事务操作的时候，必然会出问题，因为当前连接已经释放了。

 

我们通过一个实例来验证一下三个注意项，解析请参见代码注释。

[csharp]  view plain copy print ?

1. //代码6  
2. using System;  
3. using System.Collections.Generic;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Data.SqlClient;  
7. using System.Data;  
8.   
9. namespace ConsoleApplication1  
10. {  
11.     class Program  
12.     {  
13.         static void Main(string[] args)  
14.         {  
15.             int maxCount = 32767;  
16.             List<SqlConnection> collection = new List<SqlConnection>(); //用于保存sqlConnection对象的列表  
17.             for (int i = 1; i < maxCount; i++)  
18.             {  
19.   
20.                 SqlConnection sqlConnection = null;  
21.                 try  
22.                 {  
23.   
24.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));//输出创建过的连接数  
25.                     sqlConnection = new SqlConnectionManage().GetConnection(); //获得连接  
26.                     collection.Add(sqlConnection); //保存sqlConnection对象，防止其被回收  
27.                     Console.WriteLine(string.Format("成功"));//输出创建过的连接数  
28.                 }  
29.                 catch (Exception ex)  
30.                 {  
31.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));  
32.                     Console.WriteLine(string.Format("失败"));  
33.                     Console.WriteLine(string.Format("失败原因"));  
34.                     Console.WriteLine(ex.ToString());  //输出异常信息  
35.   
36.                     i--; //重复创建该连接  
37.                 }  
38.                 finally  
39.                 {  
40.                     //sqlConnection.Close(); //关闭连接  
41.                 }  
42.   
43.             }  
44.   
45.         }  
46.     }  
47.   
48.   
49.     /// <summary>  
50.     /// 数据库连接管理类  
51.     /// </summary>  
52.     public class SqlConnectionManage  
53.     {  
54.         /// <summary>  
55.         /// 数据库连接字符串  
56.         /// </summary>  
57.         private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;";  //连接池默认使用，默认最大连接数100          
58. //private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;pooling=true;max pool size=1000;min pool size=100;";  
59.   
60.         /// <summary>  
61.         /// 获取并打开连接  
62.         /// </summary>  
63.         /// <returns></returns>  
64.         public SqlConnection GetConnection()  
65.         {  
66.             SqlConnection sqlCon = new SqlConnection(strCon);  
67.   
68.             if (sqlCon.State == ConnectionState.Closed)  
69.             {  
70.                 sqlCon.Open();  
71.             }  
72.   
73.             return sqlCon;  
74.         }  
75.   
76.         /// <summary>  
77.         /// 关闭数据库连接  
78.         /// </summary>  
79.         public void Close(SqlConnection sqlCon)  
80.         {  
81.             if (sqlCon.State == ConnectionState.Open)  
82.             {  
83.                 sqlCon.Close();  
84.             }  
85.         }  
86.   
87.     }  
88. }  

 

上述输出，可以看出ADO.NET默认开启数据库连接，最大连接数默认100

 

[csharp]  view plain copy print ?

1. //代码7,使用关闭连接  
2. using System;  
3. using System.Collections.Generic;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Data.SqlClient;  
7. using System.Data;  
8.   
9. namespace ConsoleApplication1  
10. {  
11.     class Program  
12.     {  
13.         static void Main(string[] args)  
14.         {  
15.             int maxCount = 32767;  
16.             List<SqlConnection> collection = new List<SqlConnection>(); //用于保存sqlConnection对象的列表  
17.             for (int i = 1; i < maxCount; i++)  
18.             {  
19.   
20.                 SqlConnection sqlConnection = null;  
21.                 try  
22.                 {  
23.   
24.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));//输出创建过的连接数  
25.                     sqlConnection = new SqlConnectionManage().GetConnection(); //获得连接  
26.                     collection.Add(sqlConnection); //保存sqlConnection对象，防止其被回收  
27.                     Console.WriteLine(string.Format("成功"));//输出创建过的连接数  
28.                 }  
29.                 catch (Exception ex)  
30.                 {  
31.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));  
32.                     Console.WriteLine(string.Format("失败"));  
33.                     Console.WriteLine(string.Format("失败原因"));  
34.                     Console.WriteLine(ex.ToString());  //输出异常信息  
35.   
36.                     i--; //重复创建该连接  
37.                 }  
38.                 finally  
39.                 {  
40.                     sqlConnection.Close(); //关闭连接  
41.                 }  
42.   
43.             }  
44.   
45.         }  
46.     }  
47.   
48.   
49.     /// <summary>  
50.     /// 数据库连接管理类  
51.     /// </summary>  
52.     public class SqlConnectionManage  
53.     {  
54.         /// <summary>  
55.         /// 数据库连接字符串  
56.         /// </summary>  
57.         private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;";  
58.         //private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;pooling=true;max pool size=100;min pool size=10;";  
59.   
60.         /// <summary>  
61.         /// 获取并打开连接  
62.         /// </summary>  
63.         /// <returns></returns>  
64.         public SqlConnection GetConnection()  
65.         {  
66.             SqlConnection sqlCon = new SqlConnection(strCon);  
67.   
68.             if (sqlCon.State == ConnectionState.Closed)  
69.             {  
70.                 sqlCon.Open();  
71.             }  
72.   
73.             return sqlCon;  
74.         }  
75.   
76.         /// <summary>  
77.         /// 关闭数据库连接  
78.         /// </summary>  
79.         public void Close(SqlConnection sqlCon)  
80.         {  
81.             if (sqlCon.State == ConnectionState.Open)  
82.             {  
83.                 sqlCon.Close();  
84.             }  
85.         }  
86.   
87.     }  
88. }  

 

上述输出可以看出，如果连接及时关闭，我们可以不断创建连接。如果是单用户操作，那么连接池中就仅仅存在一个连接。

 

[csharp]  view plain copy print ?

1. //代码8  ，显示声明连接池参数  
2. using System;  
3. using System.Collections.Generic;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Data.SqlClient;  
7. using System.Data;  
8.   
9. namespace ConsoleApplication1  
10. {  
11.     class Program  
12.     {  
13.         static void Main(string[] args)  
14.         {  
15.             int maxCount = 32767;  
16.             List<SqlConnection> collection = new List<SqlConnection>(); //用于保存sqlConnection对象的列表  
17.             for (int i = 1; i < maxCount; i++)  
18.             {  
19.   
20.                 SqlConnection sqlConnection = null;  
21.                 try  
22.                 {  
23.   
24.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));//输出创建过的连接数  
25.                     sqlConnection = new SqlConnectionManage().GetConnection(); //获得连接  
26.                     collection.Add(sqlConnection); //保存sqlConnection对象，防止其被回收  
27.                     Console.WriteLine(string.Format("成功"));//输出创建过的连接数  
28.                 }  
29.                 catch (Exception ex)  
30.                 {  
31.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));  
32.                     Console.WriteLine(string.Format("失败"));  
33.                     Console.WriteLine(string.Format("失败原因"));  
34.                     Console.WriteLine(ex.ToString());  //输出异常信息  
35.   
36.                     i--; //重复创建该连接  
37.                 }  
38.                 finally  
39.                 {  
40.                     //sqlConnection.Close(); //关闭连接  
41.                      
42.                 }  
43.   
44.             }  
45.   
46.         }  
47.     }  
48.   
49.   
50.     /// <summary>  
51.     /// 数据库连接管理类  
52.     /// </summary>  
53.     public class SqlConnectionManage  
54.     {  
55.         /// <summary>  
56.         /// 数据库连接字符串  
57.         /// </summary>  
58.         //private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;";  
59.         private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;pooling=true;max pool size=1000;min pool size=100;";  
60.   
61.         /// <summary>  
62.         /// 获取并打开连接  
63.         /// </summary>  
64.         /// <returns></returns>  
65.         public SqlConnection GetConnection()  
66.         {  
67.             SqlConnection sqlCon = new SqlConnection(strCon);  
68.   
69.             if (sqlCon.State == ConnectionState.Closed)  
70.             {  
71.                 sqlCon.Open();  
72.             }  
73.   
74.             return sqlCon;  
75.         }  
76.   
77.         /// <summary>  
78.         /// 关闭数据库连接  
79.         /// </summary>  
80.         public void Close(SqlConnection sqlCon)  
81.         {  
82.             if (sqlCon.State == ConnectionState.Open)  
83.             {  
84.                 sqlCon.Close();  
85.             }  
86.         }  
87.   
88.     }  
89. }  

 

[csharp]  view plain copy print ?

1. 代码9 //如果不保存连接对象，那么会发生什么  
2.   
3. using System;  
4. using System.Collections.Generic;  
5. using System.Linq;  
6. using System.Text;  
7. using System.Data.SqlClient;  
8. using System.Data;  
9.   
10. namespace ConsoleApplication1  
11. {  
12.     class Program  
13.     {  
14.         static void Main(string[] args)  
15.         {  
16.             int maxCount = 32767;  
17.             //List<SqlConnection> collection = new List<SqlConnection>(); //用于保存sqlConnection对象的列表  
18.             for (int i = 1; i < maxCount; i++)  
19.             {  
20.   
21.                 SqlConnection sqlConnection = null;  
22.                 try  
23.                 {  
24.   
25.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));//输出创建过的连接数  
26.                     sqlConnection = new SqlConnectionManage().GetConnection(); //获得连接  
27.                     //collection.Add(sqlConnection); //保存sqlConnection对象，防止其被回收  
28.                     Console.WriteLine(string.Format("成功"));//输出创建过的连接数  
29.                 }  
30.                 catch (Exception ex)  
31.                 {  
32.                     Console.WriteLine(string.Format("创建连接对象：第{0}个", i));  
33.                     Console.WriteLine(string.Format("失败"));  
34.                     Console.WriteLine(string.Format("失败原因"));  
35.                     Console.WriteLine(ex.ToString());  //输出异常信息  
36.   
37.                     i--; //重复创建该连接  
38.                 }  
39.                 finally  
40.                 {  
41.                     //sqlConnection.Close(); //关闭连接  
42.                      
43.                 }  
44.   
45.             }  
46.   
47.         }  
48.     }  
49.   
50.   
51.     /// <summary>  
52.     /// 数据库连接管理类  
53.     /// </summary>  
54.     public class SqlConnectionManage  
55.     {  
56.         /// <summary>  
57.         /// 数据库连接字符串  
58.         /// </summary>  
59.         private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;";  
60.         //private string strCon = "Data Source=192.168.24.251;Initial Catalog=BasicDataSystem;Persist Security Info=True;User id=sa;Password=123456;pooling=true;max pool size=1000;min pool size=100;";  
61.   
62.         /// <summary>  
63.         /// 获取并打开连接  
64.         /// </summary>  
65.         /// <returns></returns>  
66.         public SqlConnection GetConnection()  
67.         {  
68.             SqlConnection sqlCon = new SqlConnection(strCon);  
69.   
70.             if (sqlCon.State == ConnectionState.Closed)  
71.             {  
72.                 sqlCon.Open();  
73.             }  
74.   
75.             return sqlCon;  
76.         }  
77.   
78.         /// <summary>  
79.         /// 关闭数据库连接  
80.         /// </summary>  
81.         public void Close(SqlConnection sqlCon)  
82.         {  
83.             if (sqlCon.State == ConnectionState.Open)  
84.             {  
85.                 sqlCon.Close();  
86.             }  
87.         }  
88.   
89.     }  
90. }  

 

//如果不保存连接对象，那么默认情况下，我们应只能创建100个连接，但是创建了125个，为什么？因为.NET垃圾回收机制在起作用，但是它回收的速度远不如我们创建的速度。实际上存在的连接数依然是100个。

正验证了注意三，如果不显式地关闭连接，当前正使用的连接就不会被放入到连接池中，虽然最终Ado.net会回收这些未关闭的连接，不过那可能需要很长的时间，并且无法确定连接到底会在什么时候回收。

 

还有一个很常见的问题，我们该如何查看我们的连接数呢？

  数据库服务器是连接对象的目的地，通过服务器状态的查询，就能够看到连接池对服务器的访问信息。拿 SQL Server 2005 为例，执行 sp_who 或者 sp_who2 两个系统存储过程，就可以看到所有访问数据库的连接的列表。比如 min pool size 设置为 5，表示连接池中至少要维护 5 个数据库对象，程序启动以后，执行 exec sp_who2 就可以发现，数据库中有 5 个对应数据库名的连接，如果没有操作，其状态就会处于 sleeping 状态。

 

执行代码9，默认会查询到100个连接

 

还有一点说明，如果设置连接池连接数非常大，但服务器内存不够，那么如果同时打开大量连接，会报异常：

OutOfMemoryException 内存耗尽。这个很容易理解，就不过多演示了。

 

总之，如果希望Asp.net应用程序具有处理大量用户并发请求的能力，那么任何时候都不要浪费“21世纪最宝贵的财富”——数据库连接。