articles >> asp.net / ado.net >> Working with Exceptions in ADO.NET

Working with Exceptions in ADO.NET

By : Joydip Kanjilal Feb 08, 2007

Printer friendly

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.