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.