.NET ADO.NET和数据库的连接、 数据库连接池

数据库连接一般都被认为是一个性能成本相对较大的动作，所以针对数据库连接以及读写的优化往往是系统优化的关键点。数据库连接池就是一个非常重要的优化机制。

（1）数据库连接池的基本概念

数据库连接池，顾名思义就是一个存储数据库连接的缓冲池，由于连接和断开一个数据库的开销很大（想想经典的TCP三次握手和四次挥手），反复连接和断开数据库对于系统的性能影响将会非常严重。而在.NET程序中，有时候是无法预测下一次数据库访问的需求何时到来，所以通常的做法就是在使用完一个连接后就立即关闭它，这就需要ADO.NET的内部机制来维护这个访问池。

下图展示了数据库连接池的机制，在该机制中，当一个用户新申请了一个数据库连接时，当数据库池内连接匹配的情况下，用户会从连接池中直接获得一个被保持的连接。在用户使用完调用Close关闭连接时，连接池会将该连接返回到活动连接池中，而不是真正关闭连接。连接回到了活动链接池中后，即可在下一个Open调用中重复使用。
　　
　默认情况下，数据库连接时处于启用状态的。我们也可以通过数据库连接字符串设置关闭数据库连接池，如下面的代码所示：
using (SqlConnection connection = new SqlConnection(“Server=127.0.0.1;Initial Catalog=TestDB;Integrated Security=SSPI;Pooling=false”))
{
connection.Open();
// 执行你想要执行的数据库操作
}
其中参数Pooling=false就代表了关闭连接池。当然，我们还可以设置连接池中的最大和最小连接数，参数分别对应Max Pool Size和Min Pool Size。

（2）数据库连接的复用

由于数据源和连接参数选择的不同，每个数据库的连接并不是完全通用的。因此，ADO.NET选择通过连接字符串来区分。一旦用户使用某个连接字符串来申请数据库连接，ADO.NET将判断连接池中是否存在拥有相同连接字符串的连接，如果有则直接分配，没有则新建连接。

我们可以看看下面一段代码，三个不同的连接中，第三个复用第一个连接，第二个则无法复用第一个连接：
　　using (SqlConnection connection = new SqlConnection(“Server=127.0.0.1;Initial Catalog=TestDB;Integrated Security=SSPI”))
{
// 假设这是启动后第一个数据库连接请求，一个新连接将被建立
connection.Open();
}

using (SqlConnection connection = new SqlConnection("Server=127.0.0.1;Initial Catalog=TestDB1;Integrated Security=SSPI"))
{
    // 由于和上一个连接的字符串不同，因此无法复用第一个连接
    connection.Open();
}

using (SqlConnection connection = new SqlConnection("Server=127.0.0.1;Initial Catalog=TestDB;Integrated Security=SSPI"))
{
    // 连接字符串和第一个连接相同，保存在连接池中的第一个连接被复用
    connection.Open();
}
　（3）不同数据源的连接池机制
1
2
3
4
5
6
7
8
9
10
11
12

事实上，ADO.NET组件本身并不直接包含连接池，而针对不同类别机制的数据源指定不同的连接池方案。对于SqlClient、OracleClient命名空间下的组件，使用的连接池是由托管代码直接编写的，可以理解为连接池直接在.NET框架中运行。而对于OLEDB和ODBC的数据源来说，连接池的实现完全依靠OLEDB和ODBC提供商实现，ADO.NET只与其约定相应规范。

如何提高连接池内连接的重用率

由于只有相同连接字符串才能共享连接，因此经常导致连接池失效的问题，所以需要提高连接池内连接的重用率。

（1）连接池重用率低下的原因

由于数据库连接池仅按照数据库连接字符串来判断连接是否可重用，所以连接字符串内的任何改动都会导致连接失效。就系统内部而言，数据库连接字符串中最常被修改的两个属性就是数据库名和用户名/密码。

因此，对于多数据库的系统来说，只有同一数据库的连接才会被共用，如下图所示：

　而对多用户的系统而言，只有同一用户的申请才能共用数据库连接，如下图所示：
　
　　（2）如何提高数据库连接池重用率

这里提供一种能够有效提高数据库连接池重用率的方式，但是也会带来一点小安全隐患，在进行设计时需要权衡利弊关系，并根据实际情况来指定措施。

① 建立跳板数据库

在数据库内建立一个所有权限用户都能访问的跳板数据库，在进行数据库连接时先连接到该数据库，然后再使用 use databasename 这样的SQL语句来选择需要访问的数据库，这样就能够避免因为访问的数据库不一致而导致连接字符串不一致的情况。

下面的示例代码演示了这一做法：
　　// 假设这里使用Entry数据作为跳板数据库，然后再使用databaseName指定的数据库
using (SqlConnection connection = new SqlConnection(“Server=192.168.80.100;Uid=public;Pwd=public;Database=Entry”))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = string.Format(“USE {0}”, databaseName);
command.ExecuteNonQuery();
}

对于关系型数据库，ADO.NET支持两种访问模式，一种是连接式的访问模式，而另外一种则是离线式的访问模式。

（1）连接式的访问

连接式的访问是指读取数据时保持和数据库的连接，并且在使用时独占整个连接，逐步读取数据。这种模式比较适合从数据量庞大的数据库中查询数据，并且不能确定读取数量的情况。使用XXXCommand和XXXDataReader对象来读取数据就是一个典型的连接式数据访问，这种模式的缺点就是：数据库连接被长时间地保持在打开的状态。

下面的一段示例代码展示了这一读取模式的典型使用，首先是数据访问层的静态方法，该方法返回一个指定SQL命令返回的SqlDataReader独享，该对象呗关闭时会自动关闭依赖的数据库连接。
　　///
/// 数据访问层类型
///
public class DataHelper
{
private static readonly String conn_String = “Server=localhost;Integrated Security=true;database=TestDB”;

    /// 

    /// 使用给定的sql来访问数据库
    /// 返回SqlDataReader对象，提供连接式访问
    /// 
    /// SQL命令
    /// SqlDataReader对象
    public static SqlDataReader GetReader(String sql)
    {
        SqlConnection con = new SqlConnection(conn_String);
        try
        {
            // 打开连接，执行查询
            // 并且返回SqlDataReader
            con.Open();
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = sql;
                SqlDataReader dr = cmd.ExecuteReader
                                (CommandBehavior.CloseConnection);
                return dr;
            }
        }
        // 连接数据库随时可能发生异常
        catch (Exception ex)
        {
            if (con.State != ConnectionState.Closed)
            {
                con.Close();
            }
            return null;
        }
    }
}
其次是调用该方法的入口，使用者将会得到一个连接着数据库的SqlDataReader对象，该对象本身并**不包含任何数据**，使用者可以通过该对象读取数据库中的数据。但由于是**连接方式，读取只能是顺序地逐条读取**。
/// 

/// 使用数据库访问层
/// 连接式读取数据
/// 
class Program
{
    // SQL命令
    private static readonly String sql = "select * from dbo.DeptMaterialDetails";

    static void Main(string[] args)
    {
        // 使用连接式方法读取数据源
        using (SqlDataReader reader = DataHelper.GetReader(sql))
        {
            // 得到列数
            int colcount = reader.FieldCount;
            // 打印列名
            for (int i = 0; i < colcount; i++)
            {
                Console.Write("{0}  ", reader.GetName(i));
            }
            Console.WriteLine();
            // 顺序读取每一行，并打印
            while (reader.Read())
            {
                for (int i = 0; i < colcount; i++)
                {
                    Console.Write("{0}\t", reader[i].ToString());
                }
                Console.WriteLine();
            }
            reader.Close();
        }

        Console.ReadKey();
    }
}
（2）脱机式的访问
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

脱机式的访问并不是指不连接数据库，而是指一般在读取实际数据时连接就已经断开了。脱机式访问方式在连接至数据库后，会根据SQL命令批量读入所有记录，这样就能直接断开数据库连接以供其他线程使用，读入的记录将暂时存放在内存之中。脱机式访问的优点就在于不会长期占用数据库连接资源，而这样做的代价就是将消耗内存来存储数据，在大数据量查询的情况下该方式并不适用。例如，使用XXXDataAdapter和DataSet对象就是最常用的脱机式访问方式。

下面的实例代码对上面的连接式做了一些修改，借助SqlDataAdapter和DataSet来实现脱机式访问：
　　///
/// 数据访问层类型
///
public class DataHelper
{
private static readonly String conn_String = “Server=localhost;Integrated Security=true;database=TestDB”;

    /// 

    /// 使用给定的sql来访问数据库
    /// 返回DataSet对象
    /// 
    /// SQL命令
    /// DataSet对象
    public static DataSet GetDataSet(String sql)
    {
        SqlConnection con = new SqlConnection(conn_String);
        DataSet ds = new DataSet();
        try
        {
            // 打开连接，执行查询
            // 并且返回DataSet
            con.Open();
            using (SqlDataAdapter sd = new SqlDataAdapter(sql, con))
            {
                // 这里数据将被批量读入
                sd.Fill(ds);
            }
            return ds;
        }
        // 连接数据库随时可能发生异常
        catch (Exception ex)
        {
            if (con.State != ConnectionState.Closed)
            {
                con.Close();
            }
            return ds;
        }
    }
}

/// 

/// 使用数据库访问层
/// 脱机式读取数据
/// 
class Program
{
    //SQL命令
    private static readonly String sql = "select * from dbo.DeptMaterialDetails";

    static void Main(string[] args)
    {
        DataSet ds = DataHelper.GetDataSet(sql);
        // 打印结果，这里假设只对DataSet中的第一个表感兴趣
        DataTable dt = ds.Tables[0];
        // 打印列名
        foreach (DataColumn column in dt.Columns)
        {
            Console.Write("{0}  ", column.ColumnName);
        }
        Console.WriteLine();
        // 打印表内容
        foreach (DataRow row in dt.Rows)
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                Console.Write("{0}  ", row[i].ToString());
            }
            Console.WriteLine();
        }

        Console.ReadKey();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

简述SqlDataAdapter的基本工作机制
ADO.NET提供的XXXDataAdapter类型都使用了非常一致的机制，并且向使用者提供了统一的接口。一个SqlDataAdapter对象，在数据库操作中充当了中间适配的角色，它组织起数据缓存对数据库的所有操作，进行统一执行。一个SqlDataAdapter对象内实际包含四个负责具体操作的SqlCommand对象，它们分别负责查询、更新、插入和删除操作。下图展示了SqlDataAdapter的工作机制：

　如上图所示，实际上进行数据操作的是包含在SqlDataAdapter内的四个SqlCommand对象，而当SqlDataAdapter的Update方法被调用时，它会根据DataSet独享的更新情况而调用插入、删除和更新等命令。
　如何实现批量更新的功能
　

（2）批量更新的使用

下面的示例代码展示了如何使用UpdateBatchSize属性来设置批量更新，这里更改了DataHelper的Update方法，在内部设置了UpdateBatchSize属性。
　　public class DataHelper
{
private static readonly string conn_string = “Server=localhost;Integrated Security=true;database=TestDB”;
//选择、更新、删除和插入的SQL命令
static readonly string SQL_SELECT = “SELECT * FROM DeptMaterialDetails”;
static readonly string SQL_UPDATE = “UPDATE DeptMaterialDetails SET Department=@Department,Item=@Item,Number=@Number where Id=@Id”;
static readonly string SQL_DELETE = “DELETE FROM DeptMaterialDetails where Id=@Id”;
static readonly string SQL_INSERT = “Insert INTO DeptMaterialDetails (Department,Item,Number) VALUES (@Department,@Item,@Number)”;

    /// 

    /// 得到SqlDataAdapter，私有方法
    /// 
    /// 
    /// 
    private static SqlDataAdapter GetDataAdapter(SqlConnection con)
    {
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = new SqlCommand(SQL_SELECT, con);
        sda.UpdateCommand = new SqlCommand(SQL_UPDATE, con);
        sda.DeleteCommand = new SqlCommand(SQL_DELETE, con);
        sda.InsertCommand = new SqlCommand(SQL_INSERT, con);
        sda.UpdateCommand.Parameters.AddRange(GetUpdatePars());
        sda.InsertCommand.Parameters.AddRange(GetInsertPars());
        sda.DeleteCommand.Parameters.AddRange(GetDeletePars());
        return sda;
    }

    // 三个SqlCommand的参数
    private static SqlParameter[] GetInsertPars()
    {
        SqlParameter[] pars = new SqlParameter[3];
        pars[0] = new SqlParameter("@Department", SqlDbType.VarChar, 50, "Department");
        pars[1] = new SqlParameter("@Item", SqlDbType.VarChar, 50, "Item");
        pars[2] = new SqlParameter("@Number", SqlDbType.Int, 4, "Number");
        return pars;
    }

    private static SqlParameter[] GetUpdatePars()
    {
        SqlParameter[] pars = new SqlParameter[4];
        pars[0] = new SqlParameter("@Id", SqlDbType.VarChar, 50, "Id");
        pars[1] = new SqlParameter("@Department", SqlDbType.VarChar, 50, "Department");
        pars[2] = new SqlParameter("@Item", SqlDbType.VarChar, 50, "Item");
        pars[3] = new SqlParameter("@Number", SqlDbType.Int, 4, "Number");
        return pars;
    }

    private static SqlParameter[] GetDeletePars()
    {
        SqlParameter[] pars = new SqlParameter[1];
        pars[0] = new SqlParameter("@Id", SqlDbType.VarChar, 50, "Id");
        return pars;
    }

    /// 

    /// 更新数据库，使用批量更新
    /// 
    /// 数据集
    public static void Update(DataSet ds)
    {
        using (SqlConnection connection = new SqlConnection(conn_string))
        {
            connection.Open();
            using (SqlDataAdapter adapater = GetDataAdapter(connection))
            {
                // 设置批量更新
                adapater.UpdateBatchSize = 0;
                adapater.Update(ds);
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63