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 ...
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.