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
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
Avoid using the
Fill method which takes the
maxRecord as parameters. This will fill a
DataSet with only records between
maxRecord though it will fetch the full set of data from the database every time the method gets called.
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 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
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
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.
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.
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.