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

简介

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

本文包含：

•

有关 .NET 框架包含的 .NET 框架数据提供程序的信息。

•

DataSet 和 DataReader 之间的比较，以及这些对象中每个对象最佳用法的解释。

•

解释如何使用 DataSet、Commands 和 Connections。

•

有关与 XML 集成的信息。

•

通用的技巧和问题。

使用 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：

•

在结果的多个离散表之间进行导航。

•

操作来自多个数据源（例如，来自多个数据库、一个 XML 文件和一个电子表格的混合数据）的数据。

•

在各层之间交换数据或使用 XML Web 服务。与 DataReader 不同的是，DataSet 能传递给远程客户端。

•

重用同样的行组，以便通过缓存获得性能改善（例如排序、搜索或筛选数据）。

•

每行执行大量处理。对使用 DataReader 返回的每一行进行扩展处理会延长服务于 DataReader 的连接的必要时间，这影响了性能。

•

使用 XML 操作对数据进行操作，例如可扩展样式表语言转换（XSLT 转换）或 XPath 查询。

对于下列情况，要在应用程序中使用 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 时，也会创建一个索引。下面是一些利用基于索引进行查找的技巧。

•

如果对组成 DataTable 的 PrimaryKey的列进行查询，要使用 DataTable.Rows.Find 而不是 DataTable.Select。

•

对于涉及到非主键列的查询，可以使用 DataView 为数据的多个查询提高性能。当把排序顺序应用到 DataView 时，就会建立一个搜索时使用的索引。DataView 公开 Find 和 FindRows 方法，以便查询基础 DataTable 中的数据。

•

如果不需要表的排序视图，仍可以通过为 DataTable 创建 DataView 来利用基于索引的查找。注意，只有对数据执行多个查询操作时，这样才会带来好处。如果只执行单一查询，创建索引所需要的处理就会降低使用索引所带来的性能提升。

DataView 构造

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

分页

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

•

避免使用带有 startRecord 和 maxRecords 值的 DataAdapter.Fill 重载。当以这种方式填充 DataSet 时，只有 maxRecords 参数（从 startRecord 参数标识的记录开始）指定的记录数量用于填充 DataSet，但无论如何总是返回完整的查询。这就会引起不必要的处理，用于读取“不需要的”记录；而且为了返回附加记录，会耗尽不必要的服务器资源。

•

用于每次只返回一页记录的技术是创建 SQL 语句，把 WHERE 子句以及 ORDER BY 子句和 TOP 谓词组合起来。此技术取决于存在一种可唯一标识每一行的办法。当浏览下一页记录时，修改 WHERE 子句使之包含所有唯一标识符大于当前页最后一个唯一标识符的记录。当浏览上一页记录时，修改 WHERE 子句使之返回所有唯一标识符小于当前页第一个唯一标识符的记录。两种查询都只返回记录的 TOP 页。当浏览上一页时，需要以降序为结果排序。这将有效地返回查询的最后一页（如果需要，显示之前也许要重新排序结果）。有关这个技术的一个示例，请参阅 Paging Through a Query Result。

•

另一项每次只返回一页记录的技术是创建 SQL 语句，把 TOP 谓词和嵌入式 SELECT 语句的使用结合在一起。此技术并不依赖于存在一种可唯一标识每一行的办法。使用这项技术的第一步是把所需页的数量与页大小相乘。然后将结果传递给 SQL Query 的 TOP 谓词，该查询以升序排列。再把此查询嵌入到另一个查询中，后者从降序排列的嵌入式查询结果中选择 TOP 页大小。实质上，返回的是嵌入式查询的最后一页。例如，要返回查询结果的第三页（页大小是 10），应该书写如下所示的命令：

SELECT TOP 10 * FROM  (SELECT TOP 30 * FROM Customers ORDER BY Id ASC) AS Table1ORDER BY Id DESC

注意，从查询中返回的结果页以降序显示。如果需要，应该重新排序。

•

如果数据不经常变动，可以在 DataSet 中本地维护一个记录缓存，以此提高性能。例如，可以在本地 DataSet 中存储 10 页有用的数据，并且只有当用户浏览超出缓存第一页和最后一页时，才从数据源中查询新数据。

有关更多信息，请参阅 .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 的一些技巧。

•

CommandBuilder 的使用应该限制在设计时或即席方案中。生成 DataAdapter 命令属性所必需的处理会影响性能。如果预先知道 INSERT/UPDATE/DELETE 语句的内容，就显式设置它们。一个比较好的设计技巧是，为 INSERT/UPDATE/DELETE 命令创建存储过程并显式配置 DataAdapter 命令属性以使用它们。

•

CommandBuilder 使用 DataAdapter 的 SelectCommand 属性确定其他命令属性的值。如果 DataAdapter 的 SelectCommand 本身曾经更改过，确保调用 RefreshSchema 以更新命令属性。

•

如果 DataAdapter 命令属性为空（命令属性默认情况下为空），CommandBuilder 仅仅为它生成一条命令。如果显式设置了命令属性，CommandBuilder 不会重写它。如果希望 CommandBuilder 为以前已经设置过的命令属性生成命令，就把命令属性设置为空。

批处理 SQL 语句

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

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

用多个表填充 DataSet

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

'Visual BasicDim 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 BasicDim 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 的常见问题。

•

在访问相关 Command 的任何输出参数之前，必须关闭 DataReader。

•

完成读数据之后总是要关闭 DataReader。如果使用 Connection 只是用于返回 DataReader，那么关闭 DataReader 之后立刻关闭它。

另外一个显式关闭 Connection 的方法是把 CommandBehavior.CloseConnection 传递给 ExecuteReader 方法，以确保相关的连接在关闭 DataReader 时被关闭。如果从一个方法返回 DataReader，而且不能控制 DataReader 或相关连接的关闭，则这样做特别有用。

•

不能在层之间远程访问 DataReader。DataReader 是为已连接好的数据访问设计的。

•

当访问列数据时，使用类型化访问器，例如，GetString、GetInt32 等。这使您不用进行将 GetValue 返回的 Object 强制转换成特定类型所需的处理。

•

一个单一连接每次只能打开一个 DataReader。在 ADO 中，如果打开一个单一连接，并且请求两个使用只进、只读游标的记录集，那么 ADO 会在游标生存期内隐式打开第二个、未池化的到数据存储区的连接，然后再隐式关闭该连接。对于 ADO.NET，“秘密”完成的动作很少。如果想在相同的数据存储区上同时打开两个 DataReaders，就必须显式创建两个连接，每个 DataReader 一个。这是 ADO.NET 为池化连接的使用提供更多控制的一种方法。

•

默认情况下，DataReader 每次 Read 时都要把整行加载到内存。这允许在当前行内随机访问列。如果不需要这种随机访问，为了提高性能，就把 CommandBehavior.SequentialAccess 传递给 ExecuteReader 调用。这将 DataReader 的默认行为更改为仅在请求时将数据加载到内存。注意，CommandBehavior.SequentialAccess 要求顺序访问返回的列。也就是说，一旦读过返回的列，就不能再读它的值了。

•

如果已经完成读取来自 DataReader 的数据，但仍然有大量挂起的未读结果，就在调用 DataReader 的 Close 之前先调用 Command 的 Cancel。调用 DataReader 的 Close 会导致在关闭游标之前检索挂起的结果并清空流。调用 Command 的 Cancel 会放弃服务器上的结果，这样，DataReader 在关闭的时候就不必读这些结果。如果要从 Command 返回输出参数，还要调用 Cancel 放弃它们。如果需要读取任何输出参数，不要调用 Command 的 Cancel，只要调用 DataReader 的 Close 即可。

二进制大对象 (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 框架数据提供程序的命令执行。

•

按照 ODBC CALL 语法使用 CommandType.Text 调用存储过程。使用 CommandType.StoredProcedure 只是秘密地生成 ODBC CALL 语法。

•

一定要设置 OleDbParameter 的类型、大小（如果适用）、以及精度和范围（如果参数类型是 numeric 或 decimal）。注意，如果不显式提供参数信息，OleDbCommand 会为每个执行命令重新创建 OLE DB 参数访问器。

使用 SqlCommand 的最佳实践

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

使用 Prepare 方法

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

显式指定架构和元数据

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

•

DataAdapter.Fill 方法，如果 DataSet 中没有表和列，DataAdapter.Fill 方法会在 DataSet 中创建表和列。

•

CommandBuilder，它会为单表 SELECT 命令生成 DataAdapter 命令属性。

•

CommandBuilder.DeriveParameters，它会填充 Command 对象的 Parameters 集合。

但是，每次用到这些特性，都会有性能损失。建议将这些特性主要用于设计时和即席应用程序中。在可能的情况下，显式指定架构和元数据。其中包括在 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 CustomersWHERE ((LastName = LastName) OR (LastName IS NULL AND LastName IS NULL))

把 Null 作为参数值传递

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

'Visual BasicDim 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。

返回页首

[1] [2] 下一页