.NET Data Access Architecture Guide

.NET Data Access Architecture Guide

Building Distributed Applications

.NET Data Access Architecture Guide

Introduction

If you are designing a data access layer for a .NET-based application, you should use Microsoft® ADO.NET as the data access model. ADO.NET is feature rich and supports the data access requirements of loosely coupled, multitier Web applications and Web services. As with other feature-rich object models, ADO.NET offers a number of ways to solve a particular problem.

The .NET Data Access Architecture Guide provides information to help you choose the most appropriate data access approach. It does this by describing a wide range of common data access scenarios, providing performance tips, and prescribing best practices. This guide also provides answers to frequently asked questions, such as: Where is the best place to store database connection strings? How should I implement connection pooling? How should I work with transactions? How should I implement paging to allow users to scroll through large numbers of records?

This guide focuses on the use of ADO.NET to access Microsoft SQL Server™ 2000 by using the SQL Server .NET data provider, one of the two providers shipped with ADO.NET. Where appropriate, this guide highlights any differences that you need to be aware of when you use the OLE DB .NET data provider to access other OLE DB–aware data sources.

For a concrete implementation of a data access component developed using the guidelines and best practices discussed in this document, see the Data Access Application Block. The Data Access Application Block includes the source code for the implementation, and you can use that code directly in your .NET-based applications.

The .NET Data Access Architecture Guide includes the following sections:

Introducing ADO.NET
Managing Database Connections
Error Handling
Performance
Connecting Through Firewalls
Handling BLOBs
Performing Database Updates with DataSets
Using Strongly Typed DataSet Objects
Working with Null Data Fields
Transactions
Data Paging

Who Should Read This Document

This document provides guidelines for application architects and enterprise developers who want to build .NET-based applications. Read this document if you are responsible for designing and developing the data tier of a multitier .NET-based application.

What You Must Know

To use this guide to build .NET-based applications, you must have experience developing data access code using ActiveX® Data Objects (ADO) and/or OLE DB, as well as SQL Server experience. You must understand how to develop managed code for the .NET platform, and you must be aware of the fundamental changes that the ADO.NET data access model introduces. For more information about .NET development, see http://msdn.microsoft.com/net.

What's New

This document has been updated to include sections on performing database updates, using typed DataSets, and using null data fields.

As indicated in the text, some of the content in this guide applies specifically to the Microsoft Visual Studio® 2003 development system and the .NET Framework SDK version 1.1.

Download the .NET Data Access Architecture Guide

Click to download the .NET Data Access Architecture Guide from the MS.com Download Center

Introducing ADO.NET

ADO.NET is the data access model for .NET-based applications. It can be used to access relational database systems such as SQL Server 2000, Oracle, and many other data sources for which there is an OLE DB or ODBC provider. To a certain extent, ADO.NET represents the latest evolution of ADO technology. However, ADO.NET introduces some major changes and innovations that are aimed at the loosely coupled—and inherently disconnected—nature of Web applications. For a comparison of ADO and ADO.NET, see the MSDN article "ADO.NET for the ADO Programmer," at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/adonetprogmsdn.asp.

One of the key changes that ADO.NET introduces is the replacement of the ADO Recordset object with a combination of the DataTable, DataSet, DataAdapter, and DataReader objects. A DataTable represents a collection of rows from a single table, and in this respect is similar to the Recordset. A DataSet represents a collection of DataTable objects, together with the relationships and constraints that bind the various tables together. In effect, the DataSet is an in-memory relational structure with built-in XML support.

One of the key characteristics of the DataSet is that it has no knowledge of the underlying data source that might have been used to populate it. It is a disconnected, stand-alone entity used to represent a collection of data, and it can be passed from component to component through the various layers of a multitier application. It can also be serialized as an XML data stream, which makes it ideally suited for data transfer between heterogeneous platforms. ADO.NET uses the DataAdapter object to channel data to and from the DataSet and the underlying data source. The DataAdapter object also provides enhanced batch update features previously associated with the Recordset.

Figure 1 shows the full DataSet object model.

Figure 1.1. DataSet object model

.NET Data Providers

ADO.NET relies on the services of .NET data providers. These provide access to the underlying data source, and they comprise four key objects (Connection, Command, DataReader, and DataAdapter).

Currently, ADO.NET ships with two categories of providers: bridge providers and native providers. Bridge providers, such as those supplied for OLE DB and ODBC, allow you to use data libraries designed for earlier data access technologies. Native providers, such as the SQL Server and Oracle providers, typically offer performance improvements due, in part, to the fact that there is one less layer of abstraction.

The SQL Server .NET Data Provider. This is a provider for Microsoft SQL Server 7.0 and later databases. It is optimized for accessing SQL Server, and it communicates directly with SQL Server by using the native data transfer protocol of SQL Server.
Always use this provider when you connect to SQL Server 7.0 or SQL Server 2000.
The Oracle .NET Data Provider. The .NET Framework Data Provider for Oracle 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.
The OLE DB .NET Data Provider. This is a managed provider for OLE DB data sources. It is slightly less efficient than the SQL Server .NET Data Provider, because it calls through the OLE DB layer when communicating with the database. Note that this provider does not support the OLE DB provider for Open Database Connectivity (ODBC), MSDASQL. For ODBC data sources, use the ODBC .NET Data Provider (described later) instead. For a list of OLE DB providers that are compatible with ADO.NET, see http://msdn.microsoft.com/library/en-us/cpguidnf/html/cpconadonetproviders.asp.

Other .NET data providers currently in beta testing include:

The ODBC .NET Data Provider. The .NET Framework Data Provider for ODBC uses native ODBC Driver Manager (DM) to enable data access by means of COM interoperability.
A managed provider for retrieving XML from SQL Server 2000. The XML for SQL Server Web update 2 (currently in beta) includes a managed provider specifically for retrieving XML from SQL Server 2000. For more information about this update, see http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001300.

For a more detailed overview of the different data providers, see ".NET Framework Data Providers" in the.NET Framework Developer's Guide, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetproviders.asp.

Namespace Organization

The types (classes, structs, enums, and so on) associated with each .NET data provider are located in their own namespaces:

System.Data.SqlClient. Contains the SQL Server .NET Data Provider types.
System.Data.OracleClient. Contains the Oracle .NET Data Provider
System.Data.OleDb. Contains the OLE DB .NET Data Provider types.
System.Data.Odbc. Contains the ODBC .NET Data Provider types.
System.Data. Contains provider-independent types such as the DataSet and DataTable.

Within its associated namespace, each provider provides an implementation of the Connection, Command, DataReader, and DataAdapter objects. The SqlClient implementations are prefixed with "Sql" and the OleDb implementations are prefixed with "OleDb." For example, the SqlClient implementation of the Connection object is SqlConnection, and the OleDb equivalent is OleDbConnection. Similarly, the two incarnations of the DataAdapter object are SqlDataAdapter and OleDbDataAdapter, respectively.

In this guide, the examples are drawn from the SQL Server object model. Although not illustrated here, similar features are available in Oracle/OLEDB and ODBC.

Generic Programming

If you are likely to target different data sources and want to move your code from one to the other, consider programming to the IDbConnection, IDbCommand, IDataReader, and IDbDataAdapter interfaces located within the System.Data namespace. All implementations of the Connection, Command, DataReader, and DataAdapter objects must support these interfaces.

For more information about implementing .NET data providers, see http://msdn.microsoft.com/library/en-us/cpguidnf/html/cpconimplementingnetdataprovider.asp.

It should also be noted that both the OLE DB and ODBC bridging providers are alternatives if an application uses a single object model to access multiple databases. In this situation, it is important to consider the application's need for flexibility, and the extent to which database-specific functionality is required, in comparison with the application's need for performance.

Figure 2 illustrates the data access stack and how ADO.NET relates to other data access technologies, including ADO and OLE DB. It also shows the two managed providers and the principal objects within the ADO.NET model.

Figure 1.2. Data access stack

For more information about the evolution of ADO to ADO.NET, see the article "Introducing ADO+: Data Access Services for the Microsoft .NET Framework" in the November 2000 issue of MSDN Magazine, at http://msdn.microsoft.com/msdnmag/issues/1100/adoplus/default.aspx.

Stored Procedures vs. Direct SQL

Most code fragments shown in this document use SqlCommand objects to call stored procedures to perform database manipulation. In some cases, you will not see the SqlCommand object because the stored procedure name is passed directly to a SqlDataAdapter object. Internally, this still results in the creation of a SqlCommand object.

You should use stored procedures instead of embedded SQL statements for a number of reasons:

Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.
Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.
Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

The SQL Server online documentation strongly recommends that you do not create any stored procedures using "sp_" as a name prefix because such names have been designated for system stored procedures. SQL Server always looks for stored procedures beginning with sp_ in this order:

Look for the stored procedure in the master database.
Look for the stored procedure based on any qualifiers provided (database name or owner).
Look for the stored procedure, using dbo as the owner if an owner is not specified.

Properties vs. Constructor Arguments

You can set specific property values of ADO.NET objects either through constructor arguments or by directly setting the properties. For example, the following code fragments are functionally equivalent.

// Use constructor arguments to configure command object
SqlCommand cmd = new SqlCommand( "SELECT * FROM PRODUCTS", conn );

// The above line is functionally equivalent to the following
// three lines which set properties explicitly
sqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM PRODUCTS";

From a performance perspective, there is negligible difference between the two approaches because setting and getting properties against .NET objects is more efficient than performing similar operations against COM objects.

The choice is one of personal preference and coding style. The explicit setting of properties does, however, make the code easier to comprehend (particularly if you are not familiar with the ADO.NET object model) and easier to debug.

Note In the past, developers of the Microsoft Visual Basic® development system were advised to avoid creating objects with the "Dim x As New…" construct. In the world of COM, this code could result in the short circuit of the COM object creation process, leading to some subtle and some not-so-subtle bugs. In the .NET world, however, this is no longer an issue.

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.

This section discusses connection pooling and will help you choose an appropriate connection pooling strategy. This section also considers how you should manage, store, and administer your database connection strings. Finally, this section presents two coding patterns that you can use to help ensure that connections are closed reliably and returned to the connection pool.

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.

Data access technologies such as ODBC and OLE DB provide forms of connection pooling, which are configurable to varying degrees. Both approaches are largely transparent to the database client application. OLE DB connection pooling is often referred to as session or resource pooling.

For a general discussion of pooling within Microsoft Data Access Components (MDAC), see "Pooling in the Microsoft Data Access Components," at http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp.

ADO.NET data providers provide transparent connection pooling, the exact mechanics of which vary for each provider. This section discusses connection pooling in relation to:

The SQL Server .NET Data Provider
The Oracle .NET Data Provider
The OLE DB .NET Data Provider
The ODBC .NET Data Provider

Pooling with the SQL Server .NET Data Provider

If you are using the SQL Server .NET Data Provider, use the connection pooling support offered by the provider. It is a transaction-aware and efficient mechanism implemented internally by the provider, within managed code. Pools are created on a per application domain basis, and pools are not destroyed until the application domain is unloaded.

You can use this form of connection pooling transparently, but you should be aware of how pools are managed and of the various configuration options that you can use to fine-tune connection pooling.

In many cases, the default connection pooling settings for the SQL Server .NET data provider may be sufficient for your application. During the development and testing of your .NET-based application, it is recommended that you simulate projected traffic patterns to determine if modifications to the connection pool size are required.

Developers building scalable, high performance applications should minimize the amount of time a connection is used, keeping it open for only as long as it takes to retrieve or update data. When a connection is closed, it is returned to the connection pool and made available for reuse. In this case, the actual connection to the database is not severed; however, if connection pooling is disabled, the actual connection to the database will be closed.

Developers should be careful not to rely on the garbage collector to free connections because a connection is not necessarily closed when the reference goes out of scope. This a common source of connection leaks, resulting in connection exceptions when new connections are requested.

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.

Choosing Pool Sizes

Being able to establish a maximum threshold is very important for large-scale systems that manage the concurrent requests of many thousands of clients. You need to monitor connection pooling and the performance of your application to determine the optimum pool sizes for your system. The optimum size also depends on the hardware on which you are running SQL Server.

During development, you might want to reduce the default maximum pool size (currently 100) to help find connection leaks.

If you establish a minimum pool size, you will incur a small performance overhead when the pool is initially populated to bring it to that level, although the first few clients that connect will benefit. Note that the process of creating new connections is serialized, which means that your server will not be flooded with simultaneous requests when a pool is being initially populated.

For more details about monitoring connection pooling, see the Monitoring Connection Pooling section in this document. For a complete list of connection pooling connection string keywords, see "Connection Pooling for the .NET Framework Data Provider for SQL Server" in the.NET Framework Developer's Guide, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp.

More Information

When using SQL Server .NET Data Provider connection pooling, be aware of the following:

Connections are pooled through an exact match algorithm on the connection string. The pooling mechanism is even sensitive to spaces between name-value pairs. For example, the following two connection strings will result in two separate pools because the second contains an extra space character.

SqlConnection conn = new SqlConnection(
         "Integrated Security=SSPI;Database=Northwind");
conn.Open(); // Pool A is created

SqlConmection conn = new SqlConnection(
         "Integrated Security=SSPI ; Database=Northwind");
conn.Open(); // Pool B is created (extra spaces in string)

The connection pool is divided into multiple transaction-specific pools and one pool for connections not currently enlisted in a transaction. For threads associated with a particular transaction context, a connection from the appropriate pool (containing connections enlisted with that transaction) is returned. This makes working with enlisted connections a transparent process.

Pooling with the OLE DB .NET Data Provider

The OLE DB .NET Data Provider pools connections by using the underlying services of OLE DB resource pooling. You have a number of options for configuring resource pooling:

You can use the connection string to configure, enable, or disable resource pooling.
You can use the registry.
You can programmatically configure resource pooling.

To circumvent registry-related deployment issues, avoid using the registry to configure OLE DB resource pooling.

For more details about OLE DB resource pooling, see "Resource Pooling" in Chapter 19, "OLE DB Services" of the OLE DB Programmer's Reference, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbabout_the_ole_db_documentation.asp.

Managing Connection Pooling with Pooled Objects

As Windows DNA developers, you were encouraged to disable OLE DB resource pooling and/or ODBC connection pooling and use COM+ object pooling as a technique to pool database connections. There are two primary reasons for this:

Pool sizes and thresholds can be explicitly configured (in the COM+ catalog).
Performance is improved. The pooled object approach can outperform native pooling by a factor of two.

However, because the SQL Server .NET Data Provider uses pooling internally, you no longer need to develop your own object pooling mechanism (when using this provider). You can thus avoid the complexities associated with manual transaction enlistment.

You might want to consider COM+ object pooling if you are using the OLE DB .NET Data Provider to benefit from superior configuration and improved performance. If you develop a pooled object for this purpose, you must disable OLE DB resource pooling and automatic transaction enlistment (for example, by including "OLE DB Services=-4" in the connection string). You must handle transaction enlistment within your pooled object implementation.

Monitoring Connection Pooling

To monitor your application's use of connection pooling, you can use the Profiler tool that ships with SQL Server, or the Performance Monitor tool that ships with the Microsoft Windows® 2000 operating system.

To monitor connection pooling with SQL Server Profiler

Click Start, point to Programs, point to Microsoft SQL Server, and then click Profiler to start Profiler.
On the File menu, point to New, and then click Trace.
Supply connection details, and then click OK.
In the Trace Properties dialog box, click the Events tab.
In the Selected event classes list, ensure that the Audit Login and Audit Logout events are shown beneath Security Audit. To make the trace clearer, remove all other events from the list.
Click Run to start the trace. You will see Audit Login events when connections are established and Audit Logout events when connections are closed.

To monitor connection pooling with Performance Monitor

Click Start, point to Programs, point to Administrative Tools, and then click Performance to start Performance Monitor.
Right-click the graph background, and then click Add Counters.
In the Performance object drop-down list, click SQL Server: General Statistics.
In the list that appears, click User Connections.
Click Add, and then click Close.

Managing Security

Although database connection pooling improves the overall scalability of your application, it means you can no longer manage security at the database. This is because to support connection pooling, the connection strings must be identical. If you need to track database operations on a per user basis, consider adding a parameter through which you can pass the user identity and manually log user actions in the database. You need to add this parameter to each operation.

Using Windows Authentication

You should use Windows authentication when connecting to SQL Server because it provides a number of benefits:

Security is easier to manage because you work with a single (Windows) security model rather than the separate SQL Server security model.
You avoid embedding user names and passwords in connection strings.
User names and passwords are not passed over the network in clear text.
Logon security improves through password expiration periods, minimum lengths, and account lockout after multiple invalid logon requests.

More Information

When you use Windows authentication to access SQL Server, use the following guidelines:

Consider performance tradeoffs. Performance tests have shown that it takes longer to open a pooled database connection when using Windows authentication as compared to using SQL Server authentication. The .NET runtime version 1.1 has reduced the margin by which SQL Server security outperforms Windows authentication, but SQL Server authentication is still faster.
However, although Windows authentication is still more expensive, the performance reduction is relatively insignificant in comparison to the time it takes to execute a command or stored procedure. As a result, in most cases the security benefits of using Windows authentication outweigh this slight performance degradation. Before making a decision, assess the performance requirements of your application.
Avoid impersonation in the middle tier. Windows authentication requires a Windows account for database access. Although it might seem logical to use impersonation in the middle tier, avoid doing so because it defeats connection pooling and has a severe impact on application scalability.
To address this problem, consider impersonating a limited number of Windows accounts (rather than the authenticated principal) with each account representing a particular role.
For example, you can use this approach:
1. Create two Windows accounts, one for read operations and one for write operations. (Or, you might want separate accounts to mirror application-specific roles. For example, you might want to use one account for Internet users and another for internal operators and/or administrators.)
2. Map each account to a SQL Server database role, and establish the necessary database permissions for each role.
3. Use application logic in your data access layer to determine which Windows account to impersonate before you perform a database operation.
Note Each account must be a domain account with Internet Information Services (IIS) and SQL Server in the same domain or in trusted domains. Or, you can create matching accounts (with the same name and password) on each computer.
Use TCP/IP for your network library. SQL Server 7.0 and later support Windows authentication for all network libraries. Use TCP/IP to gain configuration, performance, and scalability benefits. For more information about using TCP/IP, see the Connecting Through Firewalls section in this document.

For general guidance on developing secure ASP.NET and Web applications, refer to the following Microsoft patterns & practices guides:

Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication, available at http://www.microsoft.com/practices
Volume II, Improving Web Application Security: Threats and Countermeasures, which will be available at http://www.microsoft.com/practices

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.

When you are choosing an approach for connection string storage, the two most important considerations are security and ease of configuration, closely followed by performance.

You can choose among the following locations for storing database connection strings:

In an application configuration file; for example, Web.config for an ASP.NET Web application
In a Universal Data Link (UDL) file (supported only by the OLE DB .NET Data Provider)
In the Windows registry
In a custom file
In the COM+ catalog, by using construction strings (for serviced components only)

By using Windows authentication to access SQL Server, you can avoid storing user names and passwords in connection strings. If your security requirements demand more stringent measures, consider storing the connection strings in encrypted format.

For ASP.NET Web applications, storing the connection strings in encrypted format within the Web.config file represents a secure and configurable solution.

Note You can set the Persist Security Info named value to false in the connection string to prevent security-sensitive details, such as the password, from being returned by means of the ConnectionString property of the SqlConnection or OleDbConnection objects.

The following subsections discuss how to use the various options to store connection strings, and they present the relative advantages and disadvantages of each approach. This will allow you to make an informed choice based on your specific application scenario.

Note The Configuration Application Management block allows you to manage configuration settings—from database connections to complex hierarchical data. For more information, see http://msdn.microsoft.com/practices.

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"];
}
  
```
For more information about configuring .NET Framework applications, see http://msdn.microsoft.com/library/en-us/cpguidnf/html/cpconconfiguringnetframeworkapplications.asp.

Using UDL Files

The OLE DB .NET Data Provider supports Universal Data Link (UDL) file names in its connection string. You can pass the connection string by using construction arguments to the OleDbConnection object, or you can set the connection string by using the object's ConnectionString property.

Note The SQL Server .NET Data Provider does not support UDL files in its connection string. Therefore, this approach is available to you only if you are using the OLE DB .NET Data Provider.

For the OLE DB provider, to reference a UDL file with the connection string, use "File Name=name.udl."

Advantages

Standard approach. You might already be using UDL files for connection string management.

Disadvantages

Performance. Connection strings that contain UDLs are read and parsed each time the connection is opened.
Security. UDL files are stored as plain text. You can secure these files by using NTFS file permissions, but doing so raises the same issues as with .config files.
SqlClient does not support UDL files. This approach is not supported by the SQL Server .NET Data Provider, which you use to access SQL Server 7.0 and later.

More Information

To support administration, make sure that administrators have read/write access to the UDL file and that the identity used to run your application has read access. For ASP.NET Web applications, the application worker process runs by using the SYSTEM account by default, although you can override this by using the <processModel> element of the machine-wide configuration file (Machine.config). You can also impersonate, optionally with a nominated account, by using the <identity> element of the Web.config file.
For Web applications, make sure that you do not place the UDL file in a virtual directory, which would make the file downloadable over the Web.
For more information about these and other security-related ASP.NET features, see "Authentication in ASP.NET: .NET Security Guidance," at http://msdn.microsoft.com/library/en-us/dnbda/html/authaspdotnet.asp.

Using the Windows Registry

You can also use a custom key in the Windows registry to store the connection string, although this is not recommended due to deployment issues.

Advantages

Security. You can manage access to selected registry keys by using access control lists (ACLs). For even higher levels of security, consider encrypting the data.
Ease of programmatic access. .NET classes are available to support reading strings from the registry.

Disadvantages

Deployment. The relevant registry setting must be deployed along with your application, somewhat defeating the advantage of xcopy deployment.

Using a Custom File

You can use a custom file to store the connection string. However, this technique offers no advantages and is not recommended.

Advantages

None.

Disadvantages

Extra coding. This approach requires extra coding and forces you to deal explicitly with concurrency issues.
Deployment. The file must be copied along with the other ASP.NET application files. Avoid placing the file in the ASP.NET application directory or subdirectory to prevent it from being downloaded over the Web.

Using Construction Arguments and the COM+ Catalog

You can store the database connection string in the COM+ catalog and have it automatically passed to your object by means of an object construction string. COM+ will call the object's Construct method immediately after instantiating the object, supplying the configured construction string.

Note This approach works only for serviced components. Consider it only if your managed components use other services, such as distributed transaction support or object pooling.

Advantages

Administration. An administrator can easily configure the connection string by using the Component Services MMC snap-in.

Disadvantages

Security. The COM+ catalog is considered a non-secure storage area (although you can restrict access with COM+ roles) and therefore must not be used to maintain connection strings in clear text.
Deployment. Entries in the COM+ catalog must be deployed along with your .NET-based application. If you are using other enterprise services, such as distributed transactions or object pooling, storing the database connection string in the catalog presents no additional deployment overhead, because the COM+ catalog must be deployed to support those other services.
Components must be serviced. You can use construction strings only for serviced components. You should not derive your component's class from ServicedComponent (making your component serviced) simply to enable construction strings.

Important It is critical to secure connection strings. With SQL authentication, the connection contains a user name and password. If an attacker exploits a source code vulnerability on the Web server or gains access to the configuration store, the database will be vulnerable. To prevent this, connection strings should be encrypted. For descriptions of different methods available to encrypt plaintext connection strings, see Improving Web Application Security: Threats and Countermeasures, which will be available at http://www.microsoft.com/practices.

More Information

For more information about how to configure a .NET class for object construction, see How To Enable Object Construction For a .NET Class in the appendix.
For more information about developing serviced components, see http://msdn.microsoft.com/library/en-us/cpguidnf/html/cpconwritingservicedcomponents.asp.
For general guidance on developing secure ASP.NET and Web applications, refer to the following Microsoft patterns & practices guides:
- Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication, available at http://www.microsoft.com/practices
- Volume II, Improving Web Application Security: Threats and Countermeasures, which will be available at http://www.microsoft.com/practices

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();
  }
}

You can also apply this approach to other objects—for example, SqlDataReader or OleDbDataReader—which must be closed before anything else can be done with the current connection.

Error Handling

ADO.NET errors are generated and handled through the underlying structured exception handling support that is native to the .NET Framework. As a result, you handle errors within your data access code in the same way that you handle errors elsewhere in your application. Exceptions can be detected and handled through standard .NET exception handling syntax and techniques.

This section shows you how to develop robust data access code and explains how to handle data access errors. It also provides specific exception handling guidance relating to the SQL Server .NET Data Provider.

.NET Exceptions

The .NET data providers translate database-specific error conditions into standard exception types, which you should handle in your data access code. The database-specific error details are made available to you through properties of the relevant exception object.

All .NET exception types ultimately are derived from the base Exception class in the System namespace. The .NET data providers throw provider-specific exception types. For example, the SQL Server .NET Data Provider throws SqlException objects whenever SQL Server returns an error condition. Similarly, the OLE DB .NET Data Provider throws exceptions of type OleDbException, which contain details exposed by the underlying OLE DB provider.

Figure 3 shows the .NET data provider exception hierarchy. Notice that the OleDbException class is derived from ExternalException, the base class for all COM Interop exceptions. The ErrorCode property of this object stores the COM HRESULT generated by OLE DB.

Figure 1.3. .NET Data Provider exception hierarchy

Catching and Handling .NET Exceptions

To handle data access exception conditions, place your data access code within a try block and trap any exceptions generated by using catch blocks with the appropriate filter. For example, when writing data access code by using the SQL Server .NET Data Provider, you should catch exceptions of type SqlException, as shown in the following code:

try
{
  // Data access code
}
catch (SqlException sqlex) // more specific
{
}
catch (Exception ex) // less specific
{
}

If you provide more than one catch statement with differing filter criteria, remember to order them from most specific type to least specific type. That way, the most specific type of catch block is executed for any given exception type.

This SqlException class exposes properties that contain details of the exception condition. These include:

A Message property that contains text describing the error.
A Number property that contains the error number, which uniquely identifies the type of error.
A State property that contains additional information about the invocation state of the error. This is usually used to indicate a particular occurrence of a specific error condition. For example, if a single stored procedure can generate the same error from more than one line, the state should be used to identify the specific occurrence.
An Errors collection, which contains detailed error information about the errors that SQL Server generates. The Errors collection will always contain at least one object of type SqlError.

The following code fragment illustrates how to handle a SQL Server error condition by using the SQL Server .NET Data Provider:

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

// Method exposed by a Data Access Layer (DAL) Component
public string GetProductName( int ProductID )
{
  SqlConnection conn = null;
  // Enclose all data access code within a try block
  try
  {
    conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=northwind");
    conn.Open();
    SqlCommand cmd = new SqlCommand("LookupProductName", conn );
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@ProductID", ProductID );
    SqlParameter paramPN = 
         cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
    paramPN.Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();
    // The finally code is executed before the method returns
    return paramPN.Value.ToString();  
  }
  catch (SqlException sqlex)
  {
    // Handle data access exception condition
    // Log specific exception details
    LogException(sqlex);
    // Wrap the current exception in a more relevant
    // outer exception and re-throw the new exception
    throw new DALException(
                  "Unknown ProductID: " + ProductID.ToString(), sqlex );
  }
  catch (Exception ex)
  {
    // Handle generic exception condition . . .
    throw ex;
  }
  finally
  {
    if(conn != null) conn.Close(); // Ensures connection is closed
  }
}

// Helper routine that logs SqlException details to the 
// Application event log
private void LogException( SqlException sqlex )
{
  EventLog el = new EventLog();
  el.Source = "CustomAppLog";
  string strMessage;
  strMessage = "Exception Number : " + sqlex.Number + 
               "(" + sqlex.Message + ") has occurred";
  el.WriteEntry( strMessage );

  foreach (SqlError sqle in sqlex.Errors)
  {
    strMessage = "Message: " + sqle.Message +
                 " Number: " + sqle.Number +
                 " Procedure: " + sqle.Procedure +
                 " Server: " + sqle.Server +
                 " Source: " + sqle.Source +
                 " State: " + sqle.State +
                 " Severity: " + sqle.Class +
                 " LineNumber: " + sqle.LineNumber;
    el.WriteEntry( strMessage );
  }
}

Within the SqlException catch block, the code initially logs the exception details by using the LogException helper function. This function uses a foreach statement to enumerate the provider-specific details within the Errors collection and records the error details to the error log. The code within the catch block then wraps the SQL Server-specific exception within an exception of type DALException, which is more meaningful to the callers of the GetProductName method. The exception handler uses the throw keyword to propagate this exception back to the caller.

More Information

For a full list of members of the SqlException class, see http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlExceptionMembersTopic.asp.
For further guidance about developing custom exceptions, logging and wrapping .NET exceptions, and using various approaches to exception propagation, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/exceptdotnet.asp.

Generating Errors from Stored Procedures

Transact-SQL (T-SQL) provides a RAISERROR (note the spelling) function, which you can use to generate custom errors and return them to the client. For ADO.NET clients, the SQL Server .NET Data Provider intercepts these database errors and translates them to SqlError objects.

The simplest way to use the RAISERROR function is to include the message text as the first parameter, and then specify severity and state parameters, as shown in the following code fragment.

RAISERROR( 'Unknown Product ID: %s', 16, 1, @ProductID )

In this example, a substitution parameter is used to return the current product ID as part of the error message text. Parameter two is the message severity, and parameter three is the message state.

More Information

To avoid hard coding message text, you can add your own message to the sysmessages table by using the sp_addmessage system stored procedure, or by using the SQL Server Enterprise Manager. You can then reference the message by using an ID passed to the RAISERROR function. The message IDs that you define must be greater than 50,000, as shown in the following code fragment.
```
RAISERROR( 50001, 16, 1, @ProductID )
  
```
For full details relating to the RAISERROR function, look up RAISERROR in the SQL Server Books Online index.

Using Severity Levels Appropriately

Choose your error severity levels carefully and be aware of the impact of each level. Error severity levels range from 0 to 25 and are used to indicate the type of problem that SQL Server 2000 has encountered. In client code, you can obtain an error's severity by examining the Class property of the SqlError object, within the Errors collection of the SqlException class. Table 1 indicates the impact and meaning of the various severity levels.

Table 1. Error Severity Levels–Impact and Meaning

Severity level	Connection is closed	Generates SqlException	Meaning
10 and below	No	No	Informational messages that do not necessarily represent error conditions.
11–16	No	Yes	Errors that can be corrected by the user—for example, by retrying the operation with amended input data.
17–19	No	Yes	Resource or system errors.
20–25	Yes	Yes	Fatal system errors (including hardware errors). Client's connection is terminated.

Controlling Automatic Transactions

The SQL Server .NET Data Provider throws a SqlException for any error encountered with a severity greater than 10. When a component that is part of an automatic (COM+) transaction detects a SqlException, the component must ensure that it votes to abort the transaction. This might or might not be an automatic process, and depends on whether or not the method is marked with the AutoComplete attribute.

For more information about handling SqlExceptions in the context of automatic transactions, see the Determining Transaction Outcome section in this document.

Retrieving Informational Messages

Severity levels of 10 and lower are used to represent informational messages and do not cause a SqlException to be raised.

To retrieve informational messages:

Create an event handler and subscribe to the InfoMessage event exposed by the SqlConnection object. This event's delegate is shown in the following code fragment.
```
public delegate void SqlInfoMessageEventHandler( object sender, 
                                                     SqlInfoMessageEventArgs e );
  
```

Message data is available through the SqlInfoMessageEventArgs object passed to your event handler. This object exposes an Errors property, which contains a set of SqlError objects—one per informational message. The following code fragment illustrates how to register an event handler that is used to log informational messages.

public string GetProductName( int ProductID )
{
  SqlConnection conn = null;
  try
  {
    conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=northwind");
    // Register a message event handler
    conn.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler );
    conn.Open();
    // Setup command object and execute it
    . . .
  }
  catch (SqlException sqlex)
  {
    // log and handle exception
    . . .
  }
  finally
  {
    if(conn != null) conn.Close();
  }
}
// message event handler
void MessageEventHandler( object sender, SqlInfoMessageEventArgs e )
{
  foreach( SqlError sqle in e.Errors )
  {
    // Log SqlError properties
    . . .
  }
}

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. Figure 4 shows the two basic scenarios.

Note The SqlDataAdapter used to populate a DataSet internally uses a SqlDataReader to access the data.

Figure 1.4. Multiple row data access scenarios

Comparing the Options

You have the following options when you retrieve multiple rows from a data source:

Use a SqlDataAdapter object to generate a DataSet or DataTable.
Use a SqlDataReader to provide a read-only, forward-only data stream.
Use an XmlReader to provide a read-only, forward-only data stream of XML data.

The choice between SqlDataReader and DataSet/DataTable is essentially one of performance versus functionality. The SqlDataReader offers optimum performance; the DataSet provides additional functionality and flexibility.

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.

Check the data source requirements for the particular control type that you want to bind to.

Passing Data Between Application Tiers

The DataSet provides a relational view of the data that can optionally be manipulated as XML, and allows a disconnected cached copy of the data to be passed between application tiers and components. The SqlDataReader, however, offers optimum performance because it avoids the performance and memory overhead associated with the creation of the DataSet. Remember that the creation of a DataSet object can result in the creation of multiple sub-objects—including DataTable, DataRow, and DataColumn objects—and the collection objects used as containers for these sub-objects.

Using a DataSet

Use a DataSet populated by a SqlDataAdapter object when:

You require a disconnected memory-resident cache of data, so that you can pass it to another component or tier within your application.
You require an in-memory relational view of the data for XML or non-XML manipulation.
You are working with data retrieved from multiple data sources, such as multiple databases, tables, or files.
You want to update some or all of the retrieved rows and use the batch update facilities of the SqlDataAdapter.
You want to perform data binding against a control that requires a data source that supports IList.

Note For detailed information, see "Designing Data Tier Components and Passing Data Through Tiers" on the MSDN Web site at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp.

More Information

If you use a SqlDataAdapter to generate a DataSet or DataTable, note the following:

You do not need to explicitly open or close the database connection. The SqlDataAdapter Fill method opens the database connection and then closes the connection before it returns. If the connection is already open, Fill leaves the connection open.
If you require the connection for other purposes, consider opening it prior to calling the Fill method. You can thus avoid unnecessary open/close operations and gain a performance benefit.
Although you can repeatedly use the same SqlCommand object to execute the same command multiple times, do not reuse the same SqlCommand object to execute different commands.
For a code sample that shows how to use a SqlDataAdapter to populate a DataSet or DataTable, see How to Use a SqlDataAdapter to Retrieve Multiple Rows in the appendix.

Using a SqlDataReader

Use a SqlDataReader obtained by calling the ExecuteReader method of the SqlCommand object when:

You are dealing with large volumes of data—too much to maintain in a single cache.
You want to reduce the memory footprint of your application.
You want to avoid the object creation overhead associated with the DataSet.
You want to perform data binding with a control that supports a data source that implements IEnumerable.
You wish to streamline and optimize your data access.
You are reading rows containing binary large object (BLOB) columns. You can use the SqlDataReader to pull BLOB data in manageable chunks from the database, instead of pulling all of it at once. For more details about handling BLOB data, see the Handling BLOBs section in this document.

More Information

If you use the SqlDataReader, note the following:

The underlying connection to the database remains open and cannot be used for any other purpose while the data reader is active. Call Close on the SqlDataReader as soon as possible.
There can be only one data reader per connection.
You can close the connection explicitly when you finish with the data reader, or tie the lifetime of the connection to the SqlDataReader object, by passing the CommandBehavior.CloseConnection enumerated value to the ExecuteReader method. This indicates that the connection should be closed when the SqlDataReader is closed.
When accessing data by using the reader, use the typed accessor methods (such as GetInt32 and GetString) if you know the column's underlying data type because they reduce the amount of type conversion required when you read column data.
To avoid unnecessary data being pulled from server to client, if you want to close the reader and discard any remaining results, call the command object's Cancel method before calling Close on the reader. Cancel ensures that the results are discarded on the server and are not pulled unnecessarily to the client. Conversely, calling Close on the data reader causes the reader to unnecessarily pull the remaining results to empty the data stream.
If you want to obtain output or return values returned from a stored procedure and you are using the ExecuteReader method of the SqlCommand object, you must call the Close method on the reader before the output and return values are available.
For a code sample that shows how to use a SqlDataReader, see How to Use a SqlDataReader to Retrieve Multiple Rows in the appendix.

Using an XmlReader

Use an XmlReader obtained by calling the ExecuteXmlReader method of the SqlCommand object when:

You want to process the retrieved data as XML, but you do not want to incur the performance overhead of creating a DataSet and do not require a disconnected cache of data.
You want to exploit the functionality of the SQL Server 2000 FOR XML clause, which allows XML fragments (that is, XML documents with no root element) to be retrieved from the database in a flexible manner. For example, this approach lets you specify precise element names, whether an element or attribute-centric schema should be used, whether a schema should be returned with the XML data and so on.

More Information

If you use the XmlReader, note the following:

The connection must remain open while you read data from the XmlReader. The ExecuteXmlReader method of the SqlCommand object currently does not support the CommandBehavior.CloseConnection enumerated value, so you must explicitly close the connection when you finish with the reader.
For a code sample that shows how to use an XmlReader, see How To Use an XmlReader to Retrieve Multiple Rows in the appendix.

Retrieving a Single Row

In this scenario, you want to retrieve a single row of data that contains a specified set of columns from a data source. For example, you have a customer ID and want to look up related customer details, or you have a product ID and want to retrieve product information.

Comparing the Options

If you want to perform data binding with a single row retrieved from a data source, you can use a SqlDataAdapter to populate a DataSet or DataTable in the same way that is described in the Multiple Row Retrieval and Iteration scenario discussed previously. However, unless you specifically require DataSet/DataTable functionality, you should avoid creating these objects.

If you need to retrieve a single row, use one of the following options:

Use stored procedure output parameters.
Use a SqlDataReader object.

Both options avoid the unnecessary overhead of creating a result set on the server and a DataSet on the client. The relative performance of each approach depends on stress levels and whether or not database connection pooling is enabled. When database connection pooling is enabled, performance tests have shown the stored procedure approach to outperform the SqlDataReader approach by nearly 30 percent under high-stress conditions (200+ simultaneous connections).

Using Stored Procedure Output Parameters

Use stored procedure output parameters when you want to retrieve a single row from a multitier Web application where you have enabled connection pooling.

More Information

For a code sample that shows how to use stored procedure output parameters, see How To Use Stored Procedure Output Parameters To Retrieve a Single Row in the appendix.

Using a SqlDataReader

Use a SqlDataReader when:

You require metadata in addition to data values. You can use the GetSchemaTable method of the data reader to obtain column metadata.
You are not using connection pooling. With connection pooling disabled, the SqlDataReader is a good option under all stress conditions; performance tests have shown it to outperform the stored procedure approach by around 20 percent at 200 browser connections.

More Information

If you use a SqlDataReader, note the following:

If you know your query only returns a single row, use the CommandBehavior.SingleRow enumerated value when calling the ExecuteReader method of the SqlCommand object. Some providers such as the OLE DB .NET Data Provider use this hint to optimize performance. For example, this provider performs binding by using the IRow interface (if it is available) rather than the more expensive IRowset. This argument has no effect on the SQL Server .NET Data Provider.
If your SQL Server command contains output parameters or return values, they will not be available until the DataReader is closed.
When using the SqlDataReader object, always retrieve output parameters through the typed accessor methods of the SqlDataReader object, for example GetString and GetDecimal. This avoids unnecessary type conversions.
.NET Framework version 1.1 includes an additional DataReader property called HasRows, which enables you to determine if the DataReader has returned any results before reading from it.
For a code sample that shows how to use a SqlDataReader object to retrieve a single row, see How To use a SqlDataReader to Retrieve a Single Row in the appendix.

Retrieving a Single Item

In this scenario, you want to retrieve a single item of data. For example, you might want to look up a single product name, given its ID, or a single customer credit rating, given the customer's name. In such scenarios, you will generally not want to incur the overhead of a DataSet or even a DataTable when retrieving a single item.

You might also want simply to check whether a particular row exists in the database. For example, as a new user registers on a Web site, you need to check whether or not the chosen user name already exists. This is a special case of the single item lookup, but in this case, a simple Boolean return is sufficient.

Comparing the Options

Consider the following options when you retrieve a single item of data from a data source:

Use the ExecuteScalar method of a SqlCommand object with a stored procedure.
Use a stored procedure output or return parameter.
Use a SqlDataReader object.

The ExecuteScalar method returns the data item directly because it is designed for queries that only return a single value. It requires less code than either the stored procedure output parameter or SqlDataReader approaches require..

From a performance perspective, you should use a stored procedure output or return parameter because tests have shown that the stored procedure approach offers consistent performance across low and high-stress conditions (from fewer than 100 simultaneous browser connections to 200 browser connections).

More Information

When retrieving a single item, be aware of the following:

If a query normally returns multiple columns and/or rows, executing it through ExecuteQuery will return only the first column of the first row.
For a code sample that shows how to use ExecuteScalar, see How To Use ExecuteScalar to Retrieve a Single Item in the appendix.
For a code sample that shows how to use a stored procedure output or return parameter to retrieve a single item, see How To Use a Stored Procedure Output or Return Parameter To Retrieve a Single Item in the appendix.
For a code sample that shows how to use a SqlDataReader object to retrieve a single item, see How To Use a SqlDataReader to Retrieve a Single Item in the appendix.

Connecting Through Firewalls

You will often want to configure Internet applications to connect to SQL Server through a firewall. For example, a key architectural component of many Web applications and their firewalls is the perimeter network (also known as DMZ or demilitarized zone), which is used to isolate front-end Web servers from internal networks.

Connecting to SQL Server through a firewall requires specific configuration of the firewall, client, and server. SQL Server provides the Client Network Utility and Server Network Utility programs to aid configuration.

Choosing a Network Library

Use the SQL Server TCP/IP network library to simplify configuration when connecting through a firewall. This is the SQL Server 2000 installation default. If you are using an earlier version of SQL Server, check that you have configured TCP/IP as the default network library on both the client and the server by using the Client Network Utility and Server Network Utility, respectively.

In addition to the configuration benefit, using the TCP/IP library results means that you:

Benefit from improved performance with high volumes of data and improved scalability.
Avoid additional security issues associated with named pipes.

You must configure the client and server computers for TCP/IP. Because most firewalls restrict the set of ports through which they allow traffic to flow, you must also give careful consideration to the port numbers that SQL Server uses.

Configuring the Server

Default instances of SQL Server listen on port 1433. UDP port 1434 is also used to allow SQL clients to locate other SQL servers on their network. Named instances of SQL Server 2000, however, dynamically assign a port number when they are first started. Your network administrator will not want to open a range of port numbers on the firewall; therefore, when you use a named instance of SQL Server with a firewall, use the Server Network Utility to configure the instance to listen on a specific port number. Your administrator can then configure the firewall to allow traffic to the specific IP address and port number that the server instance is listening on.

Note The source port that the client network library uses is dynamically assigned in the range 1024–5000. This is standard practice for TCP/IP client applications, but it means your firewall must allow traffic from any port within this range. For more information about the ports that SQL Server uses, see Microsoft Knowledge Base article 287932, "INF: TCP Ports Needed for Communication to SQL Server Through a Firewall."

Dynamic Discovery of Named Instances

If you change the default port number that SQL Server listens on, configure your client to connect to this port. For details, see the Configuring the Client section in this document.

If you change the port number for your default instance of SQL Server 2000, failure to modify the client will result in a connection error. If you have multiple SQL Server instances, the latest version of the MDAC data access stack (2.6) employs dynamic discovery and uses a User Datagram Protocol (UDP) negotiation (through UDP port 1434) to locate the named instances. Although this might work in a development environment, it is unlikely to work in a live environment because the firewall will typically block the UDP negotiation traffic.

To circumvent this, always configure your client to connect to the configured destination port number.

Configuring the Client

You should configure the client to use the TCP/IP network library to connect to SQL Server, and you should ensure that the client library uses the correct destination port number.

Using the TCP/IP Network Library

You can configure the client by using the SQL Server Client Network Utility. In some installations, you might not have this utility installed on the client (for example, your Web server). In this case, you can do either of the following:

Specify the network library by using the "Network Library=dbmssocn" name-value pair supplied through the connection string. The string "dbmssocn" is used to identify the TCP/IP (sockets) library.
Note When using the SQL Server .NET Data Provider, the network library setting uses "dbmssocn" by default.
Modify the registry on the client machine, to establish TCP/IP as the default library. For more information about configuring the SQL Server network library, see HOWTO: Change SQL Server Default Network Library Without Using Client Network Utility (Q250550).

Specifying a Port

If your instance of SQL Server is configured to listen on a port other than the default 1433, you can specify the port number to connect to by:

Using the Client Network Utility.
Specifying the port number with the "Server" or "Data Source" name-value pair supplied to the connection string. Use a string with the following format:
```
"Data Source=ServerName, PortNumber"
  
```
Note ServerName might be an IP address or Domain Name System (DNS) name. For optimum performance, use an IP address to avoid a DNS lookup.

Distributed Transactions

If you have developed serviced components that use COM+ distributed transactions and the services of the Microsoft Distributed Transaction Coordinator (DTC), you might also need to configure your firewall to allow DTC traffic to flow between separate DTC instances, and between the DTC and resource managers such as SQL Server.

For more information about opening ports for the DTC, see INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall.

Handling BLOBs

Today, many applications need to deal with data formats such as graphic and sound—or even more elaborate data formats, such as video—in addition to more conventional character and numeric data. There are many different types of graphic, sound, and video formats. From a storage perspective, however, they can all be thought of as lumps of binary data, typically referred to as binary large objects, or BLOBs.

SQL Server provides the binary, varbinary, and image data types to store BLOBs. Despite the name, BLOB data can also refer to text-based data. For example, you might want to store an arbitrarily long notes field that can be associated with a particular row. SQL Server provides the ntext and text data types for this purpose.

In general, for binary data less than 8 kilobytes (KB), use the varbinar

Product Families

Resources

About Microsoft

Popular Places

Popular Searches

Popular Downloads

Related Links

Introduction

Who Should Read This Document

What You Must Know

What's New

Download the .NET Data Access Architecture Guide

Introducing ADO.NET

.NET Data Providers

Namespace Organization

Generic Programming

Stored Procedures vs. Direct SQL

Properties vs. Constructor Arguments

Managing Database Connections

Using Connection Pooling

Pooling with the SQL Server .NET Data Provider

Configuring SQL Server .NET Data Provider Connection Pooling

Choosing Pool Sizes

More Information

Pooling with the OLE DB .NET Data Provider

Managing Connection Pooling with Pooled Objects

Monitoring Connection Pooling

Managing Security

Using Windows Authentication

More Information

Storing Connection Strings

Using XML Application Configuration Files

Advantages

Disadvantages

More Information

Using UDL Files

Advantages

Disadvantages

More Information

Using the Windows Registry

Advantages

Disadvantages

Using a Custom File

Advantages

Disadvantages

Using Construction Arguments and the COM+ Catalog

Advantages

Disadvantages

More Information

Connection Usage Patterns

Error Handling

.NET Exceptions

Catching and Handling .NET Exceptions

More Information

Generating Errors from Stored Procedures

More Information

Using Severity Levels Appropriately

Controlling Automatic Transactions

Retrieving Informational Messages

Performance

Retrieving Multiple Rows

Comparing the Options

Data Binding

Passing Data Between Application Tiers

Using a DataSet

More Information

Using a SqlDataReader

More Information

Using an XmlReader

More Information

Retrieving a Single Row

Comparing the Options

Using Stored Procedure Output Parameters

More Information

Using a SqlDataReader

More Information

Retrieving a Single Item

Comparing the Options

More Information

Connecting Through Firewalls