ADO.NET 基础（Connection、Command、DataReader）

ADO.NET 架构

ADO.NET 数据提供程序

数据提供程序是一组用于访问特定数据库，执行SQL命令并获取值的ADO.NE类，就其本质而言，数据提供程序是应用程序和数据元之间的一座桥梁。

数据提供程序包括以下几个类：

Connection：建立和数据源的连接
Command：执行SQL命令和存储过程
DataReader：提供对查询结果的快速只读，只进的访问方式，它是保持连接的处理方式
DataAdapter：从数据源获得信息填充到DataSet，依照DataSet的修改更新数据源，它是一系列表和关系的集合，它是断开连接的处理方式

ADO.NET没有提供通用的数据提供程序，它只为不同数据源和特别设计不同数据提供程序。每个数据提供程序包含为特定 RDBMS（关系型数据库管理系统）特别实现的 Connection、Command、DataReader 类。

.NET Framework 提供4个提供程序：

SQL Server：提供对 SQL Server 数据库的优化访问
OLE DB：提供对有 OLE DB 驱动的任意数据源的访问
Oracle：提供对 Oracle 数据库访问
ODBC：提供对有 ODBC 驱动的任意数据源访问

自 .NET 4 开始，Oracle 提供程序被废弃，虽然仍可以使用它，但微软推荐使用第三方 ADO.NET 提供程序访问 Oracle 数据库，比如 Oracle 发布的 ODP.NET，它为特殊的 Oracle 数据类型【如 LOB（大对象）、时间戳、XML 数据】提供更丰富的支持，此外还有一些特性。

ADO.NET 的标准化

初看起来，ADO.NET 似乎提供了一个松散的模型，它没有提供能够和不同类型的数据库一起工作的通用对象，这样，如果换了 RDBMS，就需要修改数据访问代码以实用不同的类。（不同数据提供程序使用完全不同的底层方法和API，各有各的特色和优化，因此也无法通用）。这个模型的好处和效果也许不是那么明显，但确实有好处：

每个提供程序使用相同的接口和基类，所以基于接口而不是基于提供程序类的编码仍可以写出通用的数据访问代码。（但要付出一些额外的代价）
每个提供程序分别独立实现，拥有各自的优化
自定义提供程序还可以加入其他提供程序没有的非标准特性（如 SQL Server 执行 XML 查询的能力）

基本 ADO.NET 类

ADO.NET 有两种类型的对象：

基于连接的对象：如 Connection、Command、DataReader 和 DataAdapter，它们连接到数据库，执行 SQL 语句，基于连接的对象是针对具体数据源类型的，并且可以在各自的命名空间中（例如SQL Server 提供程序的 System.Data.SqlClient）找到。
基于内容的对象：包括 DataSet、DataColumn、DataRow、DataRelation等，它们完全和数据源独立，出现在 System.Data 命名空间里。

ADO.NET 支持的最重要命名空间：

System.Data	关键数据容器类。包括列、关系、表、数据集、行、视图和约束建立模型。
System.Data.Common	包括大部分基本的抽象类，这些类实现 System.Data 中的某些接口并定义了 ADO.NET的核心功能。数据提供程序继承这些类来创建它们自己的版本。
System.Data.OleDb	包含用于连接 OLE DB提供程序的类。这些类支持大部分 OLE DB 提供程序。
System.Data.SqlClient	包含用于连接微软 SQL Server 数据库所需的类，这些类经过优化以便使用 SQL Server 的 TDS 接口。
System.Data.OracleClient	包含用于连接 Oracle 数据库的类，这些类使用经过优化的 Oracle 调用接口（OCI）
System.Data.Odbc	包含连接大部分 ODBC 驱动所需的类。所有数据源都包含 ODBC 驱动，并可以通过“控制面板”中的“数据源”快捷方式配置。
System.Data.SqlTypes	包含 SQL Server 本地数据类型相对应的类型。这些类不是必须的，但它们提供了一种使用标准.NET数据类型的选择，这是自动类型转换时所必需的。

Connection 类

Connection类用于和要交互的数据源建立连接，在执行任何操作前（增、删、改、查）必须建立连接。

连接字符串

创建 Connection 对象时，必须先提供连接字符串。连接字符串是以分号（;）分隔的一系列名称/值对的选项，这些选项的顺序并不重要，大小写也不重要。

尽管随着 RDBMS 和提供程序的不同，连接字符串也不同，但基本需要的信息如下：

数据库所在的服务器
要使用的数据库名称
如何通过数据库验证

比如下面这个连接字符串使用整合安全（用当前登录windows用户身份访问数据库）方式连接本机的 Northwind 数据库：

string connStr = "Data Source=localhost;Initial Catalog=Northwind;Integrated Secutiry=SSPI" // SSPI 等同于 True

如果数据库不支持整合安全，就必须指定有效的用户名和密码，例如：

string connStr = "Data Source=localhost;Initial Catalog=Northwind;user id=sa;password=xxx"

若使用OLE DB提供程序，连接字符串和前面的相似，但需要额外添加一个提供程序设置来标识 OLE DB 驱动

string connStr = "Data Source=localhost;Initial Catalog=Northwind;user id=sa;password=xxx;Provider=MSDAORA"

通过 MSDAORA OLE DB 提供程序访问 Oracle 数据库

连接 Access 数据库

string connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataSources\Northwind.mdb"

没有任何理由硬编码数据库连接字符串，web.config 文件的 <connectionStrings> 节便于保存和随时修改连接字符串：

<connectionStrings>

  <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Secutiry=SSPI"/>    

</connectionStrings>

接着可以使用名称从 WebConfigurationManager.ConnectionStrings 集合中读取连接字符串（导入 System.Web.Configuration 命名空间）：

string connStr = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

测试连接

protected void Page_Load(object sender, EventArgs e)

{

    string connStr = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    SqlConnection conn = new SqlConnection(connStr);

    try

    {

        conn.Open();

        lblInfo.Text = "<b>Server Version:</b> " + conn.ServerVersion;

        lblInfo.Text += "<br /><b>Connection State:</b> " + conn.State.ToString();

    }

    catch (Exception err)

    {

        lblInfo.Text = "Error reading the database. " + err.Message;

    }

    finally {

        conn.Close();

        lblInfo.Text += "<br /><b>Connection State:</b> " + conn.State.ToString();

    }

}

连接时有限的服务器资源，要尽量晚打开而尽早释放。finally 块确保了连接能够被关闭，否则如果发生了异常，连接将一直保持到垃圾回收器释放 SqlConnection 对象。

另一种方法是把数据访问代码放入到 using 块中。using 语句用来声明正在短期使用的一个可释放的对象，最妙的是你不编写finally块，即使由于未处理的异常而退出该块，using 语句也会释放正在使用的对象。

string connStr = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

SqlConnection conn = new SqlConnection(connStr);

using (conn)

{

    conn.Open();

    lblInfo.Text = "<b>Server Version:</b> " + conn.ServerVersion;

    lblInfo.Text += "<br /><b>Connection State:</b> " + conn.State.ToString();

}

lblInfo.Text += "<br /><b>Connection State:</b> " + conn.State.ToString();

连接池

虽然请求连接耗时很短，但它们确实需要时间。在 Web 应用程序中，随着请求的处理连接被不断的打开和关闭，每个请求都被高效处理。在这样的环境中，即使建立连接所需要的微小消耗也会显著影响系统性能。

一个解决办法是使用连接池。连接池保证已经打开的数据库连接，这些连接在使用相同数据源的会话间共享，这样就省了不断创建和销毁连接的时间。当客户用 Open（）方法请求打开连接时，连接直接由连接池提供而不是再次创建。当客户调用 Close（）方法或 Dispose（）方法释放连接时，它并没有被真的释放而是重新回到池中等待下一次请求。

ADO.NET 并不包含任何连接池的机制，但是大部分数据库提供程序提供对连接池的实现。

为了使用 SQL Server 或 Oracle 的连接池，连接字符串必须完全匹配。即使只有微小的差异（哪怕只是修改了参数的顺序或是多了一个空格），也会在新池中创建新连接，这也是不要硬编码连接字符串的原因。

SQL Server 和 Oracle 提供程序都是自动使用连接池的。

Max Pool Size	池中允许最大连接数（默认 100），如已达最大数，所有打开连接请求将排队等候
min Pool Size	池中最小连接数（默认 0），第一次打开时会建立相应数量的连接，所以第一次请求时会稍微有点延迟
Pooling	为 true（默认值）时，连接从池中获取
Connection Lifetime	指定以秒为单位的时间间隔，默认是 0 。池中的连接创建时间早于指定生命周期，将被销毁。当需要大量回收连接时，该功能很有效

下面这个连接字符串设置最小连接池的大小：

string connStr = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Min Pool Size=10"

Command 类和 DataReader 类

Command 基础

Command 类可以执行所有类型的 SQL 语句。虽然它也可以执行数据定义任务（创建或修改数据库、表和索引），但一般被用来执行数据操作任务（增删改查）。

使用命令前，需要设置命令文本（CommandText）、命令类型（CommandType）并把命令绑定到连接（Connection）上，或者作为构造函数的参数传递。

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM Employees";

传参的构造方法效率更高一些：

SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", conn);

也可以使用存储过程：

SqlCommand cmd = new SqlCommand("GetEmployees", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

这些只是定义了 Command 对象而没有真正的执行该对象，Command 对象的方法如下：

ExecuteNonQuery()	执行非 SELECT 语句，如增、删、改。返回受影响的行数。也可以执行数据定义命令（表的创建，索引，约束等）
ExecuteScalar()	执行 SELECT 查询，返回第一行第一列的值，类型为 Object，常用语执行聚合函数
ExecuteReader()	执行 SELECT 查询，返回一个封装了只读，只进游标的 DataReader 对象

DataReader 类

允许以只进，只读流的方式每次读取一条 SELECT 命令返回的记录，这种方式有时候成为流水游标。

Read()	将行游标前进到流的下一行，在读取第一条记录前，也必须调用这个方法，该方法返回一个 bool 值指示是否还有下一行
GetValue()	根据序号或字段名得到当前行中该字段值。基于名称的查询更易读，但效率不高。
GetValues()	将当前行中的值保存到数组中，保存的记录数取决于你传递给该方法的数组的大小。可以使用 DataReader 对象的 FieldCount 属性确定一行的列数。
GetInt32() GetChars() GetDateTime() GetXXX()	返回指定序号的字段值，返回类型和方法名称一致，如果类型不一致会得到一个 InvalidCastException 异常
NextResult()	如果有多个记录集，该方法将游标移动到下一个记录集。
Close()	关闭 Reader。如果原命令执行一个带有输出参数的存储过程，该参数仅在 Reader 关闭后才刻度。

ExecuteReader（）方法和 DataReader 对象示例：

protected void Page_Load(object sender, EventArgs e)

{

    string connStr = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    SqlConnection conn = new SqlConnection(connStr);

    string sql = "SELECT * FROM Employees"; // 实际开发请查询仅需要的列

    SqlCommand cmd = new SqlCommand(sql, conn);

    conn.Open();

    SqlDataReader reader = cmd.ExecuteReader();

    StringBuilder htmlStr = new StringBuilder();

    while (reader.Read())

    {

        htmlStr.Append("<li>");

        htmlStr.Append(reader["TitleOfCourtesy"]);

        htmlStr.Append("<b>");

        htmlStr.Append(reader.GetString(1));

        htmlStr.Append("</b>, ");

        htmlStr.Append(reader.GetString(2));

        htmlStr.Append(" - employee from ");

        htmlStr.Append(reader.GetDateTime(6).ToString("d"));

        htmlStr.Append("</li>");

    }

    reader.Close();

    conn.Close();

    Label1.Text = htmlStr.ToString();

}

1. 空值

下面是一个可空的整形示例：

// 可空整型可以包含任何 Int32 位数值以及 null

int? nullableInteger = null;

// 指示当前的 System.Nullable<T> 对象是否有值

if (nullableInteger.HasValue)

{

    nullableInteger += 1;

}

遗憾的是，DataReader 没有和 .NET 可空值集成，这是出于历史原因。空数据类型在 .NET2.0第一次引入，那时 DataReader 模型已经成功建立并且很难改变。DataReader 遇到数据里的空值时，它返回一个常量 DBNull.Value，任何试图转换它的数据类型操作会引发异常。

必须使用这样的代码对其进行检测：

if (reader["NumberOfHires"] == DBNull.Value)

{

    numberOfHires = null;

}

else

{

    numberOfHires = (int?)reader["NumberOfHires"];

}

2. 处理多个结果集

命令会在两种情况下返回多个结果集：

调用存储过程时，该存储过程有多个 SELECT 语句
直接使用文本命令时，可以把用分号（；）分隔的命令批次执行

参考下面的示例：

protected void Page_Load(object sender, EventArgs e)

{

    string connStr = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    SqlConnection conn = new SqlConnection(connStr);

    string sql = @"SELECT TOP 5 EmployeeID,LastName,FirstName FROM Employees;

                SELECT TOP 5 CustomerID,CompanyName,ContactName FROM Customers;

                SELECT TOP 5 SupplierID,CompanyName,ContactName FROM Suppliers";

    SqlCommand cmd = new SqlCommand(sql, conn);

    conn.Open();

    SqlDataReader reader = cmd.ExecuteReader();        

    StringBuilder htmlStr = new StringBuilder();

    int i = 0;

    do

    {

        htmlStr.Append("<h2>Rowset: ");

        htmlStr.Append(i.ToString());

        htmlStr.Append("</h2>");

        while (reader.Read())

        {

            htmlStr.Append("<li>");

            // 获得所有的列

            for (int field = 0; field < reader.FieldCount; field++)

            {

                htmlStr.Append(reader.GetName(field).ToString());

                htmlStr.Append(": ");

                htmlStr.Append(reader.GetValue(field).ToString());

                htmlStr.Append("&nbsp;&nbsp;&nbsp;");

            }

            htmlStr.Append("</li>");

        }

        htmlStr.Append("<br /><br />");

        i++;

    } while (reader.NextResult()); // 前进到下一个结果集,如果没有返回 false

    reader.Close();

    conn.Close();

    Label1.Text = htmlStr.ToString();

}