Handling Exceptions

Strategies for handling exceptions are essential in any enterprise application. The following information will help you incorporate the Data Access Application Block into your approach to managing exceptions:

• The CreateDatabase method uses configuration information, which may result in configuration-related exceptions.
• The Database methods use both ADO.NET and the underlying database provider. Exceptions thrown by ADO.NET are caught by the Data Access Application Block for instrumentation purposes, and then they are rethrown.
• Adequately processing an exception often requires access to the specific exception type. You can include a catch statement for a specific database provider exception such as SqlException. However, database provider—specific exception types are not portable across different providers.

Use the CommandBehavior.CloseConnection paremeter when you call the ExecuteReader method. This closes the connection when the DataReader is closed. If you use ExecuteReader within a try block, you should add a finally statement and close the returned DataReader object, as shown in the following example.

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory"); 

IDataReader reader = null;

try
{
  //...
  reader = db.ExecuteReader(cmd);
}
catch(Exception ex)
{
  // Process exception
}
finally
{
  if (reader != null)
    reader.Close();
}

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim cmd As DbCommand = db.GetStoredProcCommand("GetProductsByCategory")

Dim reader As IDataReader = Nothing

Try
  ' ...
  reader = db.ExecuteReader(cmd)
Catch ex As Exception
  ' Process exception
Finally
  If (Not reader Is Nothing) Then
    reader.Close()
  End If
End Try

Alternatively, you can include the using statement to dispose of the DataReader object, which causes it to close, as shown in the following example.

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory"); 

using (IDataReader reader = db.ExecuteReader(cmd))
{
  // Process results
}

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim cmd As DbCommand = db.GetStoredProcCommand("GetProductsByCategory")

Using reader As IDataReader = db.ExecuteReader(cmd)

  ' Process results

End Using

For design and implementation guidelines for exception management in .NET, see the Design Guidelines for Exceptions.