SQL in a Nutshell, 2nd Edition

Error Handling in ADO.NET

ADO.NET’s error handling model is based on .NET exceptions. Functions or methods that can fail because of an error must be wrapped in a try/catch/finally block. The difficult part of handling exceptions is determining what to do when an error occurs and freeing resources for objects that were created. The following code example shows the error handling pattern that can be successfully used with ADO.NET to clean up all resources in the event of an error:

{Odbc|OleDb|Sql}Connection connection = null;
{Odbc|OleDb|Sql}Command statement = null;
{Odbc|OleDb|Sql}DataReader resultSet = null;
try {
   // Create and use the Connection, Command, and DataReader objects
   connection = new SqlConnection(connection_string);
   connection.Open( );

   statement = connection.CreateCommand( );
   statement.CommandText = SQL;

   resultSet = statement.ExecuteReader( );

} catch( Exception e ) {
   // Notify user that an error occurred
} finally {
   if( resultSet != null ) resultSet.Close( );
   if( statement != null ) statement.Dispose( );
   if( connection != null ) connection.Close( );
}

If an error occurs in the try block and an exception (of type Exception in this case) is thrown, the exception will be caught and the code in the catch block will be executed. The finally block, which will be executed regardless of whether an exception is thrown, should be responsible for freeing the resources. Which resources to free is difficult to determine if you don’t know the point of failure. For example, if the ...