Handling Database Errors

Article
08/11/2011

Applies to: Windows Communication Foundation

Published: June 2011

Referenced Image

This topic contains the following sections.

Handling Database Errors

Handling Database Errors

Almost every implementation of WCF services uses a database. Typically, most errors in your service originate with that database. It is critical to identify the root cause of the errors as quickly as possible. You do not want to have to wait until a database administrator sets up a profile that tries to recapture an error that may well be intermittent. Capture as much information as possible when an error occurs. Include the connection string, the name of the stored procedure or SQL statement, as well as any parameters that are used. To capture this information, you must wrap every call to the database with a try…catch block.

The following code is a useful extension to the System.Exception class that captures parameters from a Command object.

Visual C# Exception Extension to Capture Command Information

/// <summary>
/// Populates the exception with command parameter data.
/// </summary>
/// <param name="ex">The exception.</param>
/// <param name="cmd">The sql command object.</param>
public static void PopulateExceptionWithCommandParameterData(this Exception ex, DbCommand cmd)
{
    if (cmd != null)
    {
        AddExceptionDataValue(ex, "CommandType", cmd.CommandType.ToString());
        if (cmd.Connection != null)
        {
            AddExceptionDataValue(ex, "ConnectionString", cmd.Connection.ConnectionString);
        }
        AddExceptionDataValue(ex, "CommandText", cmd.CommandText);
        if (cmd.Parameters != null)
        {
            foreach (SqlParameter parameter in cmd.Parameters)
            {
                AddExceptionDataValue(ex, parameter.ParameterName, parameter.Value);
            }
        }
    }
    else
    {
        AddExceptionDataValue(ex, "Additional Info: ", "Failed to create command object.");
    }
}

/// <summary>
/// Adds data to an exception to help troubleshoot any problems, but first checks to make sure
/// that the key does not already exist so a new exception won't get raised.
/// </summary>
/// <param name="ex">Exception to add data to</param>
/// <param name="key">Data Key</param>
/// <param name="value">Data value</param>
public static void AddExceptionDataValue(this Exception ex, object key, object value)
{
 
    if (!ex.Data.Contains(key))
    {
        ex.Data.Add(key, value.GetNullSafeString().Trim());
    }
}

Visual Basic Exception Extension to Capture Command Information

''' <summary>
''' Populates the exception with command parameter data.
''' </summary>
''' <param name="ex">The exception.</param>
''' <param name="cmd">The sql command object.</param>
<System.Runtime.CompilerServices.Extension> _
Public Shared Sub PopulateExceptionWithCommandParameterData(ex As Exception, cmd As DbCommand)
     If cmd IsNot Nothing Then
          AddExceptionDataValue(ex, "CommandType", cmd.CommandType.ToString())
          If cmd.Connection IsNot Nothing Then
               AddExceptionDataValue(ex, "ConnectionString", cmd.Connection.ConnectionString)
          End If
          AddExceptionDataValue(ex, "CommandText", cmd.CommandText)
          If cmd.Parameters IsNot Nothing Then
               For Each parameter As SqlParameter In cmd.Parameters
                    AddExceptionDataValue(ex, parameter.ParameterName, parameter.Value)
               Next
          End If
     Else
          AddExceptionDataValue(ex, "Additional Info: ", "Failed to create command object.")
     End If
End Sub

''' <summary>
''' Adds data to an exception to help troubleshoot any problems, but first checks to make sure
''' that the key does not already exist so a new exception won't get raised.
''' </summary>
''' <param name="ex">Exception to add data to</param>
''' <param name="key">Data Key</param>
''' <param name="value">Data value</param>
<System.Runtime.CompilerServices.Extension> _
Public Shared Sub AddExceptionDataValue(ex As Exception, key As Object, value As Object)

     If Not ex.Data.Contains(key) Then
          ex.Data.Add(key, value.GetNullSafeString().Trim())
     End If
End Sub

If you use ADO.NET for data access, you can also wrap calls to the database with a try…catch block. The following code shows how to do this.

Visual C# Example Data Access Try…Catch

     try
{
    using (IDataReader reader = cmd.ExecuteReader())
    {
        //load data from reader into object
    }
}
catch (Exception ex)
{
    ex.PopulateExceptionWithCommandParameterData(ex, cmd);
    throw;
}

Visual Basic Example Data Access Try…Catch

Try
               'load data from reader into object
     Using reader As IDataReader = cmd.ExecuteReader()
     End Using
Catch ex As Exception
     ExceptionManager.PopulateExceptionWithCommandParameterData(ex, cmd)
     Throw
End Try

To improve on this approach, you can create some extension methods on the DbCommand object to wrap the logic. This wrapper means that you do not have to write any try…catch blocks in your data access code. An alternative to extensions methods is to write a wrapper class. The following code shows how to write the extension method.

Visual C# DbCommand Extension to Capture More Exception Detail

/// <summary>
/// DbCommand extension to execute the reader and capture more 
/// detail in the exception.
/// </summary>
/// <param name="cmd"></param>
public static void ExecuteReaderExtended(this DbCommand cmd)
{
    try
    {
        cmd.ExecuteReader();
    }
    catch (Exception ex)
    {
        ex.PopulateExceptionWithCommandParameterData(cmd);
    }
}

Visual Basic DbCommand Extension to Capture More Exception Detail

''' <summary>
''' DbCommand extension to execute the reader and capture more 
''' detail in the exception.
''' </summary>
''' <param name="cmd"></param>
<System.Runtime.CompilerServices.Extension> _
Public Shared Sub ExecuteReaderExtended(cmd As DbCommand)
     Try
          cmd.ExecuteReader()
     Catch ex As Exception
          ex.PopulateExceptionWithCommandParameterData(cmd)
     End Try
End Sub

While this approach works well if you use ADO.NET for data access, not every project does. There are other ways to capture SQL information, such as the Entity Framework (EF). For more information about EF, see "The ADO.NET Entity Framework" at https://msdn.microsoft.com/en-us/data/aa937723. The EF approach is more complex. To capture this level of detail in EF, you must create your own provider. A good introduction is Jaroslaw Kowalski's article, "Tracing and Caching for Entity Framework" at https://blogs.msdn.com/b/jkowalski/archive/2009/06/11/tracing-and-caching-in-entity-framework-available-on-msdn-code-gallery.aspx.

Previous article: Implementing Exception Management, Part 2

Continue on to the next article: Validation, Part 1

Share via

Handling Database Errors

Handling Database Errors

Additional resources