Working with Exceptions in ADO.NET

ADO.NET is a very rich data access technology with a plenty of powerful features - improved performance, an optimized SQL Provider, seamless support for XML and ability to work in connected and disconnected mode, to name a few. Handling exceptions properly is one of the prime concerns when working with any data access technologies. The new version of ADO.NET includes powerful support for working with exceptions efficiently. This article throws light on how to handle exceptions efficiently when working with ADO.NET and highlights the best practices that can be followed in this context.

What are Exceptions?

An exception is an error that occurs at runtime. If it is not handled properly, it terminates the normal flow of the program. According to MSDN, “An exception is any error condition or unexpected behavior encountered by an executing program. Exceptions can be raised because of a fault in your code or in code you call (such as a shared library), unavailable operating system resources, unexpected conditions the common language runtime encounters (such as code that cannot be verified), and so on. Your application can recover from some of these conditions, but not others. While you can recover from most application exceptions, you cannot recover from most runtime exceptions.” The .NET runtime creates an exception object to represent an exception when it occurs. It also creates an Exception Information Table for each executable. There are four different types of exception handlers, namely
A finally handler
A fault handler
A type-filtered handler
A user-filtered handler

Exceptions in ADO.NET

In ADO.NET 1.1, there was no common base class for ADO.NET exceptions. According to Frans Bouma, "every provider has its own exception for reporting errors, and they're not derived from a generic ADO.NET exception. This thus makes it hard to catch db specific exceptions in generic code and handle it". With ADO.NET 2.0 however, we have a new common exception base class called the System.Data.Common.DbException class. The generic DbException class in ADO.NET 2.0 exposes all the information a provider specific exception would require. This class enables catching the provider specific exceptions in a generic manner. However, exceptions should be avoided at all cost. Exceptions are expensive; they slow down the application, and use resources, regardless of whether or not they are handled. In addition, "logic by exception" can make debugging very tedious.

Exception Handlers

Exceptions are handled using the try, catch and finally blocks. The try block contains code that raises an exception. The exceptions that are raised in the try block are caught in one or more appropriate catch blocks. Why appropriate? The reason is that if an exception has occurred, only one of the catch blocks would be executed that is most appropriate to the type of the exception that has occurred. The finally block contains any necessary cleanup code. It should be noted that a try block should contain one or more catch blocks or at least a finally block. Why is a finally block required?
A finally block is executed whether or not an exception occurs. Hence, it is particularly useful for cleanup operations. MSDN states, “the purpose of a finally statement is to ensure that the necessary cleanup of objects, usually objects that are holding external resources, happens immediately, even if an exception is thrown”. Let us consider that we have opened a database connection in a try block. If an exception occurs, it is caught in the catch block just after the try block as shown in the code snippet below:

string connectionString = ...; // Some connection string
SqlConnection sqlConnection = null;
try
{
sqlConnection = new SqlConnection(connectionString);
          sqlConnection.Open();
          //Some code
}

catch (Exception ex)
{
//Some exception handling code
}

finally
{
           sqlConnection.Close();
}

Remember that the connections that are opened as late as possible and released as early as possible. They should be closed immediately we are done using them. Database connections should be closed as soon as you are done using them for efficient connection pooling and hence enhanced performance. Refer to my article on connection pooling and its efficient usage at Understanding Connection Pooling in NET. It is advisable to open the database connections in the try block and close them in the finally block. This would ensure that the connections would be properly closed irrespective of whether an exception has occurred or not. In the code example above, the connection is guaranteed to be closed as we have made a call to the Close() method on the SqlConnection instance in the finally block. I would however prefer to use the “using” statement for handling exceptions when using unmanaged resources. Let me explain what it is and how it can be used.

Using the “using” statement

The using statement can be used to specify a boundary for the object outside of which, the object is destroyed automatically. The runtime invokes the Dispose method of the objects that are specified within this statement when the control comes out of this block. This is why this is a preferred choice when using exceptions for managing resources in .NET. Refer to the following code that uses the “using” statement:

string connectionString = ...; // Some connection string

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
  sqlConnection.Open();
  //Some code
}

Note that when the end of the using block would be encountered, the Dispose () method will be immediately called on the instance. Note that when the Dispose() method is called on this connection instance, it checks to see if the connection is opened; if open it would close it implicitly prior to disposing off the instance. Please refer to my article at When and How to Use Dispose and Finalize in C# on Dispose and Finalize for more information on when and how to use them appropriately.
The above code gets translated implicitly to:

string connectionString = ...; // Some connection string

SqlConnection sqlConnection = new SqlConnection(connectionString));

try
{
  sqlConnection.Open();
  //Some code
}

finally
{
   ((IDispose)sqlConnection).Dispose();
}

Remember to keep the try block as short as possible. Note that in the code example above, we have just opened the connection in the try block. Do not use any unnecessary code/logic in the try block that is not supposed to throw any exception. Do not catch any exception that you cannot handle and avoid rethrowing exceptions unnecessarily as it is very expensive.

Prevent unnecessary database hit

One of the most useful of all features of ADO.NET is that you can attach messages to each row of data in a DataSet object. The SqlDataAdapter class attaches error messages to the rows of a DataSet if a specific database action has not been successfully completed. We can then check whether there are any errors in a DataSet prior to sending the same for updating the database using the HasErrors property of the DataSet instance. This, if used judiciously, can prevent an unnecessary database hit. Please refer to the code snippet that follows:

DataSet newDataSet = previousDataSet.GetChanges(DataRowState.Modified);

    if (newDataSet.HasErrors)
    {
      // If there are errors take appropriate action   
    }

    else
    {

      // Necessary code to update the database
    }

Conclusion

Exceptions should be used appropriately in ADO.NET and only used when required -- in exceptional cases. Even if they come in handy, exceptions tend to slow down the execution speed of the program and consume more resources. In this article, I have examined how to make proper use of exceptions when working with ADO.NET.