SQL Server .NET Framework 数据提供程序连接池（转）

bgey

建立池连接可以显著提高应用程序的性能和可缩放性。SQL Server .NET Framework 数据提供程序自动为 ADO.NET 客户端应用程序提供连接池。您也可以提供几个连接字符串修饰符来控制连接池行为，请参见本主题内下文中“使用连接字符串关键字控制连接池”这一节。
池的创建和分配

当连接打开时，将根据一种精确的匹配算法来创建连接池，该算法会使连接池与连接中的字符串相关联。每个连接池都与一个不同的连接字符串相关联。当新连接打开时，如果连接字符串不精确匹配现有池，则将创建一个新池。

在以下示例中，将创建三个新的 SqlConnection 对象，但只需要使用两个连接池来管理这些对象。请注意，第一个和第二个连接字符串的差异在于为 Initial Catalog 分配的值。

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// Pool A is created.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";
conn.Open();      
// Pool B is created because the connection strings differ.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// The connection string matches pool A.

连接池一旦创建，直到活动进程终止时才会被毁坏。非活动或空池的维护只需要最少的系统开销。
连接的添加

连接池是为每个唯一的连接字符串创建的。当创建一个池后，将创建多个连接对象并将其添加到该池中，以满足最小池大小的要求。连接将根据需要添加到池中，直至达到最大池大小。

当请求 SqlConnection 对象时，如果存在可用的连接，则将从池中获取该对象。若要成为可用连接，该连接当前必须未被使用，具有匹配的事务上下文或者不与任何事务上下文相关联，并且具有与服务器的有效链接。

如果已达到最大池大小且不存在可用的连接，则该请求将会排队。当连接被释放回池中时，连接池管理程序通过重新分配连接来满足这些请求。对 Connection 调用 Close 或 Dispose 时，连接被释放回池中。

    警告   建议使用完 Connection 后始终将其关闭，以便连接可以返回到池中。这可以使用 Connection 对象的 Close 或 Dispose 方法来实现。不是显式关闭的连接可能不会添加或返回到池中。例如，如果连接已超出范围但没有显式关闭，则仅当达到最大池大小而该连接仍然有效时，该连接才会返回到连接池中。

    注意   不要在类的 Finalize 方法中对 Connection、DataReader 或任何其他托管对象调用 Close 或 Dispose。在终结器中，仅释放类直接拥有的非托管资源。如果类不拥有任何非托管资源，则不要在类定义中包含 Finalize 方法。

连接的移除

如果连接生存期已过期，或者连接池管理程序检测到与服务器的连接已断开，连接池管理程序将从池中移除该连接。请注意，只有在尝试与服务器进行通信后，才可以检测到这种情况。如果发现某连接不再连接到服务器，则会将其标记为无效。连接池管理程序会定期扫描连接池，查找已释放到池中并标记为无效的对象。找到后，这些连接将被永久移除。

如果存在与已消失的服务器的连接，那么即使连接池管理程序未检测到已断开的连接并将其标记为无效，仍有可能将此连接从池中取出。当发生这种情况时，将生成异常。但是，为了将该连接释放回池中，仍必须将其关闭。
事务支持

连接是根据事务上下文来从池中取出并进行分配的。请求线程和所分配的连接的上下文必须匹配。因此，每个连接池实际上又分为不具有关联事务上下文的连接以及 N 个各自包含与一个特定事务上下文的连接的子部分。

当连接关闭时，它将被释放回池中，并根据其事务上下文放入相应的子部分。因此，即使分布式事务仍然挂起，仍可以关闭该连接而不会生成错误。这样，您就可以在随后提交或中止分布式事务。
使用连接字符串关键字控制连接池

SqlConnection 对象的 ConnectionString 属性支持连接字符串键/值对，这些键/值对可用于调整连接池逻辑的行为。

下表描述了可用于调整连接池行为的 ConnectionString 值。
名称 默认值 说明
Connection Lifetime 0 当连接返回到池中时，将对它的创建时间和当前时间进行比较，如果时间间隔超过由 Connection Lifetime 指定的值（以秒为单位），则会毁坏该连接。在聚集配置中可以使用它来强制在运行服务器和刚联机的服务器之间达到负载平衡。

如果值为零 (0)，则将使池连接具有最大的超时期限。
Connection Reset 'true' 确定在从池中移除数据库连接时是否将其重置。对于 Microsoft SQL Server 版本 7.0，如果设置为 false，将避免在获取连接时经历一个额外的往返过程，但必须注意的是连接状态（如数据库上下文）不会被重置。
Enlist 'true' 当为 true 时，如果存在事务上下文，池管理程序将自动在创建线程的当前事务上下文中登记连接。
Max Pool Size 100 池中允许的最大连接数。
Min Pool Size 0 池中维护的最小连接数。
Pooling 'true' 当为 true 时，将从相应的池中取出连接，或者在必要时创建连接并将其添加到相应的池中。
连接池的性能计数器

SQL Server .NET Framework 数据提供程序添加了几个性能计数器，它们将使您能够微调连接池特性，检测与失败的连接尝试相关的间歇性问题，并检测与对 SQL Server 的超时请求相关的问题。

下表列出了可以在“.NET CLR 数据”性能对象下的“性能监视器”中访问的连接池计数器。
计数器 说明
SqlClient: Current # pooled and non pooled connections 当前池连接或非池连接的数目。
SqlClient: Current # pooled connections 当前所有池中与特定进程关联的连接的数目。
SqlClient: Current # connection pools 当前与特定进程关联的池的数目。
SqlClient: Peak # pooled connections 自特定进程开始以来所有池中的连接数峰值。请注意：此计数器只有在与特定进程实例关联时才可用。_Global 实例始终返回 0。
SqlClient: Total # failed connects 打开连接的尝试因任何原因而失败的总次数。

    注意   将 SQL Server .NET Framework 数据提供程序性能计数器与 ASP.NET 应用程序一起使用时，只有 _Global 实例是可用的。因此，性能计数器返回的值是所有 ASP.NET 应用程序的计数器值的总和。


连接池允许应用程序从连接池中获得一个连接并使用这个连接，而不需要为每一个连接请求重新建立一个连接。一旦一个新的连接被创建并且放置在连接池中，应用程序就可以重复使用这个连接而不必实施整个数据库连接创建过程。

连接池允许应用程序从连接池中获得一个连接并使用这个连接，而不需要为每一个连接请求重新建立一个连接。一旦一个新的连接被创建并且放置在连接池中，应用程序就可以重复使用这个连接而不必实施整个数据库连接创建过程。

当应用程序请求一个连接时，连接池为该应用程序分配一个连接而不是重新建立一个连接；当应用程序使用完连接后，该连接被归还给连接池而不是直接释放。

如何实现连接池
确保你每一次的连接使用相同的连接字符串（和连接池相同）；只有连接字符串相同时连接池才会工作。如果连接字符串不相同，应用程序就不会使用连接池而是创建一个新的连接。

优点
        使用连接池的最主要的优点是性能。创建一个新的数据库连接所耗费的时间主要取决于网络的速度以及应用程序和数据库服务器的（网络）距离，而且这个过程通常是一个很耗时的过程。而采用数据库连接池后，数据库连接请求可以直接通过连接池满足而不需要为该请求重新连接、认证到数据库服务器，这样就节省了时间。

缺点
       数据库连接池中可能存在着多个没有被使用的连接一直连接着数据库（这意味着资源的浪费）。

技巧和提示

1．  当你需要数据库连接时才去创建连接池，而不是提前建立。一旦你使用完连接立即关闭它，不要等到垃圾收集器来处理它。

2．  在关闭数据库连接前确保关闭了所有用户定义的事务。

3．  不要关闭数据库中所有的连接，至少保证连接池中有一个连接可用。如果内存和其他资源是你必须首先考虑的问题，可以关闭所有的连接，然后在下一个请求到来时创建连接池。



连接池FAQ
1．  何时创建连接池？

当第一个连接请求到来时创建连接池；连接池的建立由数据库连接的连接字符创来决定。每一个连接池都与一个不同的连接字符串相关。当一个新的连接请求到来时如果连接字符串和连接池使用的字符串相同，就从连接池取出一个连接；如果不相同，就新建一个连接池。

2．  何时关闭连接池？
当连接池中的所有连接都已经关闭时关闭连接池。

3．  当连接池中的连接都已经用完，而有新的连接请求到来时会发生什么？
当连接池已经达到它的最大连接数目时，有新的连接请求到来时，新的连接请求将放置到连接队列中。当有连接释放给连接池时，连接池将新释放的连接分配给在队列中排队的连接请求。你可以调用close和dispose将连接归还给连接池。

4．  我应该如何允许连接池？
对于.NET应用程序而言，默认为允许连接池。（这意味着你可以不必为这件事情做任何的事情）当然，如果你可以在SQLConnection对象的连接字符串中加进Pooling=true;确保你的应用程序允许连接池的使用。

5．  我应该如何禁止连接池？
ADO.NET默认为允许数据库连接池，如果你希望禁止连接池，可以使用如下的方式：
1)        使用SQLConnection对象时，往连接字符串加入如下内容：Pooling=False;
2)        使用OLEDBConnection对象时，往连接字符串加入如下内容：OLE DB Services=-4;

-----------------------------------------------------------------------------------------------------------------------------------------------
连接池
    连接池是与业务对象对等的中间层部分，启动一个新的业务对象时，会检查连接池现有的连接，若有就使用它，否则就创建一新连接。包含在ADO.NET中的每个.NET数据提供程序都可实现连接池。
    默认连接池是打开的，当关闭连接时，并不是真正关闭实际的数据连接，若超过默认时间（60秒）未再次使用，才会真正被关闭。
    若不想存储连接？将下面的字符串添加到OLE DB连接字符串中去：OLE DB Services=-4; 若使用SqlConnection对象，添加下字符：Pooling=False;

Connection类
CreateCommand方法可节省一行代码
OleDbCommand cmd=cn.CreateCommand();
与下面两句等价：
OleDbCommand cmd=new OleDbCommand();
cmd.Connection=cn;
GetOleDbSchemaTable方法获取数据库架构信息
有两个参数schema（定要返回的架构表）和 restrictions （限制值的 Object 数组）重点是Restrictions参数数组，结构是{“TABLE_CATALOG“,“TABLE_SCHEMA“,“TABLE_NAME“, “COLUMN_NAME“},具体可参考MSDN
-----------------------------------------------------------------------------------------------------------------------------------------------
.NET框架组件数据提供程序



　　.NET框架组件中的数据提供程序是应用程序与数据源之间的一座桥梁。它允许你从数据源返回查询的结果，在数据源上执行命令，把数据集中的改变提交到数据源。本文包含了怎样选择最适合需求的.NET框架组件数据提供程序。

　　使用哪种.NET框架组件数据提供程序

　　为了使应用程序获得最佳的性能，需要使用最适合数据源的.NET框架组件数据提供程序。

　　连接到SQL Server 7.0及以上版本

　　当连接到SQL Server 7.0及以上版本时，为了获得最佳性能应该使用SQL Server .NET 数据提供程序。SQL Server .NET数据提供程序设计为直接访问SQL Server，没有其它附加的技术层。下图（图1）说明了访问SQL Server 7.0及以上版本的多种技术之间的差别。



　　图1.访问SQL Server 7.0及以上版本的连接方法

　　连接到ODBC数据源

　　名字空间中的ODBC .NET数据提供程序的结构与SQL Server和OLE DB的.NET数据提供程序相同。ODBC .NET数据提供程序使用"ODBC"前缀和标准的ODBC连接字符串。

　　注意：ODBC .NET数据提供程序包含在.NET框架组件1.1以上版本，包含ODBC .NET数据提供程序的名字空间是System.Data.Odbc。

使用DataReader、DataSet、DataAdapter和DataView



　　ADO.NET提供两个对象用于检索关系型数据并把它存储在内存中，分别是DataSet和DataReader。 DataSet提供内存中关系数据的表现--包括表和次序、约束等表间的关系的完整数据集合。DataReader提供快速、只向前、只读的来自数据库的数据流。

　　使用DataSet时，一般使用DataAdapter（也可能是CommandBuilder）与数据源交互，用 DataView对DataSet中的数据进行排序和过滤。DataSet可以被继承来建立强化类型的DataSet，用于暴露表、行、列作为强化类型对象属性。

　　下面的内容包含什么时候使用DataSet或DataReader，以及怎样优化访问它们所包含的数据，也包括怎样优化DataAdapter和DataView的使用（也包括CommandBuilder）。

　　DataSet与DataReader的对比

　　在设计应用程序时，决定使用DataSet还是DataReader需要考虑应用程序需要的功能。

　　使用DataSet是为了实现应用程序的下述功能：

　　l 操作结果中的多个分离的表。

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

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

　　l 通过缓冲重复使用相同的行集合以提高性能（例如排序、搜索或过滤数据）。

　　l 每行执行大量的处理。在使用DataReader返回的行上进行扩展处理将使连接存在的时间比必要的更长，从而降低效率。

　　l 使用XML操作（例如XSLT转换和Xpath查询）维护数据。

　　在应用程序需要以下功能时使用DataReader：

　　l 不需要缓冲数据。

　　l 正在处理的结果集太大而不能全部放入内存中。

　　l 需要迅速一次性访问数据，采用只向前的只读的方式。

　　注意：当填充DataSet的时候，DataAdapter使用DataReader。因此使用DataAdapter代替DataSet获得的性能是节约了DataSet消耗的内存和组装DataSet所需要的周期。这种性能的提高大部分是有名无实的，因此你应该根据需要的功能为基础来做设计决定。

　　使用强类型DataSet的好处

　　使用DataSet的另一个好处是它能被继承用于建立强类型的DataSet。强类型DataSet的好处包括设计时的检查和强类型DataSet 的Visual Studio .NET语句填充。当你为DataSet固定了大纲或关系结构时，就能建立强类型DataSet，把行和列作为对象的属性而不是项的集合。例如，作为暴露顾客表的某一行的列名的代替，你可以暴露Customer对象的 Name属性。强类型的DataSet衍生自DataSet类，因此不会牺牲DataSet的任何功能，也就是说，强类型的DataSet也可以是远程的，并作为数据绑定控件（例如DataGrid）的数据源提供。如果不知道大纲，也能通过使用通常的DataSet获得好处，但是丧失了强类型 DataSet的附加特性。

　　在强类型DataSet中处理空值

　　使用强类型DataSet时，你能给DataSet 的XML大纲定义语言（XSD）作注解以确保强类型DataSet正确的处理空（Null）的引用。空值（nullValue）注释使你能用 String.Empty这个特定值代替DBNull、保持了空引用、或者产生一个异常。选择其中的哪个依赖于应用程序的内容，默认情况下遇到空引用将产生一个异常。

　　刷新DataSet中的数据

　　如果你希望使用更新后的值从服务器刷新数据集中的值，使用DataAdapter.Fill。如果主键定义在数据表上，DataAdapter.Fill基于主键匹配新行，并把服务器的数据改成已存在的行。被刷新行的RowState设置为Unchanged，即使在刷新前它被修改过。注意如果给数据表定义了主键， DataAdapter.Fill添加新行可能重复主键值。

　　如果希望用服务器的当前值刷新一个表，并且保持表中行的改变，你必须首选使用DataAdapter.Fill组合它，填充一个新的数据表，接着将该数据表合并（Merge）进一个数据集，并把preserveChanges值设为true。

　　在DataSet中搜索数据

　　在一个数据集中查询符合特定条件的行时，使用基于索引（index-based）的查看表将提高性能。给数据表指定主键（PrimaryKey）值时，就建立了一个索引。当为数据表建立数据视图（DataView）时也建立了索引。下面是一些使用基于索引查看的技巧：

　　如果查询是在数据表的主键列上进行的，使用DataTable.Rows.Find代替DataTable.Select。

　　查询非主键列，可以使用数据视图来提高多个数据查询的速度。当给数据视图添加排序时，将建立搜索时使用的索引。数据视图暴露了查询下层数据表的Find和FindRows方法。

　　如果你不是查询表的排序视图，也可以通过为数据表建立数据视图获得基于索引的查看表的好处。注意如果你执行数据上的多个查询这是唯一的好处。如果你只执行单个查询，需要建立索引的过程将因为使用索引而降低了性能。

　　数据视图（DataView）结构

　　当数据视图建立后，并且当Sort、RowFilter或RowStateFilter或者属性被修改时，数据视图为下层数据表中的数据建立索引。当建立数据视图对象时，使用把Sort、RowFilter和RowStateFilter值作为参数的数据视图构造函数。结果是建立了一次索引。建立"空" 数据视图，然后设置Sort、RowFilter和RowStateFilter属性将导致至少两次建立索引。

　　分页

　　ADO.NET给了你从数据源返回什么数据的明显控制，也提供了在数据集中存储了多少数据的控制。在设计应用程序时可以考虑以下技巧：

　　l 避免使用DataAdapter.Fill，它使用了startRecord和maxRecords值。使用这种方式填充数据集时，数据集只填充由 maxRecords参数指定的记录个数（从参数startRecord指定的记录开始），而不管返回的整个查询。这导致读取过时的"不想要的"记录，同时使用了不必要的服务器资源来返回补充记录。

　　l 用于在某个时候只返回一页记录的技术之一是建立一个SQL语句，该语句包含一个WHERE和ORDER BY子句，并有TOP判定。这种技术依赖于识别每个唯一行的方法。当导航到下一页的记录时，修改WHERE子句使它包含所有唯一标识比当前页标识大的记录；当导航到前面一页时，修改WHERE子句使它包含所有唯一标识比当前页标识小的记录。对于两种查询都只返回记录的TOP页的记录。当导航到前面一页时需要对记录进行降序排列，这将返回查询的末尾页（如果需要可以在显示前对记录进行重新排序）。

　　l 另一种技术是建立一个SQL语句包含TOP判定和嵌入的SELECT语句。这种技术不是基于唯一的识别每行的方法。使用这种技术的第一步是把页面的大小与想得到的页面数量相乘。接着把该数值传递给SQL查询的TOP判定，并按升序排序。接着把这个查询嵌入另一个查询，该查询从嵌入的查询结果中选择TOP页面大小，按降序排列。本质上返回的是嵌入的查询的末尾页面。例如，为了返回页面大小是10的查询结果的第三页，使用下面的命令：

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



　　l 如果数据不是经常改变，能通过本地维护数据集里面的记录缓存来提高性能。例如，你能在本地数据集中存储10页数据，只在用户导航超出第一页或最后一页时才查询数据源检索新的数据。

　　使用大纲（Schema）填充数据集

　　当用数据填充数据集时，DataAdapter.Fill方法使用数据集的已存在的大纲并把它与Select命令（SelectCommand）返回的数据进行组合。如果数据集中没有与被填充的表匹配的表的名字，Fill方法将建立一张表。默认情况下，Fill只定义列和列的类型。

　　你能通过设置数据适配器的MissingSchemaAction属性来重载Fill的默认的行为。例如，要使Fill建立的表包含主键信息、唯一约束、列属性、是否允许空值、列的最大长度、只读列、自动增加列等等，只需要指定DataAdapter.MissingSchemaAction为 MissingSchemaAction.AddWithKey。作为选择，你能在调用DataAdapter.Fill前调用 DataAdapter.FillSchema来确保数据集被填充时大纲已经准备好了。

　　调用FillSchema将再次访问服务器并检索附加的大纲信息。为了提高性能，最好指定数据集的大纲，或者在调用Fill前设置数据适配器的MissingSchemaAction。

　　使用命令构造器（CommandBuilder）的经验

　　命令构造器根据数据适配器的SelectCommand属性自动生成数据适配器的InsertCommand、UpdateCommand和DeleteCommand属性（假若SelectCommand执行单个表上的选择（SELECT））。

　　l 命令构造器的使用应该限制在设计时或者ad-hoc情况下。需要的生成数据适配器命令属性的过程妨碍了性能。如果你预先知道 INSERT/UPDATE/DELETE语句的内容，应该显式地设置它们。好的设计技巧是为INSERT/UPDATE/DELETE命令建立存储过程并明确地配置数据适配器命令属性来使用它们。

　　l 命令构造器使用数据适配器的SelectCommand属性来决定其它命令属性的值。如果数据适配器的SelectCommand自身改变了，一定要调用RefreshSchema来更新命令属性。

　　l 如果命令属性是空的（默认情况下命令属性是空的），命令构造器只为数据适配器命令属性生成一个命令。如果你明确地设置一个命令属性，命令构造器不会覆盖它。如果你希望命令构造器为一个已经设置了的命令属性生成一个命令，要把命令属性设置为空。

　　批处理SQL语句

　　很多数据库支持在一个命令执行中组合、批处理多个命令执行。例如，SQL Server允许你使用分号分隔命令。把多个命令组合成为一个减少了对服务器的访问次数，可以提高应用程序的性能。例如，你能在本地应用程序中存储所有的删除，并在数据源发布一个批处理命令调用来删除它们。

　　尽管它提高了性能，但是也增加了应用程序管理数据集里面数据更新的复杂性。为了保持简单性，你也许会为数据集中的每个数据表建立一个数据适配器。

　　使用多个表填充数据集　　

　　如果使用批处理SQL语句检索多个表并填充一个数据集，第一张表的名字使用Fill方法指定的表名，后面的表的名字是Fill方法指定的名字加上一个数字，从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"的数据表中，从Orders表中得到的数据放在"Customers1"数据表中。

　　你可以在数据表被填充后修改"Customers1"表的属性为"Orders"。但是接下来的填充的结果是"Customers"表被重新填充，但是 "Orders"表被略过了并且建立了另一个"Customers1"表。为了避免这种情况，建立一个把"Customers1"映射到"Orders" 的DataTableMapping，并且为其它的表建立映射。例如：

'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提高性能的一些技巧：

　　l 在访问任何与命令（Command）相关的输出参数前DataReader必须关闭。

　　l 在读完数据后就关闭DataReader。如果你正在使用的连接只返回该DataReader，在关闭DataReader后立即关闭连接。

　　l 另一种明确地关闭连接的方法是给ExecuteReader方法传递CommandBehavior.CloseConnection以确保当 DataReader关闭时相关的连接关闭了。如果你从某个方法返回DataReader，并且没有办法控制DataReader或者相关的连接关闭的情况下特别有用。

　　l DataReader不能在层之间远程访问。DataReader是设计用于连接数据访问的。

　　l 使用类型化的存取程序（例如GetString、GetInt32等等）来访问列数据。这节省了将GetValue返回的对象作为特定类型的必要的处理。

　　l 在某一时刻只有一个DataReader能够打开。。在ADO中，如果你打开一个连接并请求两个使用只向前的只读游标的记录集，ADO隐性地为游标的生命周期的数据存储打开第二个不在连接池中的连接，接着隐性地关闭它。在ADO.NET中，如果你想在同一个数据存储上同时打开两个DataReader，你必须明确地建立两个连接，每个DataReader一个。通过这种方法ADO.NET给了你对连接池使用的更多控制。

　　l 默认情况下，DataReader在每个Read方法中把整个行载入内存中。这允许你随机访问当前行的任意列。如果随机访问是不必要的，为了提高性能，把 CommandBehavior.SequentialAccess传递给ExecuteReader调用。这改变了DataReader的默认行为，只在需要时才把数据载入内存。注意CommandBehavior.SequentialAccess要求你按次序访问返回的列。也就是，一旦你读过了返回的某个列，就不能再次读取它的值了。

　　l 如果你结束了从DataReader中读取数据，但是仍然有大量的未读取的结果等待，那么调用Command的Cancel比调用DataReader 的Close好。调用DataReader 的Close引起它检索等待的结果并且先清空流后关闭游标。调用Command的 Cancel删除服务器上的结果，因此当DataReader关闭时，它不需要再读取结果。如果你从Command返回输出参数，则调用Cancel删除它们。如果你要读取任何输出参数，不要调用Command 的Cancel；最后调用DataReader的 Close。

　　二进制大对象（BLOB）

　　当使用DataReader检索二进制大对象时，必须给ExecuteReader方法调用传递 CommandBehavior.SequentialAccess。因为DataReader的默认行为是在每个Read中把整行载入内存中，但是由于 BLOB可能很大，结果可能是一个BLOB对象使用大量的内存。SequentialAccess把DataReader的行为设置为只载入必要的数据，接着你能使用GetBytes或者GetChars控制每次载入多少数据。

　　记住使用SequentialAccess时，你不能无序地访问DataReader返回的不同字段。也就是说，如果查询返回三个列，第三个是BLOB，并且你希望访问前两个列的数据，你必须先访问第一个列，接着在访问BLOB数据前访问第二个列。这是因为现在数据是按次序返回的，在DataReader读过它后不能再次访问。

　　使用命令（Command）

　　ADO.NET为命令执行提供了几个不同的方法，同时也为优化命令的执行提供了不同的选择。下面的技巧包括怎样选择最好的命令执行和怎样提供一个被执行命令的性能。

　　使用OleDbCommand的最好经验

　　不同的.NET框架组件数据提供程序之间的命令执行是尽可能标准的。但是，在这些数据提供程序间也有些不同。下面的一些可以调整OLE DB数据提供程序命令执行的技巧：

　　l 与ODBC CALL语法一起使用CommandType.Text来调用存储过程。仅仅使用CommandType.StoredProcedure生成ODBC CALL语法。

　　l 一定要设置OleDbParameter类型、大小（如果可用）、精度和小数位（如果参数是数值型或者十进制型）。注意如果你没有明确地提供参数信息，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中当用户没有指定元数据信息时，有很多对象推导这些信息。例如：

　　l DataAdapter.Fill方法，如果不存在的话，它在记录集中建立表和列。

　　l CommandBuilder，它为单个的SELECT语句生成数据适配器命令属性。

　　l CommandBuilder.DeriveParameters，它组合Command对象的Parameters集合。

　　但是每次使用这些特性时都会造成效率降低。我们推荐主要在设计时和ad-hoc应用程序中使用这些特性。在可能的情况下，明确地指定大纲和元数据，包括在数据集中定义表和列，定义数据适配器的Command属性，定义Command的Parameter信息。

　　ExecuteScalar和ExecuteNonQuery

　　如果你希望返回单个值，例如Count（*）、 Sum（Price）、或者Avg（Quantity），你可以使用Command.ExecuteScalar。ExecuteScalar返回第一行第一列的值，返回结果集是数量值。ExecuteScalar通过一步完成不仅简化了代码而且提高了性能，而这些工作在使用DataReader时将需要两个处理步骤。

　　当使用不返回行的SQL语句时，类似修改数据（例如插入、更新或者删除）或者只返回输出参数或值，使用ExecuteNonQuery。它通过建立一个空DataReader删除了任何必要的处理。

　　空值的检测　　

　　如果数据库的某张表的一个列允许空值，你不能使用某个与空值相等的参数来测试它。作为代替，需要编写一个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属性没有设置为开始就与连接关联的Transaction， Command失败并出现异常。

　　使用连接

　　高性能的应用程序保持使用最少次数的数据源的连接，也利用了类似连接池的性能增强技术。下面的技巧帮你使用ADO.NET连接数据源时获得更好的性能。

　　连接池

　　SQL Server、OLE DB和.NET框架组件数据提供程序隐性为ODBC提供了连接池。你可以在连接字符串中指定不同的属性控制连接池的行为。

　　用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对象时，明确地关闭它们。尽管无用单元收集程序最终会清除这些对象，并释放连接和其它可管理资源，但是无用单元收集只在必要时才发生。因此确保昂贵的资源明确地被释放仍然是你的职责。此外，连接如果没有被明确的释放将使它不会返回连接池。例如，如果连接池到达了最大值并且一个连接还有效，该超出范围并且没有被明确关闭的连接才返回到连接池。

　　注意不要在类的Finalize方法中调用Connection、DataReader、或者其它可管理对象的Close或者Dispose方法。在该方法中只释放类直接拥有的不可管理资源。如果类中没有任何不可管理资源，在类定义中不要包含Finalize方法。

　　在C#中使用"Using"语句

　　对C#程序员来说，确保经常关闭Connection和DataReader对象的一个简便方法是使用using语句。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));
  }
}



　　避免访问OleDbConnection.State属性

　　如果连接打开了， OleDbConnection.State使本地OLE DB向DATASOURCEINFO属性集调用IDBProperties.GetProperties来获取 DBPROP_CONNECTIONSTATUS属性，这可能引起重新返回数据源。换句话说，检查State属性可能花费很大。因此只在必要时才检查 State属性。如果你需要经常检查该属性，你监听OleDbConnection的StateChange事件会使应用程序的性能更好。

　　与XML集成

　　ADO.NET在数据集中提供了广泛的XML集成，并且暴露了一些SQL Server 2000及以上版本所提供的XML功能。你能使用SQLXML 3.0来访问SQL Server 2000及以上版本所提供的XML功能。下面是使用XML和ADO.NET的一些技巧和信息。

　　数据集与XML　　

　　数据集与XML紧密结合，提供了执行下面操作的能力：

　　l 从XSD大纲载入数据集的大纲或者关系结构。

　　l 从XML载入数据集的内容。

　　l 当没有提供大纲时根据XML文档的内容推断数据集的大纲。

　　l 将数据集的大纲写成XSD大纲。

　　l 将数据集的内容写成XML。

　　l 使用数据集同步访问数据的相关表现、使用XmlDataDocument访问数据的层次表现。

　　注意：你能使用这种同步在数据集的数据上应用XML功能（例如Xpath查询和XSLT变换），或提供所有的关系型视图，或者在保持原XML不变的情况下提供XML文档中的数据的子集。

　　大纲接口

　　当从XML文件中载入数据集时，你能从XSD大纲中载入数据集的大纲，或者在载入数据前预先定义表和列。如果没有XSD大纲，并且你也不知道为XML文件的内容定义怎样的表和列，你能根据XML文档的结构推断大纲。

　　大纲推理作为迁移工具是有用的，但是由于推理过程有下面的限制，它只限于应用程序设计时使用：

　　l 推理大纲引入了附加的处理将降低应用程序的性能。

　　l 所有推理列的类型都是字符串型。

　　l 推理过程是不确定的。这就是说，它基于XML文件而不是预定的大纲。结果是你可能有两个XML文件，它们有相同的预定大纲，却因为它们的内容不同形成了两个完全不同的推理大纲。

　　为XML查询服务的SQL Server

　　如果你为XML查询返回SQL Server 2000结果，你能使用.NET框架组件SQL Server数据提供程序直接用SqlCommand.ExecuteXmlReader方法建立一个XmlReader。

　　SQLXML可管理类

　　在.NET框架组件中有一些类暴露了XML为SQL Server 2000提供的功能。这些类都在Microsoft.Data.SqlXml名字空间中，添加了执行Xpath查询和XML模板文件的能力，也能把XSLT转换为数据。
避免自动增加（Auto-Increment）值冲突

　　和许多数据源一样，DataSet允许你在添加新行时识别自动增加值的列。在DataSet中使用自动增加列时，由于数据源也有自动增加列，需要避免添加到DataSet中的本地行号与添加到数据源中的行之间的冲突。

　　例如，假设一个表的自动增加主键列是CustomerID。两个新客户信息添加到该表，获得的自动增加CustomerID值分别是1和2。接着只有第二个客户行给数据适配器传递的Update方法，在数据源中新添加的行接受的自动增加CustomerID值是1，与数据集中的2不匹配。当数据适配器用返回值填充表中的第二行时，由于第一个顾客行的CustomerID是1，便出现了错误。

　　为了避免这种情况，我们推荐当使用数据源和数据集中有自动增加列时，数据集中的该列的AutoIncrementStep设为－1，AutoIncrementSeed设为0，同时确保数据源中生成的自动增加标识值从1开始，步长为正。结果是数据集生成负的自动增加值，不会与数据源产生的正自动增加值冲突。另一种选择是使用Guid类型的列带有自动增加列，该算法产生的Guid值在数据集和数据源中永远不同。

　　如果你的自动增加列永远简单的作为唯一值，没有其它的意义，考虑使用Guid代替自动增加列。它们是唯一的，避免了做另外的工作处理自动增加列。

　　查找优化的并发性故障

　　因为DataSet被设计为从数据源断开，所有必须确保当多个客户端更新数据源的数据时应用程序避免冲突。

　　测试优化并发性错误有多种技术。一种是在表的列中包含时间戳。另一种技术是通过使用SQL语句中的WHERE条件检测来验证行中所有的源列值与数据库中的匹配。

　　多线程编程

　　ADO.NET的优化是为了提高性能、吞吐量和可伸缩性。结果是ADO.NET不锁定资源并且只能在单个线程中使用，其中一个例外是DataSet，它对多个阅读程序来说是线程安全安的。但是在写的时候必须锁定DataSet。

　　只在必要的时候使用COM交互操作（Interop）访问ADO

　　ADO.NET被设计成大量应用程序的最佳解决方案。但是，有些应用程序需要只能使用ADO对象。在这些情况下，应用程序能使用COM交互操作访问 ADO。注意使用COM交互操作访问ADO的数据将极大的降低性能。设计应用程序时，在实现使用COM交互操作访问ADO这种设计前首选决定 ADO.NET是否符合设计需要。

避免自动增加（Auto-Increment）值冲突

　　和许多数据源一样，DataSet允许你在添加新行时识别自动增加值的列。在DataSet中使用自动增加列时，由于数据源也有自动增加列，需要避免添加到DataSet中的本地行号与添加到数据源中的行之间的冲突。

　　例如，假设一个表的自动增加主键列是CustomerID。两个新客户信息添加到该表，获得的自动增加CustomerID值分别是1和2。接着只有第二个客户行给数据适配器传递的Update方法，在数据源中新添加的行接受的自动增加CustomerID值是1，与数据集中的2不匹配。当数据适配器用返回值填充表中的第二行时，由于第一个顾客行的CustomerID是1，便出现了错误。

　　为了避免这种情况，我们推荐当使用数据源和数据集中有自动增加列时，数据集中的该列的AutoIncrementStep设为－1，AutoIncrementSeed设为0，同时确保数据源中生成的自动增加标识值从1开始，步长为正。结果是数据集生成负的自动增加值，不会与数据源产生的正自动增加值冲突。另一种选择是使用Guid类型的列带有自动增加列，该算法产生的Guid值在数据集和数据源中永远不同。

　　如果你的自动增加列永远简单的作为唯一值，没有其它的意义，考虑使用Guid代替自动增加列。它们是唯一的，避免了做另外的工作处理自动增加列。

　　查找优化的并发性故障

　　因为DataSet被设计为从数据源断开，所有必须确保当多个客户端更新数据源的数据时应用程序避免冲突。

　　测试优化并发性错误有多种技术。一种是在表的列中包含时间戳。另一种技术是通过使用SQL语句中的WHERE条件检测来验证行中所有的源列值与数据库中的匹配。

　　多线程编程

　　ADO.NET的优化是为了提高性能、吞吐量和可伸缩性。结果是ADO.NET不锁定资源并且只能在单个线程中使用，其中一个例外是DataSet，它对多个阅读程序来说是线程安全安的。但是在写的时候必须锁定DataSet。

　　只在必要的时候使用COM交互操作（Interop）访问ADO

　　ADO.NET被设计成大量应用程序的最佳解决方案。但是，有些应用程序需要只能使用ADO对象。在这些情况下，应用程序能使用COM交互操作访问 ADO。注意使用COM交互操作访问ADO的数据将极大的降低性能。设计应用程序时，在实现使用COM交互操作访问ADO这种设计前首选决定 ADO.NET是否符合设计需要。