ADO.NET 的最佳实践技巧

这是我很早以前看过的微软的一篇文章，最近，一些网友问的问题很多理论都在里面，所以，整理一下放在这里，大家可以参考一下。

简介

本文为您提供了在 Microsoft ADO.NET 应用程序中实现和获得最佳性能、可伸缩性以及功能的最佳解决方案；同时也讲述了使用 ADO.NET 中可用对象的最佳实践；并提出一些有助于优化 ADO.NET 应用程序设计的建议。

本文包含：

使用 DataReader、DataSet、DataAdapter 和 DataView

ADO.NET 提供以下两个对象，用于检索关系数据并将其存储在内存中：DataSet 和 DataReader。DataSet 提供一个内存中数据的关系表示形式，一整套包括一些表在内的数据（这些表包含数据、对数据进行排序并约束数据），以及表之间的关系。DataReader 提供一个来自数据库的快速、只进、只读数据流。

当使用 DataSet 时，经常会利用 DataAdapter（也可能是 CommandBuilder）与数据源进行交互。当使用 DataSet 时，也可以利用 DataView 对 DataSet 中的数据应用排序和筛选。也可以从 DataSet 继承，创建强类型 DataSet，用于将表、行和列作为强类型对象属性公开。

下列主题包括的信息涉及：使用 DataSet 或 DataReader 的最佳时机、如何优化访问它们所包含数据、以及如何优化使用 DataAdapter（包括 CommandBuilder）和 DataView 的技巧。

DataSet 与 DataReader

当设计应用程序时，要考虑应用程序所需功能的等级，以确定使用 DataSet 或者是 DataReader。

要通过应用程序执行以下操作，就要使用 DataSet：

对于下列情况，要在应用程序中使用 DataReader：

注填充 DataSet 时，DataAdapter 使用 DataReader。因此，使用 DataAdapter 取代 DataSet 提升的性能表现为节省了 DataSet 占用内存和填充 DataSet 需要的循环。一般来说，此性能提升只是象征性的，因此，设计决策应以所需功能为基础。

使用强类型 DataSet 的好处

DataSet 的另一个好处是可被继承以创建一个强类型 DataSet。强类型 DataSet 的好处包括设计时类型检查，以及 Microsoft Visual Studio .NET 用于强类型 DataSet 语句结束所带来的好处。修改了 DataSet 的架构或关系结构后，就可以创建一个强类型 DataSet，把行和列作为对象的属性公开，而不是作为集合中的项公开。例如，不公开客户表中行的姓名列，而公开 Customer 对象的 Name 属性。类型化 DataSet 从 DataSet 类派生，因此不会牺牲 DataSet 的任何功能。也就是说，类型化 DataSet 仍能远程访问，并作为数据绑定控件（例如 DataGrid）的数据源提供。如果架构事先不可知，仍能受益于通用 DataSet 的功能，但却不能受益于强类型 DataSet 的附加功能。

处理强类型 DataSet 中的空引用

使用强类型 DataSet 时，可以批注 DataSet 的 XML 架构定义语言 (XSD) 架构，以确保强类型 DataSet 正确处理空引用。nullValue 批注使您可用一个指定的值 String.Empty 代替 DBNull、保留空引用或引发异常。选择哪个选项取决于应用程序的上下文。默认情况下，如果遇到空引用，就会引发异常。

有关更多信息，请参阅 Working with a Typed DataSet。

刷新 DataSet 中的数据

如果想用服务器上的更新值刷新 DataSet 中的值，就使用 DataAdapter.Fill。如果有在 DataTable 上定义的主键，DataAdapter.Fill 会根据主键进行新行匹配，并且当更改到现有行时应用服务器上的值。即使刷新之前修改了它们，刷新行的 RowState 仍被设置为 Unchanged。注意，如果没有为 DataTable 定义主键，DataAdapter.Fill 就用可能重复的主键值添加新行。

如果想用来自服务器的当前值刷新表，并同时保留对表中的行所做的任何更改，必须首先用 DataAdapter.Fill 填充表，并填充一个新的 DataTable，然后用 preserveChanges 值 true 把 DataTableMerge 到 DataSet 中。

在 DataSet 中搜索数据

在 DataSet 中查询与特定条件相匹配的行时，可以利用基于索引的查找提高搜索性能。当把 PrimaryKey 值赋给 DataTable 时，会创建一个索引。当给 DataTable 创建 DataView 时，也会创建一个索引。下面是一些利用基于索引进行查找的技巧。

DataView 构造

如果创建了 DataView，并且修改了 Sort、RowFilter 或 RowStateFilter 属性，DataView 就会为基础 DataTable 中的数据建立索引。创建 DataView 对象时，要使用 DataView 构造函数，它用 Sort、RowFilter 和 RowStateFilter 值作为构造函数参数（与基础 DataTable 一起）。结果是创建了一次索引。创建一个“空”DataView 并随后设置 Sort、RowFilter 或 RowStateFilter 属性，会导致索引至少创建两次。

分页

ADO.NET 可以显式控制从数据源中返回什么样的数据，以及在 DataSet 中本地缓存多少数据。对查询结果的分页没有唯一的答案，但下面有一些设计应用程序时应该考虑的技巧。

SELECT TOP 10 * FROM

      (SELECT TOP 30 * FROM Customers ORDER BY Id ASC) AS Table1

      ORDER BY Id DESC

有关更多信息，请参阅 .NET Data Access Architecture Guide。

用架构填充 DataSet

当用数据填充 DataSet 时，DataAdapter.Fill 方法使用 DataSet 的现有架构，并使用从 SelectCommand 返回的数据填充它。如果在 DataSet 中没有表名与要被填充的表名相匹配，Fill 方法就会创建一个表。默认情况下，Fill 仅定义列和列类型。

通过设置 DataAdapter 的 MissingSchemaAction 属性，可以重写 Fill 的默认行为。例如，要让 Fill 创建一个表架构，并且还包括主键信息、唯一约束、列属性、是否允许为空、最大列长度、只读列和自动增量的列，就要把 DataAdapter.MissingSchemaAction 指定为 MissingSchemaAction.AddWithKey。或者，在调用 DataAdapter.Fill 前，可以调用 DataAdapter.FillSchema 来确保当填充 DataSet 时架构已到位。

对 FillSchema 的调用会产生一个到服务器的额外行程，用于检索附加架构信息。为了获得最佳性能，需要在调用 Fill 之前指定 DataSet 的架构，或者设置 DataAdapter 的 MissingSchemaAction。

使用 CommandBuilder 的最佳实践

假设 SelectCommand 执行单一表 SELECT，CommandBuilder 就会以 DataAdapter 的 SelectCommand 属性为基础自动生成 DataAdapter 的 InsertCommand、UpdateCommand、和 DeleteCommand 属性。下面是为获得最佳性能而使用 CommandBuilder 的一些技巧。

批处理 SQL 语句

很多数据库支持把多条命令合并或批处理成一条单一命令执行。例如，SQL Server 使您可以用分号 (;) 分隔命令。把多条命令合并成单一命令，能减少到服务器的行程数，并提高应用程序的性能。例如，可以把所有预定的删除在应用程序中本地存储起来，然后再发出一条批处理命令调用，从数据源删除它们。

虽然这样做确实能提高性能，但是，当对 DataSet 中的数据更新进行管理时，可能会增加应用程序的复杂性。要保持简单，可能要在 DataSet 中为每个 DataTable 创建一个 DataAdapter。

用多个表填充 DataSet

如果使用批处理 SQL 语句检索多个表并填充 DataSet，第一个表用指定给 Fill 方法的表名命名。后面的表用指定给 Fill 方法的表名加上一个从 1 开始并且增量为 1 的数字命名。例如，如果运行下面的代码：

'Visual Basic

Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "Customers")

//C#

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection);

DataSet ds = new DataSet();

da.Fill(ds, "Customers");

来自 Customers 表的数据放在名为 "Customers" 的 DataTable 中。来自 Orders 表的数据放在名为 "Customers1" 的 DataTable 中。

填充完 DataSet 之后，可以很容易地把 "Customers1" 表的 TableName 属性改为 "Orders"。但是，后面的填充会导致 "Customers" 表被重新填充，而 "Orders" 表会被忽略，并创建另外一个 "Customers1" 表。为了对这种情况作出补救，创建一个 DataTableMapping，把 "Customers1" 映射到 "Orders"，并为其他后面的表创建其他的表映射。例如：

'Visual Basic

Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection)

da.TableMappings.Add("Customers1", "Orders")

Dim ds As DataSet = New DataSet()

da.Fill(ds, "Customers")

//C#

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection);

da.TableMappings.Add("Customers1", "Orders");

DataSet ds = new DataSet();

da.Fill(ds, "Customers");

使用 DataReader

下面是一些使用 DataReader 获得最佳性能的技巧，同时还回答了一些关于使用 DataReader 的常见问题。

二进制大对象 (BLOB)

用 DataReader 检索二进制大对象 (BLOB) 时，应该把 CommandBehavior.SequentialAccess 传递给 ExecuteReader 方法调用。因为 DataReader 的默认行为是每次 Read 都把整行加载到内存，又因为 BLOB 值可能非常大，所以结果可能由于单个 BLOB 而使大量内存被用光。SequentialAccess 将 DataReader 的行为设置为只加载请求的数据。然后还可以使用 GetBytes 或 GetChars 控制每次加载多少数据。

记住，使用 SequentialAccess 时，不能不按顺序访问 DataReader 返回的不同字段。也就是说，如果查询返回三列，其中第三列是 BLOB，并且想访问前两列中的数据，就必须在访问 BLOB 数据之前先访问第一列的值，然后访问第二列的值。这是因为现在数据是顺序返回的，并且 DataReader 一旦读过该数据，该数据就不再可用。

有关如何在 ADO.NET 中访问 BLOB 的详细描述，请参阅 Obtaining BLOB Values from a Database。

返回页首

使用命令

ADO.NET 提供了几种命令执行的不同方法以及优化命令执行的不同选项。下面包括一些技巧，它们是关于选择最佳命令执行以及如何提高执行命令的性能。

使用 OleDbCommand 的最佳实践

不同 .NET 框架数据提供程序之间的命令执行被尽可能标准化了。但是，数据提供程序之间仍然存在差异。下面给出一些技巧，可微调用于 OLE DB 的 .NET 框架数据提供程序的命令执行。

使用 SqlCommand 的最佳实践

使用 SqlCommand 执行存储过程的快速提示：如果调用存储过程，将 SqlCommand 的 CommandType 属性指定为 StoredProcedure 的 CommandType。这样通过将该命令显式标识为存储过程，就不需要在执行之前分析命令。

使用 Prepare 方法

对于重复作用于数据源的参数化命令，Command.Prepare 方法能提高性能。Prepare 指示数据源为多次调用优化指定的命令。要想有效利用 Prepare，需要彻底理解数据源是如何响应 Prepare 调用的。对于一些数据源（例如 SQL Server 2000），命令是隐式优化的，不必调用 Prepare。对于其他（例如 SQL Server 7.0）数据源，Prepare 会比较有效。

显式指定架构和元数据

只要用户没有指定元数据信息，ADO.NET 的许多对象就会推断元数据信息。下面是一些示例：

但是，每次用到这些特性，都会有性能损失。建议将这些特性主要用于设计时和即席应用程序中。在可能的情况下，显式指定架构和元数据。其中包括在 DataSet 中定义表和列、定义 DataAdapter 的 Command 属性、以及为 Command 定义 Parameter 信息。

ExecuteScalar 和 ExecuteNonQuery

如果想返回像 Count(*)、Sum(Price) 或 Avg(Quantity) 的结果那样的单值，可以使用 Command.ExecuteScalar。ExecuteScalar 返回第一行第一列的值，将结果集作为标量值返回。因为单独一步就能完成，所以 ExecuteScalar 不仅简化了代码，还提高了性能；要是使用 DataReader 就需要两步才能完成（即，ExecuteReader + 取值）。

使用不返回行的 SQL 语句时，例如修改数据（例如INSERT、UPDATE 或 DELETE）或仅返回输出参数或返回值，请使用 ExecuteNonQuery。这避免了用于创建空 DataReader 的任何不必要处理。

有关更多信息，请参阅 Executing a Command。

测试 Null

如果表（在数据库中）中的列允许为空，就不能测试参数值是否“等于”空。相反，需要写一个 WHERE 子句，测试列和参数是否都为空。下面的 SQL 语句返回一些行，它们的 LastName 列等于赋给 @LastName 参数的值，或者 LastName 列和 @LastName 参数都为空。

SELECT * FROM Customers

WHERE ((LastName = @LastName) OR (LastName IS NULL AND @LastName IS NULL))

把 Null 作为参数值传递

对数据库的命令中，当把空值作为参数值发送时，不能使用 null（Visual Basic庐 .NET 中为 Nothing）。而需要使用 DBNull.Value。例如：

'Visual Basic

Dim param As SqlParameter = New SqlParameter("@Name", SqlDbType.NVarChar, 20)

param.Value = DBNull.Value

//C#

SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);

param.Value = DBNull.Value;

执行事务

ADO.NET 的事务模型已经更改。在 ADO 中，当调用 StartTransaction 时，调用之后的任何更新操作都被视为是事务的一部分。但是，在 ADO.NET 中，当调用 Connection.BeginTransaction 时，会返回一个 Transaction 对象，需要把它与 Command 的 Transaction 属性联系起来。这种设计可以在一个单一连接上执行多个根事务。如果未将 Command.Transaction 属性设置为一个针对相关的 Connection 而启动的 Transaction，那么 Command 就会失败并引发异常。

即将发布的 .NET 框架将使您可以在现有的分布式事务中手动登记。这对于对象池方案来说很理想；在该方案中，一个池对象打开一次连接，但是在多个独立的事务中都涉及到该对象。.NET 框架 1.0 发行版中这一功能并不可用。

有关事务的更多信息，请参阅 Performing Transactions 以及 .NET Data Access Architecture Guide。

返回页首

使用连接

高性能应用程序与使用中的数据源保持最短时间的连接，并且利用性能增强技术，例如连接池。下面的主题提供一些技巧，有助于在使用 ADO.NET 连接到数据源时获得更好的性能。

连接池

用于 ODBC 的 SQL Server、OLE DB 和 .NET 框架数据提供程序隐式缓冲连接。通过在连接字符串中指定不同的属性值，可以控制连接池的行为。有关如何控制连接池的行为的详细信息，请参阅 Connection Pooling for the SQL Server .NET Data Provider 和 Connection Pooling for the OLE DB .NET Data Provider。

用 DataAdapter 优化连接

DataAdapter 的 Fill 和 Update 方法在连接关闭的情况下自动打开为相关命令属性指定的连接。如果 Fill 或 Update 方法打开了连接，Fill 或 Update 将在操作完成的时候关闭它。为了获得最佳性能，仅在需要时将与数据库的连接保持为打开。同时，减少打开和关闭多操作连接的次数。

如果只执行单个的 Fill 或 Update 方法调用，建议允许 Fill 或 Update 方法隐式打开和关闭连接。如果对 Fill 和/或 Update 调用有很多，建议显式打开连接，调用 Fill 和/或 Update，然后显式关闭连接。

另外，当执行事务时，显式地在开始事务之前打开连接，并在提交之后关闭连接。例如：

'Visual Basic

Public Sub RunSqlTransaction(da As SqlDataAdapter, myConnection As SqlConnection, ds As DataSet)

myConnection.Open()

Dim myTrans As SqlTransaction = myConnection.BeginTransaction()

myCommand.Transaction = myTrans

Try

da.Update(ds)

myTrans.Commit()

Console.WriteLine("Update successful.")

Catch e As Exception

Try

myTrans.Rollback()

Catch ex As SqlException

If Not myTrans.Connection Is Nothing Then

Console.WriteLine("An exception of type " & ex.GetType().ToString() & _

" was encountered while attempting to roll back the transaction.")

End If

End Try

Console.WriteLine("An exception of type " & e.GetType().ToString() & " was encountered.")

Console.WriteLine("Update failed.")

End Try

myConnection.Close()

End Sub

//C#

public void RunSqlTransaction(SqlDataAdapter da, SqlConnection myConnection, DataSet ds)

{

myConnection.Open();

SqlTransaction myTrans = myConnection.BeginTransaction();

myCommand.Transaction = myTrans;

try

{

da.Update(ds);

myCommand.Transaction.Commit();

Console.WriteLine("Update successful.");

}

catch(Exception e)

{

try

{

myTrans.Rollback();

}

catch (SqlException ex)

{

if (myTrans.Connection != null)

{

Console.WriteLine("An exception of type " + ex.GetType() +

" was encountered while attempting to roll back the transaction.");

}

}

Console.WriteLine(e.ToString());

Console.WriteLine("Update failed.");

}

myConnection.Close();

}

始终关闭 Connection 和 DataReader

完成对 Connection 或 DataReader 对象的使用后，总是显式地关闭它们。尽管垃圾回收最终会清除对象并因此释放连接和其他托管资源，但垃圾回收仅在需要时执行。因此，确保任何宝贵的资源被显式释放仍然是您的责任。并且，没有显式关闭的 Connections 可能不会返回到池中。例如，一个超出作用范围却没有显式关闭的连接，只有当池大小达到最大并且连接仍然有效时，才会被返回到连接池中。

注 不要在类的 Finalize 方法中对 Connection、DataReader 或任何其他托管对象调用 Close 或 Dispose。最后完成的时候，仅释放类自己直接拥有的非托管资源。如果类没有任何非托管资源，就不要在类定义中包含 Finalize 方法。

在 C# 中使用 "Using" 语句

对于 C# 程序员来说，确保始终关闭 Connection 和 DataReader 对象的一个方便的方法就是使用 using 语句。using 语句在离开自己的作用范围时，会自动调用被“使用”的对象的 Dispose。例如：

//C#

string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";

using (SqlConnection conn = new SqlConnection(connString))

{

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT CustomerId, CompanyName FROM Customers";

conn.Open();

using (SqlDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));

}

}

Using 语句不能用于 Microsoft庐 Visual Basic庐 .NET。

避免访问 OleDbConnection.State 属性

如果连接已经打开，OleDbConnection.State 属性会对 DBPROP_CONNECTIONSTATUS 属性的 DATASOURCEINFO 属性集执行本地 OLE DB 调用 IDBProperties.GetProperties，这可能会导致对数据源的往返行程。也就是说，检查 State 属性的代价可能很高。所以仅在需要时检查 State 属性。如果需要经常检查该属性，监听 OleDbConnection 的 StateChange 事件可能会使应用程序的性能好一些。有关 StateChange 事件的详细信息，请参阅 Working with Connection Events。

返回页首

与 XML 集成

ADO.NET 在 DataSet 中提供了广泛的 XML 集成，并公开了 SQL Server 2000 及其更高版本提供的部分 XML 功能。还可以使用 SQLXML 3.0 广泛地访问 SQL Server 2000 及其更高版本中的 XML 功能。下面是使用 XML 和 ADO.NET 的技巧和信息。

DataSet 和 XML

DataSet 与 XML 紧密集成，并提供如下功能：

注 可以使用这种同步把 XML 功能（例如，XPath 查询和 XSLT 转换）应用到 DataSet 中的数据，或者在保留原始 XML 保真度的前提下为 XML 文档中数据的全部或其中一个子集提供关系视图。

关于 DataSet 提供的 XML 功能的详细信息，请参阅 XML and the DataSet。

架构推断

从 XML 文件加载 DataSet 时，可以从 XSD 架构加载 DataSet 架构，或者在加载数据前预定义表和列。如果没有可用的 XSD 架构，而且不知道为 XML 文件的内容定义哪些表和列，就可以在 XML 文档结构的基础上对架构进行推断。

架构推断作为迁移工具很有用，但应只限于设计阶段应用程序，这是由于推断处理有如下限制。

有关更多信息，请参阅 Inferring DataSet Relational Structure from XML。

用于 XML 查询的 SQL Server

如果正从 SQL Server 2000 FOR XML 返回查询结果，可以让用于 SQL Server 的 .NET 框架数据提供程序使用 SqlCommand.ExecuteXmlReader 方法直接创建一个 XmlReader。

SQLXML 托管类

.NET 框架中有一些类，公开用于 SQL Server 2000 的 XML 的功能。这些类可在 Microsoft.Data.SqlXml 命名空间中找到，它们添加了执行 XPath 查询和 XML 模板文件以及把 XSLT 转换应用到数据的能力。

SQLXML 托管类包含在用于 Microsoft SQL Server 2000 的 XML (SQLXML 2.0) 发行版中，可从 XML for Microsoft SQL Server 2000 Web Release 2 (SQLXML 2.0) ??μ?。

返回页首

更多有用的技巧

下面是一些编写 ADO.NET 代码时的通用技巧。

避免自动增量值冲突

就像大多数数据源一样，DataSet 使您可标识那些添加新行时自动对其值进行递增的列。在 DataSet 中使用自动增量的列时，如果自动增量的列来自数据源，可避免添加到 DataSet 的行和添加到数据源的行之间本地编号冲突。

例如，考虑一个表，它的主键列 CustomerID 是自动增量的。两个新的客户信息行添加到表中，并接收到自动增量的 CustomerID 值 1 和 2。然后，只有第二个客户行被传递给 DataAdapter 的方法 Update，新添加的行在数据源接收到一个自动增量的 CustomerID 值 1，与 DataSet 中的值 2 不匹配。当 DataAdapter 用返回值填充表中第二行时，就会出现约束冲突，因为第一个客户行已经使用了 CustomerID 值 1。

要避免这种情况，建议在使用数据源上自动增量的列以及 DataSet 上自动增量的列时，把 DataSet 中的列创建为 AutoIncrementStep 值等于 -1 并且 AutoIncrementSeed 值等于 0，另外，还要确保数据源生成的自动增量标识值从 1 开始，并且以正阶值递增。因此，DataSet 为自动增量值生成负数，与数据源生成的正自动增量值不冲突。另外一个选择是使用 Guid 类型的列，而不是自动增量的列。生成 Guid 值的算法应该永远不会使数据源中生成的 Guid 值与 DataSet 中生成的 Guid 值一样。

如果自动增量的列只是用作唯一值，而且没有任何意义，就考虑使用 Guid 代替自动增量的列。它们是唯一的，并且避免了使用自动增量的列所必需的额外工作。

有关从数据源检索自动增量的列值的示例，请参阅 Retrieving Identity or AutoNumber Values。

检查开放式并发冲突

按照设计，由于 DataSet 是与数据源断开的，所以，当多个客户端在数据源上按照开放式并发模型更新数据时，需要确保应用程序避免冲突。

在测试开放式并发冲突时有几项技术。一项技术涉及在表中包含时间戳列。另外一项技术是，验证一行中所有列的原始值是否仍然与通过在 SQL 语句中使用 WHERE 子句进行测试时在数据库中找到的值相匹配。

有关包含代码示例的该主题的详细讨论，请参阅 Optimistic Concurrency。

多线程编程

ADO.NET 对性能、吞吐量和可伸缩性进行优化。因此，ADO.NET 对象不锁定资源，并且必须只用于单线程。一个例外是 DataSet，它对多个阅读器是线程安全的。但是，在写的时候需要把 DataSet 锁定。

仅在需要的时候才用 COM Interop 访问 ADO

ADO.NET 的设计目的是成为许多应用程序的最佳解决方案。但是，有些应用程序需要只有使用 ADO 对象才有的功能，例如，ADO 多维 (ADOMD)。在这些情况下，应用程序可以用 COM Interop 访问 ADO。注意使用 COM Interop 访问具有 ADO 的数据会导致性能降低。在设计应用程序时，首先在实现用 COM Interop 访问 ADO 的设计之前，先确定 ADO.NET 是否满足设计需求。