ADO.NET Best practices

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


1. Database Connection:

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

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

One should call the close method on connection object. Calling close method returns the connection to the connection pool. This connection from connection pool can be reutilized. One should not rely on garbage collector to destroy the connection object.

It is better to use “using” block of C# for connection. This automatically disposes the connection as connection object implements Idisposable interface.

2. Transaction:

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

3. Connection String:

It is recommended to store the connection string securely as it is very sensitive piece of information. Storing connection string in clear text is not recommended. The connection string should be encrypted.

One can store the connection string into registry. Storing connection into registry string could cause issues while deploying. Following command can be used to store the connection in registry.

aspnet_setreg -k “software\applicationData” -u:UserID -p:Password

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

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

4. DataReader & DataSet:

One should decide when to use data reader and when to use dataset.

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

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

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

When accessing values of the columns from database, one should use GetString/GEtInt32, etc. This reduces casing overhead and improves performance. Only one data reader can be opened on single associated connection.

The default behaviour of DataReader is to load entire row into memory for each Read. By this one can have random access to columns within current row. If you do not require random access, you can pass CommandBehaviour.SequentialAccess to ExecuteReader method. This will change the default behaviour of 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 the value of it. Passing SequentialAccess behaviour gives better performance.

5. Command Object:

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

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

While calling stored procedure, it is better to set CommandType to StoredProcedure. This will remove the need to parse the command.

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

Note: If you are returning output parameters form 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 Data Reader.

6. CommandBuilder:

CommandBuilder generates insert/update/delete commands for Data adapter based on select command. Automatic creation of insert/update/delete commands hinders performance. In case one knows the contents of insert/update/delete, should create those explicitly. Better to create explicit stored procedures for insert/update/delete and assign those.

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

CommandBuilder only generates a command for data adapter’s Command property if command property is null. By default command properties are null for 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 Command property.