Eric's Zone...

SqlDataReader和DataSet的概述

Ericdyx — Thu, 03 Feb 2005 18:21:00 GMT

Performance

This section introduces a number of common data access scenarios, and for each one, provides details about the most high-performance and scalable solution in terms of ADO.NET data access code. Where appropriate, performance, functionality, and development effort are compared. This section considers the following functional scenarios:

. Retrieving a result set and iterating through the retrieved rows.
. Retrieving a single row with a specified primary key.
. Retrieving a single item from a specified row.
. Checking to see whether or not a row with a particular primary key exists. This is a variation of the single item lookup scenario in which a simple Boolean return is sufficient.

Retrieving Multiple Rows

In this scenario, you want to retrieve a tabulated set of data and iterate through the retrieved rows to perform an operation. For example, you might want to retrieve a set of data, work with it in disconnected fashion, and pass it to a client application as an XML document (perhaps through a Web service). Alternatively, you might want to display the data in the form of a HTML table.

To help determine the most appropriate data access approach, consider whether you require the added flexibility of the (disconnected) DataSet object, or the raw performance offered by the SqlDataReader object, which is ideally suited to data presentation in business-to consumer (B2C) Web applications.

Data Binding

All three of these objects can act as data sources for data-bound controls, although the DataSet and DataTable can act as data sources for a wider variety of controls than the SqlDataReader. This is because the DataSet and DataTable implement IListSource (yielding IList), whereas the SqlDataReader implements IEnumerable. A number of WinForm controls capable of data binding require a data source that implements IList.

This difference is due to the type of scenario for which each object type is designed. The DataSet (which includes the DataTable) is a rich, disconnected structure suited to both Web and desktop (WinForm) scenarios. The data reader, on the other hand, is optimized for Web applications that require optimized forward-only data access.

关于ConnectionString的理解总结

Ericdyx — Thu, 03 Feb 2005 16:53:00 GMT

1）使用Trusted_Connection=true的情况
在Sql Server中的“安全性”那里必须建立网络访问的系统用户的帐号，该帐号的权限应该是包括对对应数据库实例的操作权限，然后在web.config中的authentication结点的值应该改为windows，这样网络访问就可以通过windows帐号访问数据库，而无需在web.config里明文写上uid和pwd。

2）使用uid和pwd
sqlserver中一定要有该用户，并有相应的权限，但这个帐号不是系统的帐号，然后在sqlserver的验证那里一定要设置“sqlserver和windows验证”，在web.config中设置authentication结点的值为forms

Managing Database Connections

Ericdyx — Wed, 02 Feb 2005 04:09:00 GMT

Managing Database Connections

Database connections represent a critical, expensive, and limited resource, particularly in a multitier Web application. It is imperative that you manage your connections correctly because your approach can significantly affect the overall scalability of your application. Also, think carefully about where to store connection strings. You need a configurable and secure location.

When managing database connections and connection strings, you should strive to:

Help realize application scalability by multiplexing a pool of database connections across multiple clients.
Adopt a configurable and high performance connection pooling strategy.
Use Windows authentication when accessing SQL Server.
Avoid impersonation in the middle tier.
Store connection strings securely.
Open database connections late and close them early.

Using Connection Pooling

Database connection pooling allows an application to reuse an existing connection from a pool instead of repeatedly establishing a new connection with the database. This technique can significantly increase the scalability of an application, because a limited number of database connections can serve a much larger number of clients. This technique also improves performance, because the significant time required to establish a new connection can be avoided.

Configuring SQL Server .NET Data Provider Connection Pooling

You can configure connection pooling by using a set of name-value pairs, supplied by means of the connection string. For example, you can configure whether or not pooling is enabled (it is enabled by default), the maximum and minimum pool sizes, and the amount of time that a queued request to open a connection can block. The following is an example connection string that configures the maximum and minimum pool sizes.

"Server=(local); Integrated Security=SSPI; Database=Northwind; 
Max Pool Size=75; Min Pool Size=5"

When a connection is opened and a pool is created, multiple connections are added to the pool to bring the connection count to the configured minimum level. Connections can be subsequently added to the pool up to the configured maximum pool count. When the maximum count is reached, new requests to open a connection are queued for a configurable duration.

Storing Connection Strings

To store database connection strings, you have a variety of options with different degrees of flexibility and security. Although hard coding a connection string within source code offers the best performance, file system caching ensures that the performance degradation associated with storing the string externally in the file system is negligible. The extra flexibility provided by an external connection string, which supports administrator configuration, is preferred in virtually all cases.

Using XML Application Configuration Files

You can use the <appSettings> element to store a database connection string in the custom settings section of an application configuration file. This element supports arbitrary key-value pairs, as illustrated in the following fragment:

<configuration>
 <appSettings>
  <add key="DBConnStr"
     value="server=(local);Integrated Security=SSPI;database=northwind"/>
 </appSettings>
</configuration>

Note The <appSettings> element appears under the <configuration> element and not directly under <system.web>.

Advantages

Ease of deployment. The connection string is deployed along with the configuration file through regular .NET xcopy deployment.
Ease of programmatic access. The AppSettings property of the ConfigurationSettings class makes reading the configured database connection string an easy task at run time.
Support of dynamic update (ASP.NET only). If an administrator updates the connection string in a Web.config file, the change will be picked up the next time the string is accessed, which for a stateless component is likely to be the next time a client uses the component to make a data access request.

Disadvantages

Security. Although the ASP.NET Internet Server Application Programming Interface (ISAPI) dynamic-link library (DLL) prevents clients from directly accessing files with a .config file extension and NTFS permissions can be used to further restrict access, you might still want to avoid storing these details in clear text on a front-end Web server. For added security, store the connection string in encrypted format in the configuration file.

More Information

You can retrieve custom application settings by using the static AppSettings property of the System.Configuration.ConfigurationSettings class. This is shown in the following code fragment, which assumes the previously illustrated custom key called DBConnStr:

using System.Configuration;
private string GetDBaseConnectionString()
{
  return ConfigurationSettings.AppSettings["DBConnStr"];
}
  Connection Usage Patterns
Irrespective of the .NET data provider you use, you must always: 
Open a database connection as late as possible. 
Use the connection for as short a period as possible. 
Close the connection as soon as possible. The connection is not returned to the pool until it is closed through either the Close or Dispose method. You should also close a connection even if you detect that it has entered the broken state. This ensures that it is returned to the pool and marked as invalid. The object pooler periodically scans the pool, looking for objects that have been marked as invalid. 
To guarantee that the connection is closed before a method returns, consider one of the approaches illustrated in the two code samples that follow. The first uses a finally block. The second uses a C# using statement, which ensures that an object's Dispose method is called.
The following code ensures that a finally block closes the connection. Note that this approach works for both Visual Basic .NET and C# because Visual Basic .NET supports structured exception handling.
public void DoSomeWork()
{
  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("CommandProc", conn );
  cmd.CommandType = CommandType.StoredProcedure;

  try
  {
    conn.Open();
    cmd.ExecuteNonQuery();
  }
  catch (Exception e)
  {
    // Handle and log error
  }
  finally
  {
    conn.Close();
  }
}
  
The following code shows an alternate approach that uses a C# using statement. Note that Visual Basic .NET does not provide a using statement or any equivalent functionality.
public void DoSomeWork()
{
  // using guarantees that Dispose is called on conn, which will
  // close the connection.
  using (SqlConnection conn = new SqlConnection(connectionString))
  {
    SqlCommand cmd = new SqlCommand("CommandProc", conn);
    fcmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    cmd.ExecuteQuery();
  }
}

打包中把.net framework一起打包的方法

Ericdyx — Sun, 30 Jan 2005 17:54:00 GMT

Creating the Settings.ini file

The Setup.exe Bootstrapper sample uses the external file, settings.ini, to determine the following:

The location of Dotnetfx.exe and the host application.
The language version of the .NET Framework for which it is checking.
Custom strings to use for Setup.exe dialog boxes.

The default location for settings.ini is in the same folder as Setup.exe. You can copy the settings.ini file provided with the Setup.exe Bootstrapper sample and then edit the file as necessary to specify the correct values for your application.

Specify the location of Dotnetfx.exe and the host application

The Setup.exe Bootstrapper sample requires Dotnetfx.exe and the host .NET Framework application to be in a known location relative to Setup.exe. The Setup.exe Bootstrapper sample uses the external file, settings.ini, to determine the location of Dotnetfx.exe and the host application. You can copy the settings.ini file provided with the sample and then edit the file to point to the locations of your .NET Framework host application and Dotnetfx.exe.

The following examples of settings.ini files illustrate how to point to a Windows Installer package (.msi file) for a host .NET Framework application and Dotnetfx.exe in various locations relative to the location of Setup.exe.

The following example settings.ini file points to the Windows Installer package for a .NET Framework application, mySetup.msi, and Dotnetfx.exe located in the same folder as Setup.exe.

[Bootstrap]
Msi=mySetup.msi
'LanguageDirectory=
ProductName= My Application 
DialogText=
CaptionText=My Application
ErrorCaptionText= My Application Error
FxInstallerPath=c:\myProjectFolder\

The following example settings.ini file points to the Windows Installer package for a .NET Framework application, mySetup.msi, and Dotnetfx.exe located on a network share, separate from Setup.exe.

[Bootstrap]
Msi=\\myNetworkShare\myProjectFolder\mySetup.msi
'LanguageDirectory=
ProductName=My Application
'DialogText=
CaptionText=My Application
ErrorCaptionText= My Application Error
FxInstallerPath=\\myNetworkShare\myProjectFolder\

The following example settings.ini file points to the Windows Installer package for a .NET Framework application, mySetup.msi, and Dotnetfx.exe located on the same computer as Setup.exe, but in different folders.

[Bootstrap]
Msi=c:\myInstallerFiles\mySetup.msi
'LanguageDirectory=
ProductName= My Application
'DialogText=
CaptionText=My Application
ErrorCaptionText= My Application Error
FxInstallerPath=c:\myExecutables\

The following example settings.ini file points to the Windows Installer package for a .NET Framework application, mySetup.msi, and Dotnetfx.exe located on the same CD as Setup.exe, but in different folders.

[Bootstrap]
Msi=myInstallerFiles/mySetup.msi
'LanguageDirectory=
ProductName= My Application
'DialogText=
CaptionText=My Application
ErrorCaptionText= My Application Error
FxInstallerPath=myExecutables/

Specify the language version

If you want Setup.exe to check for a build number and a language version of the .NET Framework, you must specify the culture name of the language to check for in the settings.ini file. If you do not specify a culture name, Setup.exe checks for the English language version by default.

The following table lists the available language versions and the culture name to specify in the settings.ini file.

Language	Culture name
Chinese (Simplified)	CHS
Chinese (Traditional)	CHT
French	fr
German	de
Italian	it
Japanese	ja
Korean	ko
Spanish	es

The following example settings.ini file instructs Setup.exe to check for the French language version of Dotnetfx.exe.

[Bootstrap]
Msi=mySetup.msi
LanguageDirectory=fr
ProductName= My Application 
'DialogText=
CaptionText=My Application
ErrorCaptionText= My Application Error
FxInstallerPath=c:\myProjectFolder\

Customize strings for Setup.exe dialog boxes

You can edit the following variables in the settings.ini file to customize strings displayed in Setup.exe dialog boxes.

ProductName

Specifies the name of the .NET Framework application Setup.exe will install. The product name that you specify is used to customize the text in the dialog box that appears after launching Setup.exe. For example, if you specify My Application as ProductName, the dialog box text reads, "To start My Application Setup, click OK. To quit without installing, click Cancel."

If you do not customize the CaptionText variable, ProductName is also used to customize the title bar for the dialog box that appears after launching Setup.exe. For example, if you specify My Application as the ProductName, the title bar displays "My Application Setup." If you customize both ProductName and CaptionText, CaptionText is used to customize the title bar text in this dialog box.

ProductName is also used to customize the title bar text in the success dialog box displayed when the application is finished installing. If you do not customize ProductName, the default text "Application Setup" is displayed.

DialogText

Specifies the custom text to display in the dialog box that is displayed after launching Setup.exe. If you do not customize DialogText, the default text "To start Application Setup, click OK. To quit without installing, click Cancel" is displayed.

CaptionText

Specifies the custom text to display in the title bar of the dialog box that appears after launching Setup.exe. If you do not customize CaptionText, ProductName is displayed. If ProductName is not customized, the default text "Application Setup" is displayed.

ErrorCaptionText

Specifies the custom text to display in the title bar of Setup.exe error dialog boxes. If you do not customize ErrorCaptionText, the default text "Application Setup Error" is displayed.

第四章 ARP地址解析协议

Ericdyx — Thu, 27 Jan 2005 02:44:00 GMT

    地址解析为两种不同的地址形式提供映射：32bit的IP地址和数据链路层使用的任何类型的地址。在A R P背后有一个基本概念，那就是网络接口有一个硬件地址（一个48 bit的值，标识不同的以太网或令牌环网络接口）。在硬件层次上进行的数据帧交换必须有正确的接口地址。但是，T C P / I P有自己的地址： 32 bit的I P地址。知道主机的I P地址并不能让内核发送一帧数据给主机。内核（如以太网驱动程序）必须知道目的端的硬件地址才能发送数据。A R P的功能是在32 bit的I P地址和采用不同网络技术的硬件地址之间提供动态映射。点对点链路不使用A R P，必须告知内核链路每一端的I P地址。

4.3   ARP高速缓存
    A R P高效运行的关键是由于每个主机上都有一个A R P高速缓存。这个高速缓存存放了最近I n t e r n e t地址到硬件地址之间的映射记录。命令行环境下键入arp －a显示高速缓存中的所有内容。

4.4   ARP的分组格式

第三章 IP：网际协议

Ericdyx — Wed, 26 Jan 2005 17:31:00 GMT

3.1    IP首部
IP提供不可靠、无连接的数据报传送服务。
不可靠（ u n r e l i a b l e）的意思是它不能保证I P数据报能成功地到达目的地。I P仅提供最好的传输服务。如果发生某种错误时，如某个路由器暂时用完了缓冲区， I P有一个简单的错误处理算法：丢弃该数据报，然后发送I C M P消息报给信源端。任何要求的可靠性必须由上层来
提供（如T C P）。
无连接（ c o n n e c t i o n l e s s）这个术语的意思是I P并不维护任何关于后续数据报的状态信息。每个数据报的处理是相互独立的。这也说明， I P数据报可以不按发送顺序接收。如果一信源向相同的信宿发送两个连续的数据报（先是A，然后是B），每个数据报都是独立地进行路由选择，可能选择不同的路线，因此B可能在A到达之前先到达。

1）服务类型（TO S）字段（8bit）包括一个3 bit的优先权子字段（现在已被忽略），4 bit的TO S子字段和1 bit未用位但必须置0。4 bit的TO S分别代表：最小时延、最大吞吐量、最高可靠性和最小费用。
2）总长度字段是指整个I P数据报的长度，以字节为单位。利用首部长度字段和总长度字段，可以知道I P数据报中数据内容的起始位置和长度。
3）标识字段唯一地标识主机发送的每一份数据报。通常每发送一份报文它的值就会加1。
4）T T L（t i m e - t o - l i v e）生存时间字段设置了数据报可以经过的最多路由器数。它指定了数据报的生存时间。T T L的初始值由源主机设置（通常为3 2或6 4），一旦经过一个处理它的路由器，它的值就减去1。当该字段的值为0时，数据报就被丢弃，并发送I C M P报文通知源主机。

3.3      IP路由选择
    如果目的主机与源主机直接相连（如点对点链路）或都在一个共享网络上（以太网或令牌环网），那么IP数据报就直接送到目的主机上，否则，数据报发往路由器上，由路由器来转发。
    在一般的体制中，I P可以从T C P、U D P、I C M P和I G M P接收数据报（即在本地生成的数据报）并进行发送，或者从一个网络接口接收数据报（待转发的数据报）并进行发送。I P层在内存中有一个路由表。当收到一份数据报并进行发送时，它都要对该表搜索一次。当数据报来自某个网络接口时， I P首先检查目的I P地址是否为本机的I P地址之一或者I P广播地址。如果确实是这样，数据报就被送到由I P首部协议字段所指定的协议模块进行处理。如果数据报的目的不是这些地址，那么（ 1）如果I P层被设置为路由器的功能，那么就对数据报进行转发（也就是说，像下面对待发出的数据报一样处理）；否则（ 2）数据报被丢弃。
    路由表中的每一项都包含下面这些信息：目的IP地址（可以是完整的主机地址，也可以是一个网络地址，有该表目的标志字段指定）；   下一站路由器的IP地址；   标志（指明目的IP地址是网络地址还是主机地址）。
    所有的IP路由选择只为数据报传输提供下一站路由器的IP地址，IP路由选择主要完成以下功能：1) 搜索路由表，寻找能与目的I P地址完全匹配的表目（网络号和主机号都要匹配）；2) 搜索路由表，寻找能与目的网络号相匹配的表目。如果找到，则把报文发送给该表目指定的下一站路由器或直接连接的网络接口；3）搜索路由表，寻找标为“默认（ d e f a u l t）”的表目。如果找到，则把报文发送给该表目指定的下一站路由器。

3.4     子网寻址
    A类和B类地址为主机号分配了太多的空间，由当地的系统管理员决定是否进行分配，是否建立子网，例如这里有一个B类网络地址（1 4 0 . 2 5 2），在剩下的16 bit中，8 bit用于子网号， 8 bit用于主机号，格式如图3 - 5所示。这样就允许有2 5 4个子网，每个子网可以有2 5 4台主机。
    与3 0个C类地址相比，用一个包含3 0个子网的B类地址的好处是，它可以缩小I n t e r n e t路由
的规模。B类地址1 4 0 . 2 5 2被划分为若干子网的事实对于所有子网以外的I n t e r n e t路由器都是透明的。为了到达I P地址开始部分为1 4 0 . 2 5 2的主机，外部路由器只需要知道通往I P地址1 4 0 . 2 5 2 . 1 0 4 . 1的路径。这就是说，对于网络1 4 0 . 2 5 2只需一个路由表目，而如果采用3 0个C类地址，则需要3 0个路由表目。因此，子网划分缩减了路由表的规模。

3.5    子网掩码
    除了I P地址以外，主机还需要知道有多少比特用于子网号及多少比特用于主机号。这是在引导过程中通过子网掩码来确定的。这个掩码是一个32 bit的值，其中值为1的比特留给网络号和子网号，为0的比特留给主机号。

    假设我们的主机地址是1 4 0 . 2 5 2 . 1 . 1（一个B类地址），而子网掩码为2 5 5 . 2 5 5 . 2 5 5 . 0（其中8
b i t为子网号， 8 bit为主机号）。
• 如果目的I P地址是1 4 0 . 2 5 2 . 4 . 5，那么我们就知道B类网络号是相同的（ 1 4 0 . 2 5 2），但是子网号是不同的（1和4）。用子网掩码在两个I P地址之间的比较如图3 - 8所示。
• 如果目的I P地址是1 4 0 . 2 5 2 . 1 . 2 2，那么B类网络号还是一样的（ 1 4 0 . 2 5 2），而且子网号也是一样的（ 1），但是主机号是不同的。
• 如果目的I P地址是1 9 2 . 4 3 . 2 3 5 . 6（一个C类地址），那么网络号是不同的，因而进一步的比较就不用再进行了。

3.9    netstat命令
    提供系统上的接口信息。netstat -n打印出每个接口的源地址、目的地址还有状态。在第九章中会用netstat命令检查路由表。

第二章链路层

Ericdyx — Wed, 26 Jan 2005 16:44:00 GMT

链路层主要有三个目的：（1）为I P模块发送和接收I P数据报；（ 2）为A R P模块发送A R P请求和接收A R P应答；（3）为R A R P发送R A R P请求和接收R A R P应答。
I E E E（电子电气工程师协会） 8 0 2委员会公布了一个稍有不同的标准集，其中
8 0 2 . 3针对整个C S M A / C D网络，8 0 2 . 4针对令牌总线网络， 8 0 2 . 5针对令牌环网络。

1－2 分层

Ericdyx — Wed, 26 Jan 2005 11:52:00 GMT

TCP/IP通常被认为是一个四层协议系统
1）链路层。通常包括操作系统中的设备驱动程序喝计算机中对应的网络接口卡，一起处理与电缆的物理接口细节。
2）网络层。处理分组在网络中的活动，如分组的选路，网络层协议包括IP协议（网际协议），ICMP协议（internet互联网控制报文协议），IGMP协议（Internet组管理协议）。
3）运输层。两台主机桑的应用程序提供端对端的通信。TCP（传输控制协议）和UDP（用户数据报协议）。
4）应用层。负责处理特定的应用程序细节。如telnet远程登陆、Ftp文件传输协议、SMTP简单有机爱你传送协议、SNMP简单网络管理协议。

IP路由器（IP Router）用于位不同类型的物理网络提供连接，是在网络层的机件。

TCP/IP协议族中，网络层IP提供不可靠的服务，只是尽可能快地把分组从源结点送到目的结点，但并不提供任何可靠性保证；但TCP在IP层上提供了一个可靠地运输层，采用超时重传、发送和接受端到端地确认分组等机制。

Web项目的包括数据库的打包方法

Ericdyx — Wed, 26 Jan 2005 11:13:00 GMT

新建一项目：visual C#项目 , 选中类库模板 ,如命名为myInstall,在此项目下面添加一新项：选择安装程序类（Installer1.cs），在此我采用默认名。此安装类库将实现数据库的安装及其他一些文件的安装。

在installer1.cs里我们将重写安装类的方法：见参考1

Install Commit Rollback Uninstall

在install方法中来实现自定义的安装：

首先我们要得到一些用户变量（用户在安装时所做的操作，如数据库管理帐号等）

对于这些变量我们可以通过上下文环境来得到this.Context.Parameters["DataBaseName"];（在安装项目里将会进行设置）

在此我们一共得到五个变量。安装数据库的服务器，指定的数据库名，数据库管理员帐号，密码。以及数据库备份文件。

我们来看一下具体的代码，很简单：）

private void InstallDateBase()

{

// System.Reflection.Assembly Asm;

// Asm= System.Reflection.Assembly.GetExecutingAssembly();

// System.IO.FileInfo FileInfo = new System.IO.FileInfo(Asm.Location);

// 也可以得到当前安装的目录，如wwwroot目录下myweb\你的安装工程名.dll

//下面是进行数据库连线及命令

SqlConnection sqlConnection1 = new SqlConnection();

string DBName = this.Context.Parameters["DataBaseName"];

string DBServer = this.Context.Parameters["ServerName"];

string DBUser = this.Context.Parameters["InStallAdmin"];

string DBKey = this.Context.Parameters["InstallPassword"];

string dir=this.Context.Parameters["dir"];

StreamWriter ss=new StreamWriter(@"c:\aaa.txt",true);

ss.WriteLine("data source=" + DBName + ";database=master;uid=" + DBUser +";password=" + DBKey);

ss.Close();

string CONSTR = "data source=" + DBServer +";database=master;uid=" + DBUser +";password=" + DBKey;

sqlConnection1.ConnectionString = CONSTR;

//数据库将安装到system32下

string CurDir = Directory.GetCurrentDirectory() + @"\TestData\database";

if(!Directory.Exists(CurDir))//不存在就新建一个

{

Directory.CreateDirectory(CurDir);

}

string MSQL = "RESTORE DATABASE " + DBName +

" FROM DISK = '" + dir + @"\database\myDataBase.bak' " +

" WITH MOVE 'myDate_dat(这里是备份的数据库文件中的mdf后缀的那个名字)' TO '" + CurDir + @"\" + DBName + ".mdf', " +

" MOVE 'myDate_log（这里是备份的数据库文件中的ldf后缀的那个名字）' TO '" + CurDir + @"\" + DBName + ".ldf' " ;

SqlCommand cmd = new SqlCommand(MSQL,sqlConnection1);

cmd.Connection.Open();

cmd.ExecuteNonQuery();

cmd.Connection.Close();

if(sqlConnection1.State == ConnectionState.Open)

{

sqlConnection1.Close();

}

public override void Install(System.Collections.IDictionary stateSaver)

{

try

{

base.Install(stateSaver);

this.InstallDateBase();//调用上面的方法

}

catch
{

throw;

}

public override void Uninstall(System.Collections.IDictionary stateSaver)

{

base.Uninstall(stateSaver);

}

public override void Commit(System.Collections.IDictionary stateSaver)

{

base.Commit(stateSaver);

}

public override void Rollback(System.Collections.IDictionary stateSaver)

{

base.Rollback(stateSaver);

}

2)创建部署项目

在“文件”菜单上，选择“添加项目”->“新建项目”。
在“添加项目”对话框中，选择“项目类型”窗格中的“安装和部署项目”，然后选择“模板”窗格中的“WEB安装项目”。在“名称”框中，键入 websetup2。(我是默认的，没改。这些语句懒得打，抄来的，：))
在“属性”窗口中，选择 ProductName 属性并键入 mywebTest。
在文件系统编辑器中，选择“应用程序文件夹”。在“操作”菜单上，选择“添加”->“项目输出”。
在“添加项目输出组”对话框中，为“myInstall”项目选择主输出。

在前面的操作与一般打包一样，将你的WEB工程里的一些文件添加进来。及一些其他打包设置（产品名等），我就不多说了。

我们这里仅仅要加入一个数据库备份文件（myDataBase.bak）

首先打开websetup2的文件系统窗口，选中web应用程序文件夹，在此文件夹下面添加一个文件夹我们用来存放数据库，如database,再将mydatabase.bak文件复制到此目录下面。

现在我们要添加一个安装时的窗口，用来让用户输入数据库帐号等。我们只要打开用户界面窗口就可以进行操作了。

创建自定义安装对话框

在解决方案资源管理器中选择“websetup2”项目。在“视图”菜单上指向“编辑器”，然后选择“用户界面”。
在用户界面编辑器中，选择“安装”下的“启动”节点。在“操作”菜单上，选择“添加对话框”。
在“添加对话框”对话框中选择“文本框 (A)”对话框。
在“操作”菜单上选择“上移”。重复此步骤，直到“文本框 (A)”对话框位于“安装文件夹”节点之上。
在“属性”窗口中，选择 BannerText 属性并键入自定义数据库安装
选择 BodyText 属性并键入自定义数据库安装
选择 Edit1Label 属性并键入服务器名:。
选择 Edit1Property 属性并键入 SERVERNAME。
选择 Edit2Label 属性并键入创建数据库名:。
选择 Edit2Property 属性并键入 DATABASENAME。
选择 Edit3Label 属性并键入数据库管理员帐号:。
选择 Edit3Property 属性并键入 INSTALLADMIN。
选择 Edit4Label 属性并键入管理员密码:。INSTALLPASSWORD
选择 Edit4Property 属性并键入 SERVERNAME。

创建自定义操作

在解决方案资源管理器中选择“websetup2”项目。在“视图”菜单上指向“编辑器”，然后选择“自定义操作”。
在自定义操作编辑器中选择“安装”节点。在“操作”菜单上，选择“添加自定义操作”。
在“选择项目中的项”对话框中，双击“应用程序文件夹”。
选择“添加输出”；
选择“主输出来自 DBCustomAction（活动）”项。
在“属性”窗口中，选择 CustomActionData 属性并键入 /InstallPassword=[INSTALLPASSWORD] /InStallAdmin=[INSTALLADMIN] /ServerName=[SERVERNAME] /DataBaseName=[DATABASENAME] /dir="[TARGETDIR]\"
注意各个用户数据之间是有空格的。如/aaa=[fdfad] /bfff=[fdafdfd]

在“生成”菜单上选择“生成websetup2”。
上面的customActionData将会在安装类中引用的。注意各个之间一定要有空格。

/dir=[“TARGETDIR”],取的是最终安装的WEB目录

到此一个项目打包就此结束了

ADO.net性能改进

Ericdyx — Tue, 25 Jan 2005 05:58:00 GMT

Improving .NET Application Performance and Scalability

J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
Microsoft Corporation

May 2004

Related Links

Home Page for Improving .NET Application Performance and Scalability

Chapter 5, Improving Managed Code Performance

Chapter 14, Improving SQL Server Performance

Checklist: ADO.NET Performance

Send feedback to Scale@microsoft.com

patterns & practices Library

Summary: This chapter provides proven strategies to help you design and develop scalable data access solutions. Topics covered include different techniques to pass data across application layers, managing the database connection pool, optimizing stored procedure calls, reducing dataset serialization cost, techniques for paging through large result sets, managing transactions, handling BLOBS, and much more.

Objectives
Overview
How to Use This Chapter
Architecture
Performance and Scalability Issues
Design Considerations
Implementation Considerations
.NET Framework Data Providers
Connections
Commands
Stored Procedures
Parameters
DataSet vs. DataReader
DataReader
DataSet
XML and DataSet Objects
Typed DataSets
Types
Exception Management
Transactions
Binary Large Objects
Paging Records
Analyzing Performance and Scalability of Data Access
Summary
Additional Resources

Objectives

Optimize your data access design.
Choose between DataSets and DataReaders.
Run efficient database commands.
Pass data between layers efficiently.
Perform efficient transactions.
Optimize connection management.
Evaluate the cost of paging through records.
Evaluate criteria for analyzing data access performance.
Apply performance considerations to binary large object (BLOB) manipulation.

Overview

Well-designed data access code and data processing commands are essential elements for application performance and scalability. Typically, the database is a focal point for application load because the majority of application requests require data that comes from a database.

This chapter provides proven strategies for designing and implementing data access code for performance and scalability.

How to Use This Chapter

Use this chapter to improve the implementation of your data access code for performance and scalability. To get the most out of this chapter, consider the following:

Jump to topics or read beginning to end. The main headings in this chapter help you to quickly identify and then locate the topic that interests you. Alternatively, you can read the chapter beginning to end to gain a thorough appreciation of the issues that affect ADO.NET performance.
Use the checklist. Use "Checklist: ADO.NET Performance" in the "Checklists" section of this guide to quickly view and evaluate the guidelines presented in this chapter.
Use the "Architecture" section of this chapter to understand how ADO.NET works. By understanding the architecture, you can make better design and implementation choices. Understand core ADO.NET components, such as data provider objects and the DataSet object.
Use the "Design Considerations" section of this chapter to understand the high-level decisions that will affect implementation choices for ADO.NET code.
Read Chapter 13, "Code Review: .NET Application Performance" See the "Data Access" section for specific guidance.
Measure your application performance. Read the "ADO.NET/Data Access" and ".NET Framework Technologies" sections of Chapter 15, "Measuring .NET Application Performance" to learn about the key metrics that you can use to measure application performance. You have to measure application performance so that you can identify and resolve performance issues.
Test your application performance. Read Chapter 16, "Testing .NET Application Performance" to learn how to apply performance testing to your application. You have to apply a coherent testing process and analyze the results.
Tune your application performance. Read the "ADO.NET Tuning" section of Chapter 17, "Tuning .NET Application Performance" to learn how to resolve performance issues that you identify through the use of tuning metrics.
Tune SQL Server. Read Chapter 14, "Improving SQL Server Performance" to ensure that your Microsoft® SQL Server™ database is appropriately configured.

Architecture

ADO.NET relies on data providers to provide access to the underlying data source. Each data provider exposes a set of objects that you use to manage connections, retrieve data, and update data. The core objects are the following:

Connection
Command
DataReader
DataAdapter

In addition, ADO.NET provides the DataSet object, which provides a disconnected cache of data. The DataSet object does not require a specific type of data source and is not tied to the underlying data source that the data was obtained from.

The basic ADO.NET architecture is shown in Figure 12.1.

Figure 12.1: ADO.NET architecture

The following list outlines the purpose of each of the main ADO.NET objects:

Connection. This object represents a connection to a database.
Command. This object represents an SQL statement that is run while connected to a data source. This object can be a stored procedure or a direct SQL statement.
DataReader. This object retrieves a read-only, forward-only stream of data from a database. The DataReader object is designed for connected scenarios and offers better performance than reading data into a DataSet object at the expense of functionality. For more information about how to use DataReader objects and DataSet objects, see "DataSet vs. DataReader" later in this chapter.
DataAdapter. This object channels data to and from a DataSet object and the underlying data source. The DataAdapter object also provides enhanced batch update features that were previously associated with the ADO Recordset object.
DataSet. The DataSet object represents a disconnected, cached set of data. The DataSet is independent of the provider and is not tied to the underlying data source that might have been used to populate it. DataSet can easily be passed from component to component through the various layers of an application, and it can be serialized as XML.
You should be aware of the way a DataSet is internally constructed because the DataSet contains a potentially large number of internal objects. This means that a large number of memory allocations are required to construct a typical DataSet.
A DataSet consists of one or more DataTable objects together with DataRelation objects that maintain table relationship information. Each DataTable contains DataRow objects and DataColumn objects. Constraint objects are used to represent a constraint that can be enforced on one or more DataColumn objects.
Note You can also use typed datasets that derive from the basic DataSet class. Typed datasets provide benefits at build time and at run time. For more information, see "Typed DataSets" later in this chapter.
DataView. Although the DataView object is not shown in Figure 12.1, you can use a DataView to sort and filter data in a DataTable. This capability is often used for data binding.

Abstracting Data Access

ADO.NET is designed around a set of generic interfaces that abstract the underlying data processing functionality. You can use these interfaces directly to abstract your data access layer so that you can minimize the impact of changing the type of data source that you use. Abstracting data access is extremely helpful when you are designing systems where your customer chooses the database server.

The core interfaces provided by ADO.NET are found in the System.Data namespace:

IDbConnection. This is an interface for managing database connections.
IDbCommand. This is an interface for running SQL commands.
IDbTransaction. This is an interface for managing transactions.
IDataReader. This is an interface for reading data returned by a command.
IDataAdapter. This is an interface for channeling data to and from datasets.

The various provider objects, such as SqlConnection and OleDbConnection, implement these generic ADO.NET data access interfaces. If you decide to program against the generic interfaces, be aware of the following issues:

There is some small cost associated with a virtual call through an interface.
Certain expanded functionality is lost when you use the generic interfaces. For example, the ExecuteXmlReader method is implemented by the SqlCommand object but not by the IDbCommand interface.
There is no generic base exception type, so you must catch provider-specific exception types, such as SqlException, OleDbException, or OdbcException.
When you use the generic interfaces, you cannot take advantage of database-specific types that are defined for the managed providers; for example, you cannot take advantage of SqlDbType in SqlClient and Oracle-specific types in the Oracle provider. Using specific database types is helpful for type checking and parameter binding.

More Information

For more information about how to use the generic interfaces to abstract your data access, see the following resources:

Knowledge Base article 313304, "HOW TO: Use Base Classes to Reduce Code Forking with Managed Providers in Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;313304. This article includes sample code.
Use MSDN® to look at the interfaces that are described earlier in this section to identify the providers that implement each of the interfaces.

Performance and Scalability Issues

The following is a list of the main issues that can adversely affect the performance and scalability of data access in your application.

Inefficient queries. Queries that process and then return more columns or rows than necessary waste processing cycles that could best be used for servicing other requests. Queries that do not take advantage of indexes may also cause poor performance.
Retrieving too much data. Too much data in your results is usually the result of inefficient queries. The SELECT * query often causes this problem. You do not usually need to return all the columns in a row. Also, analyze the WHERE clause in your queries to ensure that you are not returning too many rows. Try to make the WHERE clause as specific as possible to ensure that the least number of rows are returned.
Inefficient or missing indexes. Query efficiency decreases when indexes are missing because a full table scan must be performed. Also, as your data grows, tables may become fragmented. Failure to periodically rebuild indexes may also result in poor query performance.
Unnecessary round trips. Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance. Keep round trips to an absolute minimum.
Too many open connections. Connections are an expensive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability. To ensure the efficient use of connection pooling, avoid keeping connections open and avoid varying connection strings.
Failure to release resources. Failing to release resources can prevent them from being reused efficiently. If you fail to close connections before the connections fall out of scope, they are not reclaimed until garbage collection occurs for the connection. Failing to release resources can cause serious resource pressure and lead to shortages and timeouts.
Transaction misuse. If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transactions may cause resource pressure. Transactions are necessary to ensure the integrity of your data, but you need to ensure that you use the appropriate type of transaction for the shortest duration possible and only where necessary.
Overnormalized tables. Overnormalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your application, especially as the number of users and requests increases.

Subsequent sections in this chapter provide strategies and technical information to prevent or resolve each of these issues.

Design Considerations

To help ensure that data access in your application is optimized for performance, there are several issues that you must consider and a number of decisions that you must make at design time:

Design your data access layer based on how the data is used.
Cache data to avoid unnecessary work.
Connect by using service accounts.
Acquire late, release early.
Close disposable resources.
Reduce round trips.
Return only the data you need.
Use Windows authentication.
Choose the appropriate transaction type.
Use stored procedures.
Prioritize performance, maintainability, and productivity when you choose how to pass data across layers.
Consider how to handle exceptions.
Use appropriate normalization.

Design Your Data Access Layer Based on How the Data Is Used

If you choose to access tables directly from your application without an intermediate data access layer, you may improve the performance of your application at the expense of maintainability. The data access logic layer provides a level of abstraction from the underlying data store. A well-designed data access layer exposes data and functionality based on how the data is used and abstracts the underlying data store complexity.

Do not arbitrarily map objects to tables and columns, and avoid deep object hierarchies. For example, if you want to display a subset of data, and your design retrieves an entire object graph instead of the necessary portions, there is unnecessary object creation overhead. Evaluate the data you need and how you want to use the data against your underlying data store.

Cache Data to Avoid Unnecessary Work

Caching data can substantially reduce the load on your database server. By caching data, you avoid the overhead of connecting to your database, searching, processing, and transmitting data from your database server. By caching data, you directly improve performance and scalability in your application.

When you define your caching strategy, consider the following:

Is the data used application-wide and shared by all users, or is the data specific to each user? Data that is used across the application, such as a list of products, is a better caching candidate than data that is specific to each user.
How frequently do you need to update the cache? Even though the source data may change frequently, your application may not need to update the cache as often. If your data changes too frequently, it may not be a good caching candidate. You need to evaluate the expense of updating the cache compared to the cost of fetching the data as needed.
Where should you cache data? You can cache data throughout the application layers. By caching data as close as possible to the consumer of the data, you can reduce the impact of network latency.
What form of the data should you cache? The best form of data to cache is usually determined by the form that your clients require the data to be in. Try to reduce the number of times that you need to transform data.
How do you expire items in the cache? Consider the mechanism that you will use to expire old items in the cache and the best expiration time for your application.

Connect by Using Service Accounts

There are several ways to authenticate and to open a connection to the database. You can use SQL authentication that has an identity specified in the connection string. Or, you can use Windows authentication by using the process identity, by using a specific service identity, or by impersonating the original caller's identity.

From a security perspective, you should use Windows authentication. From a performance perspective, you should use a fixed service account and avoid impersonation. The fixed service account is typically the process account of the application. By using a fixed service account and a consistent connection string, you help ensure that database connections are pooled efficiently. You also help ensure that the database connections are shared by multiple clients. Using a fixed service account and a consistent connection string is a major factor in helping application scalability.

More Information

For more information, see "The Trusted Subsystem Model" in the "Authentication and Authorization" chapter of "Building Secure ASP.NET Applications: Authentication, Authorization and Secure Communication" on MSDN at http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetch03.asp. This chapter explains how to use service accounts or process identity to connect to a database. You can also use the chapter to learn about the advantages and disadvantages of Windows and SQL authentication.

Acquire Late, Release Early

Your application should share expensive resources efficiently by acquiring the resources late, and then releasing them as early as possible. To do so:

Open database connections right when you need them. Close the database connections as soon as you are finished. Do not open them early, and do not hold them open across calls.
Acquire locks late, and release them early.

Close Disposable Resources

Usually, disposable resources are represented by objects that provide a Dispose method or a Close method. Make sure that you call one of these methods as soon as you are finished with the resource. For more information about closing Connection objects and DataReader objects, see "Connections" later in this chapter.

Reduce Round Trips

Network round trips are expensive and affect performance. Minimize round trips by using the following techniques:

If possible, batch SQL statements together. Failure to batch work creates additional and often unnecessary trips to the database. You can batch text SQL statements by separating them with a semicolon or by using a stored procedure. If you need to read multiple result sets, use the NextResult method of the DataReader object to access subsequent result sets.
Use connection pooling to help avoid extra round trips. By reusing connections from a connection pool, you avoid the round trips that are associated with connection establishment and authentication. For more information, see "Connections" later in this chapter.
Do not return results if you do not need them. If you only need to retrieve a single value, use the ExecuteScalar method to avoid the operations that are required to create a result set. You can also use the ExecuteNonQuery method when you perform data definition language (DDL) operations such as the create table operation. This also avoids the expense of creating a result set.
Use caching to bring nearly static data close to the consumer instead of performing round trips for each request.

Implicit Round Trips

Be aware that certain operations can cause implicit round trips. Typically, any operation that extracts metadata from the database causes an implicit round trip. For example, avoid calling DeriveParameters if you know the parameter information in advance. It is more efficient to fill the parameters collection by setting the information explicitly. The following code sample illustrates a call that causes an implicit round trip.

// This causes an implicit round trip to the database
SqlCommandBuilder.DeriveParameters(cmd);

Return Only the Data You Need

Evaluate the data that your application actually requires. Minimize the data that is sent over the network to minimize bandwidth consumption. The following approaches help reduce data over the network:

Return only the columns and rows that you need.
Cache data where possible.
Provide data paging for large results. For more information about paging, see "Paging Records" later in this chapter.

Use Windows Authentication

From a security perspective, you should use Windows authentication to connect to Microsoft SQL Server. There are several advantages to using Windows authentication. For example, credentials are not passed over the network, database connection strings do not contain credentials, and you can apply standard Windows security policies to accounts. For example, you can enforce use of strong passwords and apply password expiration periods.

From a performance perspective, SQL authentication is slightly faster than Windows authentication, but connection pooling helps minimize the difference. You also need to help protect the credentials in the connection string and in transit between your application and the database. Helping to protect the credentials adds to the overhead and minimizes the performance difference.

Note Generally, local accounts are faster than domain accounts when you use Windows authentication. However, the performance saving needs to be balanced with the administration benefits of using domain accounts.

Choose the Appropriate Transaction Type

Proper transaction management minimizes locking and contention, and provides data integrity. The three transaction types and their usage considerations include the following:

Native database support. Native database support for transactions permits you to control the transaction from a stored procedure. In SQL Server, use BEGIN TRAN, COMMIT TRAN, and ROLLBACK to control the transaction's outcome. This type of transaction is limited to a single call from your code to the database, although the SQL query or stored procedure can make use of multiple stored procedures.
ADO.NET transactions. Manual transactions in ADO.NET enable you to span a transaction across multiple calls to a single data store. Both the SQL Server .NET Data Provider and the OLE DB .NET Data Provider implement the IDbTransaction interface and expose BeginTransaction on their respective connection object. This permits you to begin a transaction and to run multiple SQL calls using that connection instance and control the transaction outcome from your data access code.
Enterprise Services distributed transactions. Use declarative, distributed transactions when you need transactions to span multiple data stores or resource managers or where you need to flow transaction context between components. Also consider Enterprise Services transaction support for compensating transactions that permit you to enlist nontransactional resources in a transaction. For example, you can use the Compensating Resource Manager to combine a file system update and a database update into a single atomic transaction.
Enterprise Services distributed transactions use the services of the Microsoft Distributed Transaction Coordinator (DTC). The DTC introduces additional performance overhead. The DTC requires several round trips to the server and performs complex interactions to complete a transaction.

Use Stored Procedures

Avoid embedded SQL statements. Generally, well-designed stored procedures outperform embedded SQL statements. However, performance is not the only consideration. When you choose whether to store your SQL commands on the server by using stored procedures or to embed commands in your application by using embedded SQL statements, consider the following issues:

Logic separation. When you design your data access strategy, separate business logic from data manipulation logic for performance, maintainability, and flexibility benefits. Validate business rules before you send the data to the database to help reduce network round trips. Separate your business logic from data manipulation logic to isolate the impact of database changes or business rule changes. Use stored procedures to clarify the separation by moving the data manipulation logic away from the business logic so that the two do not become intertwined.
SQL optimizations. Some databases provide optimizations to stored procedures that do not apply to dynamic SQL. For example, Microsoft SQL Server™ versions prior to SQL Server 2000 kept a cached execution plan for stored procedures. The cached execution plan for stored procedures reduced the need to compile each stored procedure request. SQL Server 2000 is optimized to cache query plans for both stored procedure and for dynamic SQL query plans.
Tuning/deployment. Stored procedure code is stored in the database and permits database administrators to review data access code. Database administrators can tune both the stored procedures and the database, independent of the deployed application. The application does not always need to be redeployed when stored procedures change.
Embedded SQL is deployed as part of the application code and requires database administrators to profile the application to identify the SQL actually used. Profiling the application complicates tuning, because the application must be redeployed if any changes are made.
Network traffic sent to the server. Source code for stored procedures is stored on the server. Only the name and parameters are sent across the network to the server. Conversely, when you use embedded SQL, the full source of the commands must be transmitted each time the commands are run. When you use stored procedures, you can reduce the amount of data that is sent to the server when large SQL operations are frequently run.
Simplified batching of commands. Stored procedures make it easy to batch work and provide simpler maintenance.
Data security and integrity. With stored procedures, administrators can secure tables against direct access or manipulation, and they can only permit the execution of selected stored procedures. Both users and applications are granted access to the stored procedures that enforce data integrity rules. Embedded SQL usually requires advanced permissions on tables. Using advanced permissions on tables is a more complex security model to maintain.
SQL Injection. Avoid using dynamically generated SQL with user input. SQL injection occurs when input from a malicious user is used to perform unauthorized actions, such as retrieving too much data or destructively modifying data. Parameterized stored procedures and parameterized SQL statements can help reduce the likelihood of SQL injection. Parameter collections force parameters to be treated as literal values so that the parameters are not treated as executable code. You should also constrain all user input to reduce the likelihood that a malicious user could use SQL injection to perform unauthorized actions.

More Information

For more information about how to prevent SQL injection, see Chapter 14, "Building Secure Data Access," in Improving Web Application Security: Threats and Countermeasures on MSDN at http://msdn.microsoft.com/library/en-us/dnnetsec/html/THCMCh14.asp.

Prioritize Performance, Maintainability, and Productivity when You Choose How to Pass Data Across Layers

You should consider several factors when you choose an approach for passing data across layers:

Maintainability. Consider how hard it is to build and keep up with changes.
Productivity. Consider how hard it is to implement the solution.
Programmability. Consider how hard it is to code.
Performance. Consider how efficient it is for collections, browsing, and serializing.

This section summarizes the main approaches for passing data across application layers and the relative tradeoffs that exist:

DataSets. With this approach, you use a generic DataSet object. This approach offers great flexibility because of the extensive functionality of the DataSet. This includes serialization, XML support, ability to handle complex relationships, support for optimistic concurrency, and others. However, DataSet objects are expensive to create because of their internal object hierarchy, and clients must access them through collections.
The DataSet contains collections of many subobjects, such as the DataTable, DataRow, DataColumn, DataRelation and Constraint objects. Most of these objects are passed with the DataSet between the layers. This is a lot of objects and a lot of data to be passed between the layers. It also takes time to fill a DataSet, because there are many objects that need to be instantiated and populated. All of this affects performance. Generally, the DataSet is most useful for caching when you want to create an in-memory representation of your database, when you want to work with relations between tables, and when you want to perform sorting and filtering operations.
Typed DataSets. Instantiation and marshaling performance of the typed DataSet is roughly equivalent to the DataSet. The main performance advantage of the typed DataSet is that clients can access methods and properties directly, without having to use collections.
DataReaders. This approach offers the optimum performance when you need to render data as quickly as possible. You should close DataReader objects as soon as possible and make sure that client applications cannot affect the amount of time the DataReader and, hence, the database connection is held open.
The DataReader is very fast compared to a DataSet, but you should avoid passing DataReader objects between layers, because they require an open connection.
XML. This is a loosely coupled approach that natively supports serialization and collections of data. For example, an XML document can contain data for multiple business entities. It also supports a wide range of client types. Performance issues to consider include the fact that XML strings can require substantial parsing effort, and large and verbose strings can consume large amounts of memory.
Custom Classes. With this approach, you use private data members to maintain the object's state and provide public accessor methods. For simple types, you can use structures instead of classes, which means you avoid having to implement your own serialization. The main performance benefit of custom classes is that they enable you to create your own optimized serialization. You should avoid complex object hierarchies and optimize your class design to minimize memory consumption and reduce the amount of data that needs to be serialized when the object is passed between layers.

More Information

For more information about how to pass data across the layers, see "Designing Data Tier Components and Passing Data Through Tiers" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp.

Consider How to Handle Exceptions

In data access code in particular, you can use try/finally blocks to ensure that connections and other resources are closed, regardless of whether exceptions are generated. However, be aware of the following considerations:

Exceptions are expensive. Do not catch exceptions and then throw them again if your data access logic cannot add any value. A less costly approach is to permit the exception to propagate from the database to the caller. Similarly, do not wrap transaction attempts with try/catch blocks unless you plan to implement retry mechanisms.
If you want to completely abstract your caller from the data-specific details, you have to catch the exception, you have to log detailed information to a log store, and then you have to return an enumerated value from a list of application-specific error codes. The log store could be a file or the Windows event log.

Use Appropriate Normalization

Overnormalization of a database schema can affect performance and scalability. For example, if you program against a fully normalized database, you are often forced to use cross-table joins, subqueries, and data views as data sources. Obtaining the right degree of normalization involves tradeoffs.

On one hand, you want a normalized database to minimize data duplication, to ensure that data is logically organized, and to help maintain data integrity. On the other hand, it may be harder to program against fully normalized databases, and performance can suffer. Consider the following techniques:

Start with a normalized model. Start with a normalized model and then de-normalize later.
Reduce the cost of joins by repeating certain columns. Deep joins may result in the creation of temporary tables and table scans. To reduce the cost of joining across multiple tables, consider repeating certain columns.
Store precomputed results. Consider storing precomputed results, such as subtotals, instead of computing them dynamically for each request.

Implementation Considerations

When you move from application design to application development, consider the implementation details of your ADO.NET code. You can improve resource management by acquiring connections late, by releasing them early, and by using connection pooling.

When you run code on the server, prefer stored procedures. Stored procedures are optimized by the database and use provider-specific types when they pass parameters, to reduce processing. Choose the best transaction management mechanism, and then choose an appropriate isolation level. Keep transactions as short as possible, and avoid code that can lead to deadlocks. You can improve responsiveness by employing the correct paging strategy. Employing the correct paging strategy can also reduce server load.

By following best practice implementation guidelines, you can increase the performance of your ADO.NET code. The following sections summarize performance considerations for ADO.NET features and scenarios.

.NET Framework Data Providers

Microsoft .NET Framework Data Providers are divided into two categories: bridge providers and native providers. Bridge providers permit you to use data access libraries, such as OLE DB and Open Database Connectivity (ODBC). The bridge provider wraps the underlying data access library. Native providers, such as those for SQL Server and Oracle, typically offer performance improvements due, in part, to the fact that there is less abstraction. It is important to choose the correct data provider for your specific data source as described below:

Use System.Data.SqlClient for SQL Server 7.0 and later.
Use System.Data.OleDb for SQL Server 6.5 or OLE DB providers.
Use System.Data.ODBC for ODBC data sources.
Use System.Data.OracleClient for Oracle.
Use SQLXML managed classes for XML data and for SQL Server 2000.

Use System.Data.SqlClient for SQL Server 7.0 and Later

For SQL Server 7.0 or later, use the .NET Framework Data Provider for SQL Server, System.Data.SqlClient. It is optimized for accessing SQL Server and communicates directly by using the Tabular Data Stream (TDS) protocol. TDS is the native data transfer protocol of SQL Server.

The commonly used classes in System.Data.SqlClient are SqlConnection, SqlCommand, SqlDataAdapter, and SqlDataReader.

Use System.Data.OleDb for SQL Server 6.5 or OLE DB Providers

For SQL Server 6.5 or OLE DB data sources, use the .NET Framework Data Provider for OLE DB, System.Data.OleDb. For example, in SQL Server 6.5 or earlier, you would use the OLE DB Provider for SQL Server (SQLOLEDB) with the .NET Framework Data Provider for OLE DB. Using the OLE DB provider is less efficient than using the .NET Framework Data Provider for SQL Server, because it calls through the OLE DB layer by using COM interop when communicating with the database.

The commonly used classes in System.Data.OleDb are OleDbConnection, OleDbCommand, OleDbDataAdapter, and OleDbDataReader.

Note The .NET Framework Data Provider for OLE DB does not support the Microsoft OLE DB Provider for ODBC (MSDASQL). For ODBC data sources, use the .NET Framework Data Provider for ODBC instead.

Use System.Data.ODBC for ODBC Data Sources

For ODBC data sources, use the .NET Framework Data Provider for ODBC, System.Data.ODBC. This provider uses the native ODBC Driver Manager through COM interop.

If you are using .NET Framework 1.0, you must download the .NET Framework Data Provider for ODBC from the Microsoft .NET Framework Developer Center on MSDN at http://msdn.microsoft.com/netframework/downloads/updates/default.aspx. Note that the namespace is Microsoft.Data.Odbc. If you are using .NET Framework 1.1, it is included in the System.Data.Odbc namespace.

The commonly used classes in System.Data.Odbc are OdbcConnection, OdbcCommand, OdbcDataAdapter, and OdbcDataReader.

Use System.Data.OracleClient for Oracle

For Oracle data sources, use the .NET Framework Data Provider for Oracle, System.Data.OracleClient. This provider enables data access to Oracle data sources through Oracle client connectivity software. The data provider supports Oracle client software version 8.1.7 and later.

If you are using .NET Framework 1.0, you must download the .NET Framework Data provider for Oracle from the Microsoft .NET Framework Developer Center on MSDN at http://msdn.microsoft.com/netframework/downloads/updates/default.aspx. If you are using .NET Framework 1.1 or later, the .NET Framework Data provider for Oracle is included in the System.Data.OracleClient namespace in System.Data.OracleClient.dll.

The commonly used classes in System.Data.OracleClient are OracleConnection, OracleCommand, OracleDataAdapter, and OracleDataReader.

Use SQLXML Managed Classes for XML Data and SQL Server 2000

To manipulate data in a SQL Server database as XML, use SQLXML Managed Classes. You can download SQLXML 3.0 from the Data Access and Storage Developer Center on MSDN at http://msdn.microsoft.com/data/downloads/default.aspx.

More Information

For more information about .NET Framework Data Providers, see the following:

Knowledge Base article 313480, "INFO: Roadmap for .NET Data Providers," at http://support.microsoft.com/default.aspx?scid=kb;en-us;313480
MSDN article, "Using .NET Framework Data Provider for Oracle to Improve .NET Application Performance," at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/manprooracperf.asp
"Implementing a .NET Framework Data Provider" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingnetdataprovider.asp

Connections

Database connections are an expensive and limited resource. Your approach to connection management can significantly affect the overall performance and scalability of your application. Issues to consider include acquiring and releasing connections, pooling, and authentication. To improve database connection performance and scalability, apply the following strategies to your connection management policy:

Open and close the connection in the method.
Explicitly close connections.
When using DataReaders, specify CommandBehavior.CloseConnection.
Do not explicitly open a connection if you use Fill or Update for a single operation.
Avoid checking the State property of OleDbConnection.
Pool connections.

Open and Close the Connection in the Method

Acquire connections late and release them early. Opening connections before they are needed reduces the number of connections that are available and increases resource pressure. Close connections quickly to ensure that they can be reused as soon as possible. Do not hold on to connections. Holding on to connections reduces the connections that are available to other code and increases resource pressure. The general pattern is to open and close connections on a per-method basis.

Explicitly Close Connections

Explicitly call the Close or Dispose methods on SqlConnection objects as soon as you finish using them to release the resources that they use. Do not wait for the connection to fall out of scope. The connection is not returned to the pool until garbage collection occurs. This delays the reuse of the connection and negatively affects performance and scalability. The following are guidelines to consider. These guidelines are specific to SqlConnection because of the way it is implemented. These guidelines are not universal for all classes that have Close and Dispose functionality.

Using either the Close method or the Dispose method is sufficient. You do not have to call one method after the other. There is no benefit to calling one method after the other.
Dispose internally calls Close. In addition, Dispose clears the connection string.
If you do not call Dispose or Close, and if you do not use the using statement, you are reliant upon the finalization of the inner object to free the physical connection.
Use the using statement, instead of Dispose or Close, when you are working with a single type, and you are coding in Visual C#®. Dispose is automatically called for you when you use the using statement, even when an exception occurs.
If you do not use the using statement, close connections inside a finally block. Code in the finally block always runs, regardless of whether an exception occurs.
You do not have to set the SqlConnection reference to null or Nothing because there is no complex object graph. Setting object references to null or to Nothing is usually done to make a graph of objects unreachable.

Note Closing a connection automatically closes any active DataReader objects that are associated with the connection.

Closing Connections in Visual Basic .NET

The following Visual Basic® .NET code snippet shows how to explicitly close a connection as soon as the connection is no longer needed.

Try
  conn.Open()
  cmd.ExecuteNonQuery()
  customerCount = paramCustCount.Value
Catch ex As Exception
  ' … handle exception
Finally
  ' This is guaranteed to run regardless of whether an exception occurs
  ' in the Try block.
  If Not(conn is Nothing) Then
    conn.Close()
  End If
End Try

Closing Connections in C#

The following example shows how to close connections in C#.

public void DoSomeWork()
{
  SqlConnection conn = new SqlConnection(connectionString);
  …  try
  {
    conn.Open();
    // Do Work
  }
  catch (Exception e)
  {
    // Handle and log error
  }
  finally
  {
    if(null!=conn)
       conn.Close();
  }
}

Closing Connections with the Using Statement in C#

The using statement simplifies code for C# developers by automatically generating a try and finally block when the code is compiled. This ensures that the Dispose method is called even if an exception occurs. The following code fragment shows how to use the using statement.

using (SqlConnection conn = new SqlConnection(connString))
{
  conn.Open();
  . . .
} // Dispose is automatically called on the conn variable here

The C# compiler converts this code into the following equivalent code, which has a try and finally block to ensure that the Dispose method on the SqlConnection object is called, regardless of whether an exception occurs.

SqlConnection conn = new SqlConnection(connString);
try
{
  conn.Open();
}
finally
{
  conn.Dispose();
}

One limitation of the using statement is that you can only put a single type in the parentheses. If you want to ensure that Dispose is called on additional resources, you must nest the using statements as shown in the following example.

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = new SqlCommand("CustomerRead");

  conn.Open();
  using (SqlDataReader dr = cmd.ExecuteReader())
  {
    while (dr.Read())
      Console.WriteLine(dr.GetString(0));
  }
}

Note Using a nested using statement on the DataReader object is useful only if you need to perform further operations with the same connection after the inner using block. If you close the connection right away, this approach is of limited value because any active DataReader objects are closed automatically when the connection closes.

When Using DataReaders, Specify CommandBehavior.CloseConnection

When you create a DataReader object, specify the CommandBehavior.CloseConnection enumeration in your call to ExecuteReader. This ensures that when you close the DataReader, the connection is also closed. The following code fragment shows how to use the CommandBehavior enumeration.

// Create connection and command. Open connection.
. . .
SqlDataReader myReader= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
// read some data
. . .
myReader.Close(); // The connection and reader are closed.

The CommandBehavior.CloseConnection is especially helpful when you return a DataReader from a function, and you do not have control over the calling code. If the caller forgets to close the connection but closes the reader, both are closed when the DataReader is created by using CommandBehavior.CloseConnection. This is shown in the following code fragment.

public SqlDataReader CustomerRead(int CustomerID)
{
  //... create connection and command, open connection
  return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}

//... client code
SqlDataReader myReader = CustomerRead(10248);
//... read some data
myReader.Close(); // reader and connection are closed

Do Not Explicitly Open a Connection if You Use Fill or Update for a Single Operation

If you perform a single Fill or Update operation, do not open the connection before you call the Fill method, because the DataAdapter automatically opens and closes the connection for you. The following code fragment shows how to call Fill.

DataSet dSet = new DataSet("test");
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlQuery,conn);
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
dAdapter.Fill(dSet); // The connection was not explicitly opened.
// The connection is opened and closed by the DataAdapter automatically.

The SqlDataAdapter automatically opens the connection, runs the selected command, and then closes the connection when it is finished. This enables the connection to be open for the shortest period of time.

Note that if you need to perform multiple file or update operations, you need to open the connection before the first Fill or Update method and close it after the last one. Alternatively, you could wrap multiple Fill or Update operations inside a C# using block to ensure that the connection is closed after the last use.

Avoid Checking the State Property of OleDbConnection

If you need to monitor or check connection status and you are using an OleDbConnection, consider handling the StateChange event, and avoid checking the State property. This approach helps to minimize round trips.

Using the State property increases application overhead, because each call results in a call to the OLE DB DBPROP_CONNECTIONSTATUS property (if the connection is an OleDbConnection) for an open connection.

Note The .NET Framework 2.0 (code named "Whidbey"), at the time of writing, provides an updated OLE DB .NET Data Provider that resolves this problem.

The following code fragment shows how to implement the StateChange event. This event is raised when the state of the connection changes from open to closed or from closed to open.

OleDbConnection conn = new OleDbConnection(connStr);

// Set up a connection state change handler.
conn.StateChange  += new StateChangeEventHandler(OnStateChange);
. . .
// StateChange event handler.
protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
  Console.WriteLine("The current Connection state has changed from {0} to {1}.",
                                  args.OriginalState, args.CurrentState);
}

Note The ODBC provider also incurs similar overhead when using the State property.

Pool Connections

Creating database connections is expensive. You reduce overhead by pooling your database connections. Make sure you call Close or Dispose on a connection as soon as possible. When pooling is enabled, calling Close or Dispose returns the connection to the pool instead of closing the underlying database connection.

You must account for the following issues when pooling is part of your design:

Share connections. Use a per-application or per-group service account to connect to the database. This creates a single pool or a small number of pools, and it enables many client requests to share the same connections.
Avoid per-user logons to the database. Each logon creates a separate pooled connection. This means that you end up with a large number of small pools. If you need a different user for each connection, disable pooling or set a small maximum size for the pool.
Do not vary connection strings. Different connection strings generate different connection pools. For example, using different capitalization, extra spaces, or different ordering of attributes causes connections to go to different pools. The SQL Server .NET Data Provider performs a byte-by-byte comparison to determine whether connection strings match.
Release connections. Do not cache connections. For example, do not put them in session or application variables. Close connections as soon as you are finished with them. Busy connections are not pooled.
Passing connections. Do not pass connections between logical or physical application layers.
Consider tuning your pool size if needed. For example, in the case of the .NET Framework Data Provider for SQL Server, the default minimum pool size is zero and the maximum is 100. You might need to increase the minimum size to reduce warm-up time. You might need to increase the maximum size if your application needs more than 100 connections.
Connection pools are managed by the specific database provider. SqlClient, OleDB client, and third-party clients may provide different configuration and monitoring options.

The following list details the pooling mechanisms that are available, and it summarizes pooling behavior for the .NET Framework data providers:

The .NET Framework Data Provider for SQL Server pools connections by using a pooling mechanism implemented in managed code. You control pooling behaviors such as lifetime and pool size through connection string arguments.
The .NET Framework Data Provider for Oracle also pools connections by using a managed code solution.
The .NET Framework Data Provider for OLE DB automatically uses OLE DB session pooling to pool connections. You control pooling behavior through connection string arguments.
The .NET Framework Data Provider for ODBC uses ODBC connection pooling.

Monitoring Pooling

You can monitor connection pooling to determine that it is working as expected and to help you identify the best minimum and maximum pool sizes.

Monitoring Pooling on a Computer that is Running SQL Server

You can monitor the number of open connections to SQL Server by using the SQL Server SQLServer:General Statistics performance counter object. This object is available only on a computer that is running SQL Server.

The connections are not specific to one particular application. If there are multiple applications accessing the server, this object reflects the total number of open connections for every application. Figure 12.2 shows the SQLServer:General Statistics object in the Performance Monitor tool.

Figure 12.2: Performance monitor showing the SQLServer:General Statistics counter

When monitoring SQLServer:General Statistics, you should observe the following:

The number of logins per second increases during application startup when the connection pool is established. The number of logins per second should then drop to zero and stay there. Repeated logins and logouts per second indicate that the connection pool is not being used because a different security context is being used to establish the connection.
The User Connections value should stabilize and remain constant. If this value increases and you see a jagged pattern in the number of logins per second, you may be experiencing a connection leak in the connection pool.

Monitoring Pooling Using the .NET Framework

The .NET Framework Data Provider for SQL Server provides several counters. The following counters are of particular significance:

SqlClient: Current # connection pools
SqlClient: Current # pooled and nonpooled connections
SqlClient: Current # pooled connections
SqlClient: Peak # pooled connections

The SqlClient: Current # connection pools counter indicates the number of connection pools that are currently in use. A large number of pools indicates that a pool is not being shared across clients. Using different connection strings creates new pools.

The SqlClient: Peak # pooled connections counter indicates the maximum number of connections that are currently in use. If this value remains at its peak, consider measuring the performance impact of increasing the Max Pool Size attribute in your connection string. The default value is 100. If you see this value at its peak in conjunction with a high number of failed connections in the SqlClient: Total # failed connects counter, consider changing the value and monitoring performance.

Note These SqlClient counters may not be reset in .NET Framework version 1.1 when you stop and then restart an application. To reset the counters, stop the application and exit System Monitor, and then start the application and System Monitor again.

More Information

For more information about pooling connections, see the following resources on MSDN:

"Connection Pooling for the .NET Framework Data Provider for SQL Server" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp

For more information about pooling connections, see the following Knowledge Base articles:

164221, "INFO: How to Enable Connection Pooling in an ODBC Application" at http://support.microsoft.com/default.aspx?scid=kb;en-us;164221
166083, "INFO: How to Enable Connection Pooling in an OLE DB Application" at http://support.microsoft.com/default.aspx?scid=kb;en-us;166083
169470, "INFO: Frequently Asked Questions About ODBC Connection Pooling" at http://support.microsoft.com/default.aspx?scid=kb;en-us;169470
216950, "How to Enable ODBC Connection Pooling Performance Counters" at http://support.microsoft.com/default.aspx?scid=kb;en-us;216950
237977, "INFO: OLE DB Session Pooling Timeout Configuration" at http://support.microsoft.com/default.aspx?scid=kb;en-us;237977
316757, "BUG: SqlClient Connection Pooling That Uses Integrated Security Is Slower Than OleDb" at http://support.microsoft.com/default.aspx?scid=kb;en-us;316757

For more information about how to reset the .NET counters, see Knowledge Base article 314429, "BUG: Performance Counters for SQL Server .NET Data Provider Are Not Reset," at http://support.microsoft.com/default.aspx?scid=kb;en-us;314429.

Commands

You use Command objects such as SqlCommand, OleDbCommand, or OdbcCommand to run SQL commands against the database. You can run dynamic SQL statements or stored procedures by setting the CommandType property. You can set the CommandText property to contain the name of a stored procedure or the SQL statement that you want to run.

You use command Parameter objects, such as SqlParameter, OleDbParameter, or OdbcParameter, to specify the input and output parameters required by the current command. The recommended approach for running commands is to call stored procedures by using Parameter objects that provide parameter type checking. This approach provides both performance and security benefits. When you run commands against a database, consider the following recommendations:

Validate SQL input and use Parameter objects.
Retrieve only the columns and rows you need.
Support paging over large result sets.
Batch SQL statements to reduce round trips.
Use ExecuteNonQuery for commands that do not return data.
Use ExecuteScalar to return single values.
Use CommandBehavior.SequentialAccess for very wide rows or for rows with BLOBs.
Do not use CommandBuilder at run time.

Validate SQL Input and Use Parameter Objects

Validate all the input data that you use in SQL commands. Do not permit the client to retrieve more data than it should. Also, do not trust user input, and do not permit the client to perform operations that it should not perform. Doing so helps to lower the risk of SQL injection. By rejecting invalid data early before you issue a command that has the invalid data, you can improve performance by eliminating unnecessary database requests.

Use Parameter objects when you build database commands. When you use Parameter objects, each parameter is automatically type checked. Checking the type is another effective countermeasure you can use to help prevent SQL injection. Ideally, use Parameter objects in conjunction with stored procedures to improve performance. For more information about using parameters, see "Parameters" later in this chapter.

Using Parameters with Stored Procedures

The following code sample illustrates how to use the Parameters collection.

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
                       "@au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In the code sample, the @au_id parameter is treated as a literal value and not as code that can be run. Also, the parameter is checked for type and length. In the code fragment, the input value cannot be longer than 11 characters. If the data does not conform to the type or length that is defined by the parameter, an exception is generated.

Using stored procedures alone does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures may be susceptible to SQL injection if the stored procedures use unfiltered input. For example, the following code fragment is susceptible to SQL injection.

SqlDataAdapter myCommand = new SqlDataAdapter("LoginStoredProcedure '" +
                               Login.Text + "'", conn);

Using Parameters with Dynamic SQL

If you cannot use stored procedures, you can still use parameters with dynamic SQL as shown in the following code fragment.

SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn);
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
                        SqlDbType.VarChar, 11);
parm.Value = Login.Text;

Retrieve Only the Columns and Rows You Need

Reduce unnecessary processing and network traffic by retrieving only the columns and the rows you need. Do not use the SELECT * query. This is poor practice because you might not know the schema, or it might change. It is easy to retrieve more data than you expect. Consider a scenario where you want four columns, but you perform an operation by using the SELECT * query on a 400-column table. In that scenario, you receive many more results than you expect. Instead, use WHERE clauses to filter the rows.

Support Paging Over Large Result Sets

If you have a large result set that contains many rows of data, consider whether you can implement a paging technique to batch the retrieval of data. Batching the retrieval of data helps to reduce database server load, to reduce network traffic, and to put fewer memory requirements on the data access client. For more information, see "Paging Records" later in this chapter.

Batch SQL Statements to Reduce Round Trips

Batching is the process of grouping several SQL statements in one trip to the server. The syntax in the following code fragment calls a stored procedure (that groups several queries) to return multiple result sets. The code uses the NextResult method of the DataReader object to advance to the next result set. NextResult can be called multiple times, and it returns true when another result set exists. It returns false when there are no more result sets.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "ReadCustomerAndOrders";
// The stored procedure returns multiple result sets.
SqlDataReader myReader = cmd.ExecuteReader();

if (myReader.read())
//... read first result set

reader.NextResult();

if (myReader.read())
//... read

If you build complex SQL strings dynamically, you can use a StringBuilder object to reduce the performance cost of building the strings.

More Information

You can also use stored procedures to batch SQL operations. For more information, see Knowledge Base article 311274, "HOW TO: Handle Multiple Results by Using the DataReader in Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;311274.

Use ExecuteNonQuery for Commands That Do Not Return Data

If you want to run commands that do not retrieve data, use the ExecuteNonQuery method. For example, you would use ExecuteNonQuery for the following types of commands:

Data Definition Language commands such as CREATE and ALTER
Data Modification Language commands such as INSERT, UPDATE, and DELETE
Data Control Language commands such as GRANT and REVOKE.

The following code fragment shows an update to the customer table that uses ExecuteNonQuery.

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(
    "UPDATE Customer SET Freight = 45.44 WHERE CustomerID = 10248", conn);
cmd.ExecuteNonQuery();

Use ExecuteScalar to Return Single Values

If you want to retrieve a single value from your query by using a function such as COUNT(*) or SUM(Price), you can use a stored procedure output parameter, and then use the Command.ExecuteNonQuery method. This eliminates the overhead that is associated with creating a result set.

The following stored procedure returns the number of rows in a Customers table.

CREATE PROCEDURE GetNumberOfCustomers(
@CustomerCount int OUTPUT)
AS
SELECT @CustomerCount = COUNT(*)
FROM Customers

To call the stored procedure, use the following code.

static int GetCustomerCount()
{
  int customerCount = 0;

  SqlConnection conn = new SqlConnection("server=(local);" +
    "Integrated Security=SSPI;database=northwind");
  SqlCommand cmd = new SqlCommand("GetNumberOfCustomers", conn );
  cmd.CommandType = CommandType.StoredProcedure;

  SqlParameter paramCustCount =
    cmd.Parameters.Add("@CustomerCount", SqlDbType.Int );
    paramCustCount.Direction = ParameterDirection.Output;

  try
  {
    conn.Open();
    cmd.ExecuteNonQuery();
    customerCount = (int)paramCustCount.Value;
  }
  finally
  {
    if(null!=conn)
      conn.Close();
  }
  return customerCount;
}

If you do not have control over the stored procedure, and if the stored procedure returns the number of rows as a return value, then you can use Command.ExecuteScalar as shown in the following code fragment. The ExecuteScalar method returns the value of the first column of the first row of the result set.

static int GetCustomerCountWithScalar()
{
  int customerCount = 0;

  SqlConnection conn = new SqlConnection(
     "server=(local);Integrated Security=SSPI;database=northwind");
  SqlCommand cmd = new SqlCommand("GetCustomerCountWithScalar", conn );
  cmd.CommandType = CommandType.StoredProcedure;

  try
  {
    conn.Open();
    customerCount = (int)cmd.ExecuteScalar();
  }
  finally
  {
    if(null!=conn)
       conn.Close();
  }
  return customerCount;
}

The previous code fragment requires the following stored procedure.

CREATE PROCEDURE GetCustomerCountWithScalar
AS
SELECT COUNT(*) FROM Customers

Use CommandBehavior.SequentialAccess for Very Wide Rows or for Rows with BLOBs

Use the CommandBehavior.SequentialAccess enumeration for very wide rows or for rows that contain binary large object (BLOB) data. This permits you to return specific bytes from the retrieved row instead of returning the entire row. Returning the entire row may consume large amounts of memory because of the BLOB data.

When you use CommandBehavior.SequentialAccess, the BLOB data is retrieved only when you reference it. For example, you can call the GetBytes method. The GetBytes method permits you to control the precise number of bytes that are read. The following code fragment shows how to use CommandBehavior.SequentialAccess.

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

Also, if you are performing optimistic locking against a table with very wide rows or against rows that contain BLOB data, use timestamps. Use timestamps instead of comparing all the fields in the table to the original versions. Using time stamps reduces the number of arguments by a value that is equal to n/2+1.

More Information

For a complete sample, see "Obtaining BLOB Values from a Database" in .NET Framework Developer's Guide on MSDN at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp.

Do Not Use CommandBuilder at Run Time

CommandBuilder objects such as SqlCommandBuilder and OleDbCommandBuilder automatically generate the InsertCommand, UpdateCommand, and DeleteCommand properties of a DataAdapter. The CommandBuilder objects generate these properties based on the SelectCommand property of the DataAdapter. CommandBuilder objects are useful when you are designing and prototyping your application. However, you should not use them in production applications. The processing required to generate the commands affects performance. Manually create stored procedures for your commands, or use the Visual Studio® .NET design-time wizard and customize them later if necessary.

Stored Procedures

This section discusses how to write and to call stored procedures for maximum performance. You should generally prefer stored procedures over direct SQL statements, because stored procedures perform better. Stored procedures perform better because the database can optimize the data access plan used by the procedure and then cache it for subsequent reuse. In addition, stored procedures provide security and maintenance benefits.

Use stored procedures.
Use CommandType.Text with OleDbCommand.
Use CommandType.StoredProcedure with SqlCommand.
Consider using Command.Prepare.
Use output parameters where possible.
Consider SET NOCOUNT ON for SQL Server.

Use Stored Procedures

Stored procedures generally provide improved performance in comparison to SQL statements that are run directly. The following list explains the benefits of stored procedures compared to running data access logic directly from the middle tier of your application:

The database can prepare, optimize, and cache the execution plan so that the execution plan can be reused at a later time.
Stored procedures pass less information over the network on the initial request, because they only need to transmit the procedure name and the parameters. Everything else is already at the server.
Stored procedures abstract SQL statements from the client and business object developers and put responsibility for their maintenance in the hands of SQL experts.
Stored procedures also provide maintenance and security benefits.

More Information

For more information about the security benefits of stored procedures and about how you can use them as a countermeasure for SQL injection, see the following:

Chapter 14, "Building Secure Data Access," in "Improving Web Application Security: Threats and Countermeasures" on MSDN at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/thcmch14.asp
"Stored Procedure and Trigger Execution" in Microsoft SQL Server 2000: SQL Server Architecture on MSDN at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_7cmm.asp
MSDN article, "Query Recompilation in SQL Server 2000," at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp
Knowledge Base article 243586, "INF: Troubleshooting Stored Procedure Recompilation," at http://support.microsoft.com/default.aspx?scid=kb;en-us;243586
SQL Server Magazine article, "Inside SQL Server: Stored Procedure Plans: Improve performance through caching" at http://www.sqlmag.com/Articles/Print.cfm?ArticleID=6113.

Use CommandType.Text with OleDbCommand

If you use an OleDbCommand object to call a stored procedure, use the CommandType.Text enumeration with the ODBC call syntax. If you use CommandType.StoredProcedure, ODBC call syntax is generated by the provider anyway. By using explicit call syntax, you reduce the work of the provider.

You should also set the type and length of any parameters that the stored procedure requires. Set the type and length of the parameters to prevent the provider from performing an additional round trip to obtain the parameter information from the database. The following code fragment demonstrates how to use ODBC call syntax and CommandType.Text, and how to explicitly set parameter information.

using (OleDbConnection conn = new OleDbConnection(connStr))
{
  OleDbCommand cmd = new OleDbCommand("call CustOrderHist(?)", conn);
  cmd.CommandType = CommandType.Text;
  OleDbParameter param = cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5);
  param.Value = "ALFKI";
  conn.Open();
  OleDbDataReader reader = cmd.ExecuteReader();
  try
  {
    // List each product.
    while (reader.Read())
      Console.WriteLine(reader.GetString(0));
  }
  finally
  {
    reader.Close();
  }
} // Dispose is called on conn here

Use CommandType.StoredProcedure with SqlCommand

If you are using the SqlCommand object, use CommandType.StoredProcedure when you call stored procedures. Do not use CommandType.Text because it requires extra parsing. The following code fragment shows how to set the CommandType property to avoid extra parsing on the server.

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("UpdateCustomerProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(...

Consider Using Command.Prepare

If your application runs the same set of SQL queries multiple times, preparing those queries by using the Command.Prepare method may give you better performance. In ADO.NET, the SqlCommand.Prepare method calls the sp_prepare stored procedure for SQL Server 7. The SqlCommand.Prepare method calls sp_prepexec for SQL Server 2000 and later. SqlCommand.Prepare makes these calls instead of running a regular batch remote procedure call (RPC). The following code fragment shows how to use Command.Prepare.

cmd.CommandText =
   "insert into Region (RegionID, RegionDescription) values (@id, @desc)";

cmd.Parameters.Add ( "@id", SqlDbType.Int, 4, "RegionID") ;
cmd.Parameters.Add ( "@desc", SqlDbType.NChar, 50, "RegionDescription") ;

cmd.Parameters[0].Value = 5;
cmd.Parameters[1].Value = "North West";
cmd.Prepare();
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 6;
cmd.Parameters[1].Value = "North East";
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 7;
cmd.Parameters[1].Value = "South East";
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 8;
cmd.Parameters[1].Value = "South West";
cmd.ExecuteNonQuery();

Using the Prepare method does not yield a benefit if you are only going to run the statement one or two times. The next version of SQL Server will better leverage how plans are cached, so using it would not make a difference. You should only use the Prepare method for those statements that you run multiple times.

Use Output Parameters Where Possible

Use output parameters and ExecuteNonQuery to return small amounts of data instead of returning a result set that contains a single row. When you use output parameters and ExecuteNonQuery to return small amounts of data, you avoid the performance overhead that is associated with creating the result set on the server.

The following code fragment uses a stored procedure to retrieve the product name and unit price for a specific product that is contained in the Products table in the Northwind database.

void GetProductDetails( int ProductID,
                        out string ProductName, out decimal UnitPrice )
{
  using( SqlConnection conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=Northwind") )
  {
    // Set up the command object used to run the stored procedure.
    SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn );
    cmd.CommandType = CommandType.StoredProcedure;
    // Establish stored procedure parameters.
    //  @ProductID int INPUT
    //  @ProductName nvarchar(40) OUTPUT
    //  @UnitPrice money OUTPUT

    // Must explicitly set the direction of the output parameters.
    SqlParameter paramProdID =
             cmd.Parameters.Add( "@ProductID", ProductID );
    paramProdID.Direction = ParameterDirection.Input;
    SqlParameter paramProdName =
             cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
    paramProdName.Direction = ParameterDirection.Output;
    SqlParameter paramUnitPrice =
             cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
    paramUnitPrice.Direction = ParameterDirection.Output;

    conn.Open();
    // Use ExecuteNonQuery to run the command.
    // Although no rows are returned, any mapped output parameters
    // (and potential return values) are populated
    cmd.ExecuteNonQuery( );
    // Return output parameters from stored procedure.
    ProductName = paramProdName.Value.ToString();
    UnitPrice = (decimal)paramUnitPrice.Value;
  }
}

Consider SET NOCOUNT ON for SQL Server

When you use SET NOCOUNT ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF, the count is returned. Using SET NOCOUNT ON can improve performance because network traffic can be reduced. SET NOCOUNT ON prevents SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure or batch of SQL statements.

For example, if you have eight operations in a stored procedure, eight messages are returned to the caller. Each message contains the number of rows affected by the respective statement. When you use SET NOCOUNT ON, you reduce the processing that SQL Server performs and the size of the response that is sent across the network.

Note In Query Analyzer, the DONE_IN_PROC message is intercepted and displayed as "N rows affected".

Parameters

Most SQL commands require input or output parameters, regardless of whether they are stored procedures or direct SQL statements. Each .NET Framework data provider provides a Parameter object implementation. You can use a Parameter object implementation in conjunction with a Command object. Some sample parameter objects include SqlParameter, OleDbParameter, and OdbcParameter. When you use parameters, consider the following recommendations:

Use the Parameters collection when you call a stored procedure.
Use the Parameters collection when you build SQL statements.
Explicitly create stored procedure parameters.
Specify parameter types.
Cache stored procedure SqlParameter objects.

Use the Parameters Collection When You Call a Stored Procedure

Use the Parameters collection property of the SqlCommand object to pass parameters to a stored procedure. By using strongly typed parameters, types do not need to be discovered at run time. You can also save round trips by checking the data type on the client; for example, you can save round trips by checking the Web server. This prevents wasted cycles and wasted bandwidth that is caused by passing invalid data to the database server. The following code fragment shows how to add a typed parameter to the Parameters collection.

SqlDataAdapter adapter = new SqlDataAdapter("GetProductDesc",
                                             conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = adapter.SelectCommand.Parameters.Add(
                                             "@ProdID", SqlDbType.Int);
parm.Value = 10;

Use the Parameters Collection When You Build SQL Statements

Even if you do not use stored procedures for data access, you should still use the Parameters collection when you build your SQL statements in code. By using the Parameter collection and by explicitly setting the data type, you reduce the likelihood that the Parameter object could set an invalid type. The following code fragment shows how to use the Parameters collection when you build your SQL statements in code.

SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT ProductID, ProductName FROM Products WHERE ProductID = @ProdID", conn);
// Set the parameter including name and type.
SqlParameter parm = adapter.SelectCommand.Parameters.Add("@ProdID",
                                                          SqlDbType.Int);
// Set the parameter value.
parm.Value = 10;

Explicitly Create Stored Procedure Parameters

Identifying parameters at run time requires a round trip to the server for each use of a stored procedure. This is an expensive operation. Explicitly create parameters for stored procedures. Explicitly supply the parameter type, size, precision, and scale information to prevent the Command object from recreating them every time a command is run. The following code demonstrates how to set the type, size, and direction.

void GetProductDetails( int productID,
                        out string productName, out decimal unitPrice)
{
  using( SqlConnection conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=Northwind") )
  {
    // Set up the command object used to run the stored procedure.
    SqlCommand cmd = new SqlCommand( "GetProductDetails", conn );
    cmd.CommandType = CommandType.StoredProcedure;
    // Establish stored procedure parameters.
    //  @ProductID int INPUT
    //  @ProductName nvarchar(40) OUTPUT
    //  @UnitPrice money OUTPUT

    // Must explicitly set the direction of output parameters.
    SqlParameter paramProdID =
             cmd.Parameters.Add( "@ProductID", SqlDbType.Int );
    paramProdID.Direction = ParameterDirection.Input;
    SqlParameter paramProdName =
             cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
    paramProdName.Direction = ParameterDirection.Output;
    SqlParameter paramUnitPrice =
             cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
    paramUnitPrice.Direction = ParameterDirection.Output;

    conn.Open();
    cmd.ExecuteNonQuery( );
    // Return output parameters from the stored procedure.
    productName = paramProdName.Value.ToString();
    unitPrice = (decimal)paramUnitPrice.Value;
  }
}

Specify Parameter Types

When you create a new parameter, use the relevant enumerated type to specify the data type of the parameter. Use an enumerated type such as SqlDbType or OleDbType. This prevents unnecessary type conversions that are otherwise performed by the data provider.

Cache Stored Procedure SqlParameter Objects

Often, applications must run commands multiple times. To avoid recreating the SqlParameter objects each time, cache them so that they can be reused later. A good approach is to cache parameter arrays in a Hashtable object. Each parameter array contains the parameters that are required by a particular stored procedure that is used by a particular connection. The following code fragment shows this approach.

public static void CacheParameterSet(string connectionString,
                                     string commandText,
                                     params SqlParameter[] commandParameters)
{
  if( connectionString == null || connectionString.Length == 0 )
    throw new ArgumentNullException( "connectionString" );
  if( commandText == null || commandText.Length == 0 )
    throw new ArgumentNullException( "commandText" );

  string hashKey = connectionString + ":" + commandText;
  paramCache[hashKey] = commandParameters;
}

The following function shows the equivalent parameter retrieval function

public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
  if( connectionString == null || connectionString.Length == 0 )
    throw new ArgumentNullException( "connectionString" );
  if( commandText == null || commandText.Length == 0 )
    throw new ArgumentNullException( "commandText" );

  string hashKey = connectionString + ":" + commandText;

  SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
  if (cachedParameters == null)
  {
    return null;
  }
  else
  {
    return CloneParameters(cachedParameters);
  }
}

When parameters are retrieved from the cache, a cloned copy is created so that the client application can change parameter values, without affecting the cached parameters. The CloneParameters method is shown in the following code fragment.

private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
  SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

  for (int i = 0, j = originalParameters.Length; i < j; i++)
  {
    clonedParameters[i] =
      (SqlParameter)((ICloneable)originalParameters[i]).Clone();
  }
  return clonedParameters;
}

More Information

The code samples for the parameter caching approach that is shown above are based on samples from the Data Access Application Block. The Data Access Application Block implements this functionality in a generic data access component. For more information, see the Data Access Application Block on MSDN at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp.

DataSet vs. DataReader

When you need to retrieve multiple rows of data so that you can display or process the data in some other way, you have two basic choices. You can use a DataSet object or a DataReader object.

The DataReader approach is generally quicker because it avoids the overhead that is associated with creating a DataSet object. The overhead that is associated with a DataSet object includes creating DataSet subobjects such as DataTables, DataRows, and DataColumns. However, the DataReader provides less flexibility, and is less suited to situations where you have to cache data and pass the data to components in an application that has multiple tiers.

Note The DataAdapter used to fill the DataSet uses a DataReader internally.

Use a DataReader when the following conditions are true:

You need forward-only, read-only access to data (the fire hose scenario), and you want to access the data as quickly as possible, and you do not need to cache it.
You have a data container such as a business component that you can put the data in.

Use a DataSet when the following conditions are true:

You have to cache or pass the data between layers.
You require an in-memory relational view of the data for XML or non-XML manipulation.
You want to update some or all the retrieved rows, and you want to use the batch update facilities of the SqlDataAdapter class.
You have to bind data to a control type that the DataReader cannot be bound to. Many Windows Forms controls capable of data binding require a data source that implements the IList interface. The DataSet implements IList, but the DataReader implements IEnumerable. IEnumerable supports data binding to most Web Form controls but not to certain Windows Forms controls. Check the data source requirements for the particular control type that you want to bind.
You have to access multiple sets of data at the same time, and you do not want to hold open server resources.

DataReader

The DataReader provides a read-only, forward-only stream of data from a database. When you use DataReader objects such as SqlDataReader or OleDbDataReader, consider the following recommendations:

Close DataReader objects.
Consider using CommandBehavior.CloseConnection to close connections.
Cancel pending data.
Consider using CommandBehavior.SequentialAccess with ExecuteReader.
Use GetOrdinal when using an index-based lookup.

Close DataReader Objects

Close your DataReader object as soon as you are finished with it, either by calling its Close method or by calling its Dispose method. It is best to use a finally block to ensure that the DataReader is closed as shown in the following code fragment.

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = new SqlCommand("CustomerRead",conn);

  conn.Open();
  SqlDataReader dr = cmd.ExecuteReader();
  try
  {
    while (dr.Read())
      Console.WriteLine(dr.GetString(0));
  }
  finally
  {
    dr.Close();
  }
}

Consider Using CommandBehavior.CloseConnection to Close Connections

If you need to return a DataReader from a method, consider using the CommandBehavior.CloseConnection method to ensure that the associated connection is closed when the DataReader is closed. The following code fragment shows this approach.

public SqlDataReader RetrieveRowsWithDataReader()
{
  SqlConnection conn = new SqlConnection(
         "server=(local);Integrated Security=SSPI;database=northwind");
  SqlCommand cmd = new SqlCommand("RetrieveProducts", conn );
  cmd.CommandType = CommandType.StoredProcedure;
  try
  {
    conn.Open();
    // Generate the reader. CommandBehavior.CloseConnection causes
    // the connection to be closed when the reader object is closed.
    return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) );
  }
  finally
  {
    if(null!=conn)
       conn.Close();
  }
}

// Display the product list using the console.
private void DisplayProducts()
{
  SqlDataReader reader = RetrieveRowsWithDataReader();
  try
  {
    while (reader.Read())
    {
      Console.WriteLine("{0} {1}",
                        reader.GetInt32(0).ToString(),
                        reader.GetString(1));
    }
  }
  finally
  {
     if(null!= reader)
        reader.Close(); // Also closes the connection due to the CommandBehavior
                        // enumerator used when generating the reader.
  }
}

Cancel Pending Data

When you call the Close method, the method does not return until all the remaining data has been fetched. If you know you have pending data when you want to close your DataReader, you can call the Cancel method before you call Close to tell the server to stop sending data.

This approach does not always result in a performance improvement, because Cancel is not guaranteed to make the server stop sending data. Control information is still exchanged after the call to Cancel, and the control information may or may not be interleaved with leftover data. Therefore, before you restructure your code to call Cancel before Close, test Cancel to learn if it actually helps in your particular scenario and to learn if you really need the extra performance at the expense of readability.

Note If you need output parameters, do not call Close until you have retrieved the output parameters. After you retrieve the output parameters, you can then call Close.

Consider Using CommandBehavior.SequentialAccess with ExecuteReader

If you do not have to have random access to columns, use CommandBehavior.SequentialAccess when you call the ExecuteReader method of the Command object.

Use GetOrdinal When Using an Index-Based Lookup

Using an index or ordinal-based lookup is faster than using string-based column names. However, using an index adds code maintenance overhead. Using an index requires you to change the index when the query column-order changes or when table columns are changed. Instead of hard coding the values, you can use GetOrdinal to get the index as shown in the following code fragment.

cmd.CommandText = "Select RegionDescription, RegionId from Region";
SqlDataReader dr = cmd.ExecuteReader();

int RegionId = dr.GetOrdinal("RegionId");
int RegionDescription = dr.GetOrdinal("RegionDescription");

while( dr.Read())
{
  Console.WriteLine(dr[RegionId] + " - " + dr[RegionDescription]);
}

DataSet

If you need to work with a disconnected, cached set of data, you usually create a DataSet by using a DataAdapter. To help optimize the performance of DataSet objects, consider the following recommendations:

Reduce serialization.
Use primary keys and Rows.Find for indexed searching.
Use a DataView for repetitive non-primary key searches.
Use the Optimistic concurrency model for datasets.

Reduce Serialization

DataSet serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, DataSet serialization often introduces performance bottlenecks. You can reduce the performance impact in a number of ways:

Use column name aliasing. The serialized data contains column names so that you can use column name aliasing to reduce the size of the serialized data.
Avoid serializing multiple versions of the same data. The DataSet maintains the original data along with the changed values. If you do not need to serialize new and old values, call AcceptChanges before you serialize a DataSet to reset the internal buffers.
Reduce the number of DataTable objects that are serialized. If you do not need to send all the DataTable objects contained in a DataSet, consider copying the DataTable objects you need to send into a separate DataSet.

More Information

For more information, see "How To: Improve Serialization Performance" in the "How To" section of this guide.

Use Primary Keys and Rows.Find for Indexed Searching

If you need to search a DataSet by using a primary key, create the primary key on the DataTable. This creates an index that the Rows.Find method can use to quickly find the records that you want. Do not use DataTable.Select because DataTable.Select does not use indices.

Use a DataView for Repetitive Non-Primary Key Searches

If you need to repetitively search by using non-primary key data, create a DataView that has a sort order. This creates an index that can be used to perform the search. This is best suited to repetitive searches because there is some cost to creating the index.

The DataView object exposes the Find and FindRows methods so that you can query the data in the underlying DataTable. If you are only performing a single query, the processing that is required to create the index reduces the performance that is gained by using the index.

When you create a DataView object, use the DataView constructor that takes the Sort, RowFilter, and RowStateFilter values as constructor arguments along with the underlying DataTable. Using the DataView constructor ensures that the index is built once. If you create an empty DataView and set the Sort, RowFilter, or RowStateFilter properties afterwards, the index is built at least two times.

Use the Optimistic Concurrency Model for Datasets

There are two concurrency models that you can use when working with datasets in an environment that has multiple users. These two models are the pessimistic and optimistic models. When you read data and use the pessimistic model, locks are established and held until updates are made and the locks are released. Holding locks on server resources, in this case database tables, leads to contention issues. It is best to use granular locks for very short durations.

The optimistic model does not lock the data when the data is read. The optimistic model locks the data just before the data is updated and releases the lock afterwards. There is less contention for data with the optimistic model, which is good for shared server scenarios; however, you should take into account the scenarios for managing the concurrency violations. A common technique you can use to manage concurrency violations is to implement a timestamp column or to verify against the original copy of data.

More Information

For more information about how to implement optimistic concurrency solutions, see "Optimistic Concurrency" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconOptimisticConcurrency.asp.

XML and DataSet Objects

Data and schema information maintained within DataSet objects can be output as XML. Also, you can populate a DataSet object from an XML data stream. If you use XML and DataSets, consider the following recommendations:

Do not infer schemas at run time.
Perform bulk updates and inserts by using OpenXML.

Do Not Infer Schemas at Run Time

Limit schema inference to design time. When you load a DataSet, ensure that your schema is not inferred, which can happen by default. The inference process is costly. To ensure that your existing schema is used and that no schema is inferred, pass XmlReadMode.IgnoreSchema to the ReadXml method.

Perform Bulk Updates and Inserts by Using OpenXML

Different .NET Framework data providers enable you to do bulk updates and inserts by using the OpenXML method. You can use OpenXML to minimize SQL Server database calls, because you can use the OpenXML function to insert multiple rows of data in a single database call. OpenXML enables you to effectively package data together in a single call as XML, map it to a rowset view, and execute all of the inserts within the same database call. This helps reduce calls and resource utilization. The following code fragment shows you how to use OpenXML for updates and inserts.

--This code UPDATES data.
UPDATE Employee
SET
   Employee.FirstName = XMLEmployee.FirstName,
   Employee.LastName = XMLEmployee.LastName
   FROM OPENXML(@hDoc, 'NewDataSet/Employee')
       WITH (EmployeeId Integer, FirstName varchar(100),  LastName varchar(100))  XMLEmployee
WHERE    Employee.EmployeeId = XMLEmployee.EmployeeId

--This code inserts new data.
Insert Into Employee
SELECT EmployeeId, FirstName, LastName
       FROM  OPENXML (@hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100),  LastName varchar(100))  XMLEmployee
Where XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)

More Information

For a complete code sample that shows how to use the OpenXML method, see Knowledge Base article 315968, "HOW TO: Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;315968.

For more information about XML and DataSet objects, see "Employing XML in the .NET Framework" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconemployingxmlinnetframework.asp.

Typed DataSets

A typed DataSet is a custom object that derives from the DataSet base class. It supports typed data access through an exposed set of properties specific to the encapsulated data.

Use typed DataSet objects to avoid late-bound field access. The strongly typed accessors are provided by the typed DataSet and are faster because they eliminate column or table name lookups in a collection.

In addition to run-time performance benefits, typed datasets provide strong type checking and IntelliSense® by using custom field names at design time.

More Information

For more information about typed DataSet objects, see the following Knowledge Base articles:

320714, "HOW TO: Create and Use a Typed DataSet by Using Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;320714
313486, "INFO: Roadmap for Visual Database Tools and Typed DataSets," at http://support.microsoft.com/default.aspx?scid=kb;en-us;313486

Types

When you access a data source by using stored procedures or dynamic SQL commands, make sure that you specify the precise data type for the input and output parameters. By specifying the precise database type, you can help performance in the following ways:

You help prevent internal type conversions from being performed by the data provider. Internal type conversions can lead to loss of precision.
You help reduce round trips that the data provider might make to the database to discover type information at run time.
You enable the data provider to perform type checks at the client and fail early with type exceptions. This helps avoid unnecessary round trips to the server.

Avoid Unnecessary Type Conversions

Type conversions can occur when you pass parameters to and from stored procedures or other SQL statements. To avoid type conversions, make sure that you:

Set the provider-specific type property of each Parameter object.
Pass a consistent object type when you set the Value property of the Parameter object.

For example, the .NET Framework Data Provider for SQL Server defines the System.Data.SqlTypes namespace. This namespace provides classes that represent the native data types in SQL Server. This namespace also includes the SqlDbType enumeration, which you use to specify the precise type of a parameter that is passed through a SqlParameter object.

The following code demonstrates how to avoid type conversions for the .NET Framework Data Provider for SQL Server.

// Set the provider-specific type for the parameter.
SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
// Use the right provider-specific type. In this case use SqlString, which
// corresponds to SqlDbType.NVarChar
param.Value = new SqlString("Frederick Smith");

The type namespaces and type enumerations for each data provider are summarized in Table 12.1.

Table 12.1: Database Provider Types

Provider	Type namespace	Type enumeration
SQL Server	System.Data.SqlTypes	SqlDbType
OLE DB	System.Data.OleDb	OleDbType
ODBC	System.Data.OdbcType	OdbcType
Oracle	System.Data.OracleClient	OracleType

Exception Management

ADO.NET errors that are propagated through SqlException or OleDbException objects use custom error handling for specific data access code. Consider the following guidelines for exception handling in ADO.NET data access code:

Use the ConnectionState property. Avoid relying on an error handler to detect connection state availability. When you can, use the ConnectionState.Open or ConnectionState.Close method to check the state before use.
Use try/finally to clean up resources. Use try/finally more often than try/catch/finally. Using finally gives you the option to close the connection, even if an exception occurs. If you develop in C#, the using statement provides this functionality with code that is easy to maintain, as shown in the following code fragment.
```
using( SqlConnection conn = new SqlConnection(
      "server=(local);Integrated Security=SSPI;database=Northwind") )
{ . . . }
```
Use specific handlers to catch specific exceptions. If you know that there are scenarios where specific errors could possibly occur, use specific handlers. For example, if you want to know if a concurrency violation occurs when multiple updates are occurring, look for exceptions of type DBConcurrencyException. The specific exception classes SqlException and OleDbException provide a detailed message when errors occur. Use this message to log the details. Ensure that specific exceptions precede generic handlers, as shown in the following code fragment.
```
try
{ ...
}
catch (SqlException sqlex) // specific handler
{ ...
}
catch (Exception ex) // Generic handler
{ ...
}
```

For more information about exception handling guidelines specific to performance, see Chapter 5, "Improving Managed Code Performance."

Transactions

Transactions are important for ensuring data integrity but come at an operational cost. Selecting the right transaction management mechanism for your application can significantly improve scalability and performance. Key considerations include the type and quantity of resources involved and the isolation level required for the transactions. When you determine how you should manage transactions in your system, consider the following recommendations:

Use SQL transactions for server-controlled transactions on a single data store.
Use ADO.NET transactions for client-controlled transactions on a single data store.
Use DTC for transactions that span multiple data stores.
Keep transactions as short as possible.
Use the appropriate isolation level.
Avoid code that can lead to deadlock.
Set the connection string Enlist property to false.

Use SQL Transactions for Server-Controlled Transactions on a Single Data Store

If you need to write to a single data store, and if you can complete the operation in a single call to the database, use the transaction control provided by the SQL language on your database server. The transaction runs close to the data and reduces the cost of the transaction. Running the transaction close to the data also permits database administrators to tune the operation without changing the deployment of your application code. The following code fragment shows a simple T-SQL transaction performed in a stored procedure.

BEGIN TRAN

UPDATE Orders SET Freight=@Freight Where OrderID=@OrderID
UPDATE [Order Details] SET Quantity=@Quantity Where OrderID=@OrderID

IF (@@ERROR > 0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Note If you need to control a transaction across multiple calls to a single data store, use ADO.NET manual transactions.

Use ADO.NET Transactions for Client-Controlled Transactions on a Single Data Store

If you need to make multiple calls to a single data store participate in a transaction, use ADO.NET manual transactions. The .NET Data Provider for SQL Server and the .NET Data Provider for Oracle use the appropriate transaction language to enforce transactions on all subsequent SQL commands.

If you use SQL Profiler to monitor your use of ADO.NET manual transactions, you see that BEGIN TRAN, COMMIT TRAN, or ROLLBACK TRAN is run against the data store on your behalf by the provider. This enables you to control the transaction from your .NET Framework code and to maintain performance at a level that is similar to SQL transactions. The following code fragment shows how to use ADO.NET transactions.

SqlConnection conn = new SqlConnection(connString);
SqlTransaction trans = conn.BeginTransaction();
try
{
  SqlCommand cmd = new SqlCommand("MyWriteProc",conn, trans);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add(....
  …  // additional transactioned writes to database
  trans.Commit();
}
catch
{
  trans.Rollback();
}

When you use ADO.NET manual transactions, you can set the desired isolation level on the BeginTransacion method as shown in the following code fragment.

SqlConnection conn = new SqlConnection(connString);
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

More Information

For a more information about isolation levels, see "Use the Appropriate Isolation Level" later in this chapter.

Use DTC for Transactions That Span Multiple Data Stores

Enterprise Services uses the Microsoft Distributed Transaction Coordinator (DTC) to enforce transactions. If you have a transaction that spans multiple data stores or resource manager types, it is best to use Enterprise Services to enlist the data sources in a distributed transaction. Using Enterprise Services to enlist the data sources in this scenario is simple to configure.

The DTC performs the inter-data source communication and ensures that either all the data is committed or that none of the data is committed. This action creates an operational cost. If you do not have transactions that span multiple data sources, use SQL or ADO.NET manual transactions because they perform better.

Keep Transactions as Short as Possible

Design your code to keep transactions as short as possible to help minimize lock contention and to increase throughput. Avoid selecting data or performing long operations in the middle of a transaction.

Use the Appropriate Isolation Level

Resource managers such as SQL Server and other database systems support various levels of isolation for transactions. Isolation shields operations from the effect of other concurrent transactions. Most resource managers support the four isolation levels shown in Table 12.2. The isolation level determines the types of operation that can occur. The types of operation that can occur include dirty reads, nonrepeatable reads, or phantoms.

Table 12.2: Isolation Levels

Isolation level	Dirty reads	Nonrepeatable reads	Phantoms
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable	No	No	No

The highest isolation level, serializable, protects a transaction completely from the effects of other concurrent transactions. This is the most expensive isolation level in terms of server resources and performance. By selecting a lower level of isolation and writing the code for your transactions to deal with the effects of other concurrent transactions, you can improve performance and scalability. However, this approach may come at the expense of more complex code.

Avoid Code That Can Lead to Deadlock

Consider the following general guidelines when you use transactions so that you can avoid causing deadlocks:

Always access tables in the same order across transactions in your application. The likelihood of a deadlock increases when you access tables in a different order each time you access them.
Keep transactions as short as possible. Do not make blocking or long-running calls from a transaction. Keep the duration of the transactions short. One approach is to run transactions close to the data source. For example, run a transaction from a stored procedure instead of running the transaction from a different computer.
Choose a level of isolation that balances concurrency and data integrity. The highest isolation level, serializable, reduces concurrency and provides the highest level of data integrity. The lowest isolation level, read uncommitted, gives the opposite result. For more information, see "Use the Appropriate Isolation Level" earlier in this chapter.

Set the Connection String Enlist Property to False

A pooled transactional object must enlist its connection into the current transaction manually. To enable it to do so, you must disable automatic transaction enlistment by setting the connection string Enlist property to False.

Note This applies to a SqlConnection. For an OleDbConnection, you need to set OLE DB Services=-7 as a connection string parameter.

Pooled components that maintain database connections might be used in different transactions by separate clients. A pooled transactional object must be able to determine if it is activated in a new transaction that is different from the last time it was activated.

Each time a pooled transactional object is activated, it should check for the presence of a COM+ transaction in its context by examining ContextUtil.Transaction. If a transaction is present and the connection is not already enlisted, the object should enlist its connection manually by calling the EnlistDistributedTransaction method of the Connection object.

More Information

For more information about transaction options and how to analyze transaction performance, see the following resources on MSDN:

"Performance Comparison: Transaction Control" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch13.asp
"Implementing Database Transactions with Microsoft .NET" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/psent.asp

For more information about enlisting a pooled object in a distributed transaction, see "Enlisting in a Distributed Transaction" in the .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconenlistingindistributedtransaction.asp.

Binary Large Objects

A binary large object (BLOB) is a binary resource such as an image, a sound or video clip, or a document. Storing BLOBs in a database can cause significant resource pressure. For example, large BLOBs can consume large amounts of memory, CPU, and networking resources on both the client and the server.

You can choose to handle BLOBs as a whole or handle them in chunks.

Handling BLOBs as a whole is useful when the BLOB is not very large in size, and you require the complete BLOB to be in memory before you perform the operation. This approach tends to put excessive memory pressure on the server as well as on network bandwidth.

Compared to handling BLOBs as a whole, chunking does cause more round trips, but chunking creates less load on the server and reduces network bandwidth use. The network bandwidth is not excessively consumed because you transfer the data in chunks rather than passing the BLOB all at one time. Therefore, the server only has to take care of the immediate buffer passed to it. The server can either store the buffer to the disk or redirect it as an output stream to the client.

ADO.NET data providers do not provide the GetChunk and AppendChunk methods in the same way that Data Access Objects (DAO) and ActiveX Data Objects (ADO) do with Recordset objects. However, this section describes the alternate options that exist.

Consider the following when you are working with BLOBs:

Use CommandBehavior.SequentialAccess and GetBytes to read data.
Use READTEXT to read from SQL Server 2000.
Use OracleLob.Read to read from Oracle databases.
Use UpdateText to write to SQL Server databases.
Use OracleLob.Write to write to Oracle databases.
Avoid moving binary large objects repeatedly.

Use CommandBehavior.SequentialAccess and GetBytes to Read Data

The default behavior of the DataReader is to read an entire row into memory. All columns are accessible in any order until the next row is read.

If you retrieve large BLOBs, reading the whole BLOB into memory may cause excessive memory consumption. Using CommandBehavior.SequentialAccess enables you to stream the data or to send the data in chunks from the column containing the BLOB by using the GetBytes, GetChars, or GetString methods.

The following code fragment shows how to use the SequentialAccess and GetBytes methods.

// Allocate a buffer to hold a BLOB chunk.
int bufferSize = 100;  // the size of the buffer to hold interim chunks of the BLOB
byte[] outbyte = new byte[bufferSize];  // The buffer to hold the BLOB

SqlDataReader myReader = empCmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (myReader.Read())
{
  // The BLOB data is in column two. Must get the first column
  // before the BLOB data.
   empID = myReader.GetInt32(0); // First column
  // Read the bytes into outbyte[] and retain the number of bytes returned.
   retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
  // Continue reading and writing while there are bytes beyond the
  // Size of the buffer.
  while (retval == bufferSize)
  {
    // Write data to a file or to a Web page (omitted for brevity).
    . . .
    // Reposition the start index to the end of the last buffer
    // and fill the buffer.
    startIndex += bufferSize;
    retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
   }
}

Note When you use CommandBehavior.SequentialAccess, you must retrieve columns in sequence. For example, if you have three columns, and the BLOB data is in the third column, you must retrieve the data from the first and second columns, before you retrieve the data from the third column.

More Information

For more information, see "Obtaining BLOB Values from a Database" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp.

Use READTEXT to Read from SQL Server 2000

The READTEXT command reads text, ntext, or image values from a text, ntext, or image column. The READTEXT command starts reading from a specified offset and reads the specified number of bytes. This command is available in SQL Server 2000 and later. This command enables you to read data in chunks by sending a fixed set of bytes over the network for each iteration. The following are the steps you must follow to use the READTEXT command:

Obtain a pointer to the BLOB by using the TEXTPTR command.
Read the BLOB, by using the READTEXT command, in the required chunk size, with the help of the pointer that you obtained in step 1.
Send the data to the client.
Read the data on the client, and then store it in a buffer or a stream.

The following code fragment shows how to use the READTEXT command.

int BUFFER_LENGTH  = 32768; // chunk size
// Obtain a pointer to the BLOB using TEXTPTR.
SqlCommand cmdGetPointer = new SqlCommand(
    "SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture)" +
    "FROM Categories WHERE CategoryName='Test'", conn);

// Set up the parameters.
SqlParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);

// Run the query.
// Set up the READTEXT command to read the BLOB by passing the following
// parameters: @Pointer – pointer to blob, @Offset – number of bytes to
// skip before starting the read, @Size – number of bytes to read.
SqlCommand cmdReadBinary = new SqlCommand(
     "READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", conn);
// Set up the parameters for the command.
SqlParameter SizeParam  = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int);
SqlDataReader dr;
int Offset= 0;
Byte []Buffer = new Byte[BUFFER_LENGTH ];
// Read buffer full of data.
do {
  // Add code for calculating the buffer size - may be less than
  // BUFFER  LENGTH  for the last block.
  dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
  dr.Read();
  dr.GetBytes(PictureCol, 0, Buffer, 0,  System.Convert.ToInt32(SizeParam.Value));
  Offset += System.Convert.ToInt32(SizeParam.Value);
  OffsetParam.Value = Offset;
} while( //Check for the offset until it reaches the maximum size.);

More Information

For more information about the READTEXT command, see Knowledge Base article 317043, "HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;317043.

Use OracleLob.Read to Read from Oracle Databases

To read BLOBs from an Oracle database, use the .NET Framework Data Provider for Oracle. This data provider provides the System.Data.OracleClient.OracleLob class that can read BLOBs. The following code fragment shows how the OracleLob.Read method enables you to read the data in chunks.

byte[] buffer = new byte[100];
  while((actual = blob.Read(buffer, 0/*buffer offset*/,
         buffer.Length/*count*/)) >0)
{ //write the buffer to some stream
}

More Information

For more information about OracleLob.Read, see "OracleLob.Read Method" in .NET Framework Class Library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclelobclassreadtopic.asp.

Use UpdateText to Write to SQL Server Databases

If you are using SQL Server, you can use the UpdateText function to write the data in chunks, as shown in the following code fragment.

int BUFFER_LENGTH = 32768; // Chunk size.
// Set the existing BLOB to null and
// Obtain a pointer to the BLOB using TEXTPTR
SqlCommand cmdGetPointer = new SqlCommand(
 "SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
 "SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'",
  cn);

// Set up the parameters.
// Run the query.

// Set up the UPDATETEXT command to read the BLOB by passing the following
// parameters: @Pointer – pointer to blob, @Offset – number of bytes to
// skip before starting the read, @Size – number of bytes to read.
SqlCommand cmdUploadBinary = new SqlCommand(
  "UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn);
// Set up the parameters.
// Read buffer full of data and then run the UPDATETEXT statement.
Byte [] Buffer = br.ReadBytes(BUFFER_LENGTH);
while(Buffer.Length > 0)
{
  PointerParam.Value = PointerOutParam.Value;
  BytesParam.Value = Buffer;
  cmdUploadBinary.ExecuteNonQuery();
  DeleteParam.Value = 0; //Do not delete any other data.
  Offset += Buffer.Length;
  OffsetParam.Value = Offset;
  Buffer = br.ReadBytes(BUFFER_LENGTH);
}

More Information

For more information about writing BLOB data to SQL Server, see "Conserving Resources When Writing BLOB Values to SQL Server" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconservingresourceswhenwritingblobvaluestosqlserver.asp.

Or, see Knowledge Base article 317043, "HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;317043.

Use OracleLob.Write to Write to Oracle Databases

You can write BLOBs to an Oracle database by using the .NET Framework data provider for Oracle. This data provider permits the System.Data.OracleClient.OracleLob class to write BLOBs. The OracleLob.Write method enables you to write data in chunks.

More Information

For more information, see "OracleLob.Write Method," in .NET Framework Class Library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclelobclasswritetopic.asp.

Avoid Moving Binary Large Objects Repeatedly

Avoid moving BLOB data more than one time. For example, if you build a Web application that serves images, store the images on the file system and the file names in the database instead of storing the images as BLOBs in the database.

Storing the images as BLOBs in the database means that you must read the BLOB from the database to the Web server and then send the image from the Web server to the browser. Reading the file name from the database and having the Web server send the image to the browser reduces the load on the database server. It also reduces the data that is sent between the database and the Web server. This can significantly affect performance and scalability.

Paging Records

Paging records is a common application scenario. The records that you need to page through can often be based on user input. For example, they can be based on a search keyword entered through a search screen. Or, the records can be common to all users. For example, a product catalogue is a record that is common to all users.

Paging costs can be divided into the following stages:

Processing cost at the database. This includes processor and memory use, and disk I/O.
Network cost for the amount of data sent across the network.
Processing cost at the client. This includes the memory required to store records, and processor use for processing the records.

Paging records may be expensive for the following reasons:

Inefficient queries may increase the processing cost in all the stages mentioned in this section. The database has to process an increased number of rows, more data than is required is sent over the network, and the client has to process additional records to show the relevant ones to the user.
Inappropriate caching of data to be paged. Some of the paging implementations require the client to cache data and then page through it. These solutions can lead to excessive memory pressure if the cache is maintained on a per-user basis.

There are two basic approaches to paging:

You can return the whole result set from the database to the client. The client caches the result set and then displays the most relevant results to the user by using the correct filtering mechanism.
You can have the database assume the additional role of a filter by making the database return only the most relevant result set to the client.

More Information

For more information about how to choose and implement the best solution for your scenario, see "How To: Page Records in .NET Applications" in the "How To" section of this guide.

Analyzing Performance and Scalability of Data Access

When you evaluate the performance and scalability of your data access decisions, you should examine the impact that your code has on the server, on the network, and on the client. A good data access solution uses server resources in a timely and efficient manner, transports only the data that is required, and permits the client to quickly consume the data and then release resources.

Start by running simple logical operation tests, and then examine the key metrics and related questions listed in Table 12.3. Review the results, and then use the information in the table to improve performance and scalability.

Table 12.3: Metrics for Analyzing Data Access Performance

Metric	Questions
Run time	How long did the operation take on the server? How long did the operation take on the client?
Network trips	How many network trips were required to complete the operation? Is there a way to reduce or consolidate the trips?
Index use	Did the operation use indexes? Was the index use efficient?
Records processed/retrieved	Did the operation process more records than it returned? Did the operation return more records than you wanted? Can paging help reduce the records processed and returned?
CPU use	Was CPU use on the server excessive? Can it be reduced by different SQL language or by computing data beforehand?
Memory use	How much memory on the server was used to process the SQL operation? How much memory on the client was used to process the data that was retrieved?
Network bandwidth	How much bandwidth did the operation use? Is there a way to decrease that amount used by returning fewer rows or fewer columns?
Transactions	Are transactions creating a deadlock or failing? Is there a way to commit the transaction faster?

After you examine single operations, run load tests. Monitor the following when you run the load tests:

Pooling. Monitor pooling to ensure that connections are returned efficiently to the pool so that they can be reused. Ensure that you can close your connections early in your code.
Locks. Monitor locks to find out whether the locks are held as long as they could be held. Find out if you can reduce the number of locks that you hold, and if you can shorten the duration that you hold the existing locks.

Tool Support

Use the following tools to monitor the metrics that are listed in Table 12.3:

SQL Query Analyzer. When you run an SQL command, you can use the Statistics tab to monitor the duration (in milliseconds), the affected rows, the server round trips, and the bytes transmitted. You can also use SQL Query Analyzer to show you the execution plan that SQL Server uses to run your SQL operation. You can use this feature to identify missed indexes that manifest as table scans.
SQL Profiler. You can use SQL Profiler to monitor an enormous amount of information that includes cursors, locks, and transactions. Use this tool to identify the resources that are used, the operations that are performed, and the length of time (in milliseconds) that particular operations take.
Performance Counters. Use performance counters to monitor connection pooling, index hits and misses, cache hits and misses, and locks.

More Information

For more information about measuring data access performance, see "ADO.NET/Data Access" in Chapter 15, "Measuring .NET Application Performance"

Summary

The database is often a focal point for application load because the majority of application requests require data that comes from a database. Therefore, developing efficient ADO.NET data access code is critical.

This chapter has provided a brief overview of ADO.NET architecture and has highlighted the main performance and scalability issues that you need to be aware of when you develop data access code. By following the design and implementation guidelines in this chapter, you will greatly increase your chances of building data access code that enables your application to meet its performance objectives.

Eric's Zone...

SqlDataReader和DataSet的概述

Performance

Retrieving Multiple Rows

Data Binding

关于ConnectionString的理解总结

Managing Database Connections

Managing Database Connections

Using Connection Pooling

Configuring SQL Server .NET Data Provider Connection Pooling

Storing Connection Strings

Using XML Application Configuration Files

Advantages

Disadvantages

More Information

Connection Usage Patterns

打包中把.net framework一起打包的方法

Creating the Settings.ini file

Customize strings for Setup.exe dialog boxes

第四章 ARP地址解析协议

第三章 IP：网际协议

第二章 链路层

1－2 分层

Web项目的包括数据库的打包方法

ADO.net性能改进

Improving .NET Application Performance and Scalability

Contents

Objectives

Overview

How to Use This Chapter

Architecture

Abstracting Data Access

More Information

Performance and Scalability Issues

Design Considerations

Design Your Data Access Layer Based on How the Data Is Used

Cache Data to Avoid Unnecessary Work

Connect by Using Service Accounts

Acquire Late, Release Early

Close Disposable Resources

Reduce Round Trips

Implicit Round Trips

Return Only the Data You Need

Use Windows Authentication

Choose the Appropriate Transaction Type

Use Stored Procedures

More Information

Prioritize Performance, Maintainability, and Productivity when You Choose How to Pass Data Across Layers

Consider How to Handle Exceptions

Use Appropriate Normalization

Implementation Considerations

.NET Framework Data Providers

Use System.Data.SqlClient for SQL Server 7.0 and Later

Use System.Data.OleDb for SQL Server 6.5 or OLE DB Providers

Use System.Data.ODBC for ODBC Data Sources

Use System.Data.OracleClient for Oracle

Use SQLXML Managed Classes for XML Data and SQL Server 2000

More Information

Connections

Open and Close the Connection in the Method

Explicitly Close Connections

Closing Connections in Visual Basic .NET

Closing Connections in C#

Closing Connections with the Using Statement in C#

When Using DataReaders, Specify CommandBehavior.CloseConnection

Do Not Explicitly Open a Connection if You Use Fill or Update for a Single Operation

Avoid Checking the State Property of OleDbConnection

Pool Connections

Monitoring Pooling

More Information

Commands

Validate SQL Input and Use Parameter Objects

Using Parameters with Stored Procedures

Using Parameters with Dynamic SQL

Retrieve Only the Columns and Rows You Need

Support Paging Over Large Result Sets

Batch SQL Statements to Reduce Round Trips

More Information

Use ExecuteNonQuery for Commands That Do Not Return Data

Use ExecuteScalar to Return Single Values

第二章链路层