Technology Articles

Developing Data Access Components in Microsoft.NET

Martin Bell (extract from Microsoft article)

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.

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

• Adopt a configurable and high performance connection pooling strategy.
• Use Windows authentication when accessing SQL Server.
• Store connection strings securely.
• Open database connections late and close them early.

The ADO.NET data providers provide a largely transparent method for connection pooling in order to preserve resources used by the creation of database connections. 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.

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.

In addition to the ADO.NET framework raised exceptions, you can also generate your own database exceptions in stored procedures using the RAISERROR command.

Stored Procedures vs. Direct SQL

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.

Passing Data Between Application Tiers

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.

DataSet

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 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.

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.

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.

SqlDataReader

The SqlDataReader offers optimum performance because it avoids the performance and memory overhead associated with the creation of the DataSet. 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.

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.

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.

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.

Summary

The .NET implementation of ADO introduces an efficient, scalable and flexible object model for access to many types of data source.