ADO.NET Best Practices

Introduction

Here are a few tips which can be considered while working with ADO.NET. These may help in using ADO.NET effectively.

1. Database Connection

While working with a database, you should open a connection with the database as late as possible and should close it as early as possible.

It is always better to use connection pooling with databases. When applications use connection pooling, a connection once created goes back to the connection pool and can be reutilized by the system for another database call. The overhead for creation of connections will be bypassed by using connections from the pool. The connection pool size can be defined in the connection string. For the same connection string, ADO.NET uses the same connection pool.

You should always call the Close method on connection objects. Calling the Close method returns the connection to the connection pool. This connection from the connection pool can then be reutilized. You should not rely on the garbage collector to destroy connection objects.

It is better to use the "using" block of C# for connections. This automatically disposes a connection as connection objects implement the IDisposable interface.

2. Transaction

Most enterprise distributed applications use transactions. There are two kinds of transactions - manual and automatic. Transactions can span over one database or multiple databases. In ADO.NET, we can use a transaction by calling the BeginTransaction method on the connection object. The use of automatic transaction could be an overhead on performance.

3. Connection String

It is recommended to store a connection string securely as it is a very sensitive piece of information. Storing a connection string as clear text is not recommended. Connection strings should be encrypted.

We can store connection information in the Registry. Storing connection information in the Registry could cause issues while deploying. The following command can be used to store connection information in the Registry:

aspnet_setreg -k "software\applicationData" -u:UserID -p:Password 

It encrypts the user ID and password and stores them in the specified key.

We can keep an encrypted connection string in the web.config file too. For encrypting a connection string in web.config, the aspnet_regiis utility can be used. (Note: There are providers to encrypt/decrypt the connection string. You can create you own providers for encryption/decryption.)

4. DataReader & DataSet

You should decide when to use a DataReader and when to use a DataSet.

Avoid using the DataAdapter's Fill method which takes the startRecord and maxRecord as parameters. This will fill a DataSet with only records between startRecord and maxRecord though it will fetch the full set of data from the database every time the method gets called.

A DataReader should be closed before accessing any of the output parameters. You should close the DataReader after reading the data. If you pass CommandBehaviour.CloseConnection to the ExecuteReader method, it will close the associated connection when you close the DataReader.

The DataReader should not be accessed through layers. It is designed for connected data access.

When accessing values of columns from a database, you should use GetString/GetInt32, etc. This reduces casing overhead and improves performance. Only one DataReader can be opened on a single associated connection.

The default behaviour of the DataReader is to load the entire row into memory for each read. Thus we can have random access to columns within the current row. If you do not require random access, you can pass CommandBehaviour.SequentialAccess to the ExecuteReader method. This will change the default behaviour of the DataReader and will load data into memory only when requested. Using this behaviour, you need to access the columns in order, and once you have read a column, you can no longer read its value. Using SequentialAccess behaviour gives better performance.

5. Command Object

To have better performance of parameterized commands, you should use Command.Prepare. On calling the Prepare method, the data source optimizes the command for multiple calls.

Note: For SQL Server 2000, commands are implicitly optimized and so the Prepare method calling will not be doing anything extra. Though, the same Prepare command will be effective with data sources such as SQL Server 7.0.

While calling a Stored Procedure, it is better to set the CommandType to StoredProcedure. This will remove the need to parse the command.

You should call Cancel on a Command before calling Close on the DataReader in case you are closing before reading all the records. Calling Close causes the DataReader to retrieve pending results and empty the stream before closing the cursor. Calling Cancel on a Command discards the results on the server and so the DataReader does not have to read when it is closed.

Note: If you are returning output parameters form a command, calling Cancel will discard those too. So if you need any output parameters, do not call Cancel on the command; instead, call Close on the DataReader.

6. CommandBuilder

A CommandBuilder generates insert/update/delete commands for a Data Adapter based on the Select command. Automatic creation of insert/update/delete commands hinders performance. If you know the contents of insert/update/delete, you should create those explicitly. It is better to create explicit Stored Procedures for insert/update/delete and assign those.

The CommandBuilder uses the SelectComand property of the Data Adapter to determine values for other commands. If there is a change in the SelectCommand of the Data Adapter, remember to call RefreshScheme to update the command properties.

The CommandBuilder only generates a command for the Data Adapter's command property if the command property is null. By default, command properties are null for the Data Adapter. If you explicitly set a command property, the CommandBuilder does not overwrite it. You need to set the command property to null to allow CommandBuilder to generate a command for the command property.