Catching and Handling .NET Exceptions
To handle data access exception conditions, place your data access code within a try block and trap any exceptions generated by using catch blocks with the appropriate filter. For example, when writing data access code by using the SQL Server .NET Data Provider, you should catch exceptions of type SqlException, as shown in the following code:
// Data access code
catch (SqlException sqlex) // more specific
catch (Exception ex) // less specific
If you provide more than one catch statement with differing filter criteria, remember to order them from most specific type to least specific type. That way, the most specific type of catch block is executed for any given exception type.
This SqlException class exposes properties that contain details of the exception condition. These include:
- A Message property that contains text describing the error.
- A Number property that contains the error number, which uniquely identifies the type of error.
- A State property that contains additional information about the invocation state of the error. This is usually used to indicate a particular occurrence of a specific error condition. For example, if a single stored procedure can generate the same error from more than one line, the state should be used to identify the specific occurrence.
- An Errors collection, which contains detailed error information about the errors that SQL Server generates. The Errors collection will always contain at least one object of type SqlError.
The following code fragment illustrates how to handle a SQL Server error condition by using the SQL Server .NET Data Provider:
// Method exposed by a Data Access Layer (DAL) Component
public string GetProductName( int ProductID )
SqlConnection conn = null;
// Enclose all data access code within a try block
conn = new SqlConnection(
SqlCommand cmd = new SqlCommand("LookupProductName", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", ProductID );
SqlParameter paramPN =
cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
paramPN.Direction = ParameterDirection.Output;
// The finally code is executed before the method returns
catch (SqlException sqlex)
// Handle data access exception condition
// Log specific exception details
// Wrap the current exception in a more relevant
// outer exception and re-throw the new exception
throw new DALException(
"Unknown ProductID: " + ProductID.ToString(), sqlex );
catch (Exception ex)
// Handle generic exception condition . . .
if(conn != null) conn.Close(); // Ensures connection is closed
// Helper routine that logs SqlException details to the
// Application event log
private void LogException( SqlException sqlex )
EventLog el = new EventLog();
el.Source = "CustomAppLog";
strMessage = "Exception Number : " + sqlex.Number +
"(" + sqlex.Message + ") has occurred";
el.WriteEntry( strMessage );
foreach (SqlError sqle in sqlex.Errors)
strMessage = "Message: " + sqle.Message +
" Number: " + sqle.Number +
" Procedure: " + sqle.Procedure +
" Server: " + sqle.Server +
" Source: " + sqle.Source +
" State: " + sqle.State +
" Severity: " + sqle.Class +
" LineNumber: " + sqle.LineNumber;
el.WriteEntry( strMessage );
Within the SqlException catch block, the code initially logs the exception details by using the LogException helper function. This function uses a foreach statement to enumerate the provider-specific details within the Errors collection and records the error details to the error log. The code within the catch block then wraps the SQL Server-specific exception within an exception of type DALException, which is more meaningful to the callers of the GetProductName method. The exception handler uses the throw keyword to propagate this exception back to the caller.