databasedev.co.uk - database solutions and downloads for microsoft access

Best Practices of ADO.NET

This article takes a look at the best practices in using ADO.NET, managing transactions, using exceptions efficiently, managing connections and connection pooling efficiently, etc. It also discusses the strategies that can be adopted for optimizing the performance of applications using ADO.NET for data storage and retrieval.

Acquire connection as late as possible, and, close them early. The reason is that when connections are requested for, they are served from the connection pool - a pool of ready to use connections. The MSDN states, "Connection pooling is a powerful functionality that can improve your applications' performance. But if you aren't a good lifeguard, your connection pools can become a detriment instead of a benefit." Connection Pooling gives you an idle, open, re-usable connection instead of opening a new connection every time a connection request to the database is made by the application. The same connection string should be used each time a database operation is made by the application. This will ensure that a new connection is not created each time. Note that we have one Connection Pool per connection string, i.e., as and when the connection string changes, a new connection pool is created for the incoming request.

When you are specifying the connection string, ensure that you specify the IP address of the database server to connect to, rather than the Database Server’s DNS name. This will ensure that there is no need for name resolution, hence minimizing the time required to connect to the Database Server.

When the connection is closed, it is returned back to the pool. There is a limit to the maximum number of open connections, i.e., if the maximum limit of available connections in the connection pool is reached, no more connections will be available henceforth as the pool will not have any more available or ready to use connections to serve an incoming request for a connection by the application. To avoid such scenarios, we should always open or use connections as late as possible and dispose or close them early so that the connection pool always contains a minimum number of ready to use connections to serve the incoming requests. This practice also maximizes the connection pool performance and hence the performance of the application as a whole is also boosted.

As an example, the following code snippet shows how we can use or open connections late, just before we require them:

SqlConnection sqlConnection = new SqlConnection();
     
try
{
sqlConnection.ConnectionString = 
ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString.Trim();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = "Select * from StudentMaster";
sqlConnection.Open();
sqlCommand.Connection = sqlConnection;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

  if (sqlDataReader.Read())
  {
      txtName.Text = sqlDataReader["Name"].ToString();
      txtAddress.Text = sqlDataReader["Address"].ToString();
      txtPhone.Text = sqlDataReader["Phone"].ToString();
  }
 }

 catch (Exception ex)
 {
  Response.Write("Error: " + ex.Message);
 }

 finally
 {
  if(sqlConnection.State == ConnectionState.Open)
  sqlConnection.Close();
   }

The above example shows how we can try-catch-finally blocks when performing database operations using ADO.NET. The finally block has been used to dispose or close the connection object, i.e., close the connection and return it back to the connection pool.

In the above code we have opened a connection, retrieved data using a data adapter and populated a data set with that data. Exceptions that can be raised are handled in the try - catch block and the finally block is used to close the connection.

The "using" keyword is an equivalent of try - finally block. The following code snippet illustrates how we can use the "using" keyword to ensure that the objects are disposed before they leave the scope in which they have been created:

using(SqlConnection sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = 
ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString.Trim();
sqlConnection.Open();
          
using (SqlDataAdapter sqlDataAdapter = 
new SqlDataAdapter("Select * from StudentMaster", sqlConnection))
  {
    try
     {
        DataSet dataSet = new DataSet();
        sqlDataAdapter.Fill(dataSet);
        Repeater1.DataSource = dataSet;
        Repeater1.DataBind();
         }

         catch (Exception ex)
         {
          Response.Write("Error occured: " + ex);
       }
   }
}

When you are using the DataSet merely as a container of data, and, not using and of its constraints and other properties, set the EnforceConstraints property of the dataset instance to “false” to turn off the constraints checking and hence improve the application's performance. Also, use the BeginLoadData and EndLoadData methods of the DataTable class before and after you use the Fill method of the DataAdapter to populate data. In doing so, unnecessary index maintenance will be avoided and hence, data processing will be much faster. Here is an example that illustrates how you can implement this in your program:

using(SqlConnection sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = 
ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString.Trim();
sqlConnection.Open();
          
 using (SqlDataAdapter sqlDataAdapter = 
 new SqlDataAdapter("Select * from StudentMaster", sqlConnection))
  {
    try
      {
        DataSet dataSet = new DataSet();
        DataTable dataTable = new DataTable();
        dataTable.BeginLoadData();
        sqlDataAdapter.Fill(dataTable);
        dataTable.EndLoadData();
        dataSet.EnforceConstraints = false;
        dataSet.Tables.Add(dataTable);
        Repeater1.DataSource = dataSet;
        Repeater1.DataBind();
        }

          catch (Exception ex)
           {
             Response.Write("Error occurred: " + ex);
           }
        }
   }

Note that you should use DataReaders for reading data as much as possible in lieu of DataSets. DataReaders are much faster compared to DataSets and should be a good choice in situations where you only need to read the data from the database and bind it to the controls. If you need to send the data retrieved from the database to another domain, you have only one choice, i.e., the DataSet. The first code example in this article shows how you can use a DataReader to bind data to the data controls in the presentation layer of your application.

When you need to perform CRUD (Create, Read, Update and Delete) operations in a batch, use batched queries for better performance using the UpdateBatchSize property of the DataAdapter. This will reduce the database roundtrips boost the application's performance. At the beginning of any stored procedure that you use, use the following statement to turn off any unnecessary message displays:

SET NOCOUNT ON 

Conclusion

This article has had a look at some of the best practices in using ADO.NET. Happy reading!