SqlException Wrap Handler

Purpose

The Exception Handling Application Block comes with a WrapHandler which lets you wrap one exception with another one, based on the type of the originating exception. While this handler does what it was designed for, it has a limitation in that there is no way to wrap the original exception with different types of exceptions based on anything other than the type - for example there is no way to choose different wrapping exceptions based on properties of the original exception.

This is a particular problem when dealing with SQL Server exceptions. When a command execution fails, you'll always get back a SqlException - but this can mean any number of things. You need to inspect the errors in Errors property to figure out what actually went wrong. This often leads to the need to write code like this:
try
{    
    Database db = DatabaseFactory.CreateDatabase("MyDatabase");
    db.ExecuteNonQuery("spMySproc", param1, param2);
}
catch (SqlException ex)
{
    if (ex.Errors.Count > 0) // Assume the interesting stuff is in the first error
    {
        switch (ex.Errors[0].Number)
        {
            case 547: // Foreign Key violation
                throw new InvalidOperationException("Some helpful description", ex);
                break;
            case 2601: // Primary key violation
                throw new DuplicateRecordException("Some other helpful description", ex);
                break;
            default:
                throw new DataAccessException(ex);
        }
    }
}    


The SqlException Wrap Handler lets you accomplish the same kind of thing using the Exception Handling Application Block, without the need to write custom code. Once added to your exception handling policy, you can configure different SQL error codes and set a different wrapping exception type and message for each. All you have to do in your code is to call the Exception Handling Application Block in the usual way (or you could use the PIAB Exception Handling Handler):
try
{
    Database db = DatabaseFactory.CreateDatabase("MyDatabase");
    db.ExecuteNonQuery("spMySproc", param1, param2);
}
catch (Exception ex)
{
    if (ExceptionPolicy.HandleException(ex, "Data Access Policy"))
        throw;
}

Usage

  1. In your project, add references to the following assemblies:
    1. Microsoft.Practices.EnterpriseLibrary.Common.dll
    2. Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.dll
    3. Microsoft.Practices.ObjectBuilder.dll
    4. EntLibContrib.ExceptionHandling.dll
  2. Ensure that EntLibContrib.ExceptionHandling.dll and EntLibContrib.ExceptionHandling.Design.dll are in your configuration tool folder (usually C:\Program Files\Microsoft Enterprise Library May 2007\bin)
  3. Open your project's configuration file in one of the Enterprise Library configuration editors
  4. Add the Exception Handling Application Block to the configuration file
  5. Add a new exception handling policy and give it an appropriate name
  6. Add the System.Data.SqlClient.SqlException type to your policy (note that this handler will not work for any other exception type)
  7. IMPORTANT: Set the PostHandlingAction for the type to ThrowNewException.
  8. Add the SqlExceptionWrapHandler under the SqlException type in the policy
  9. Configure the properties on the handler node:
    1. Set the DefaultExceptionMessage to a string which will be used in the wrapped exception, if no error codes in the original exception match error codes specified in configuration.
    2. Set the DefaultExceptionTypeName to the type to be used for the wrapped exception, if no error codes in the original exception match error codes specified in configuration.
    3. Set the ErrorCodeNotConfiguredBehavior to specify what should happen if no error codes n the original exception match error codes specified in configuration. The options are WrapWithDefaultException and ReturnOriginalException.
  10. For each SQL Server error code you want to wrap with a different exception type:
    1. Right-click on the SqlExceptionWrapHandler node and choose New > SQL Error Code
    2. Set the ErrorCode property to the numeric SQL error number for this exception type
    3. Set the ExceptionMessage to a string which will be used in the wrapped exception if this error code matches one in the original exception
    4. Set the WrapExceptionTypeName to the type to be used for the wrapped exception if this error code matches one in the original exception
  11. Add any other exception handlers and policies as required
  12. Add code to your application to catch SqlExceptions and pass them into the Exception Handling Application Block (see earlier example).

SqlExceptionWrapHandler.png

More

For more details see Tom's blog post at http://blogs.msdn.com/tomholl/archive/2007/08/01/mapping-sql-server-errors-to-net-exceptions-the-fun-way.aspx

Last edited Dec 15, 2009 at 1:17 PM by ewdev, version 6

Comments

mgbrown Nov 30, 2009 at 12:11 PM 
Would it be possible for someone to show what the XML in the Config file looks like for the example created in the post using the config editor please?

ncoimbra Feb 13, 2009 at 1:31 PM 
Hi! I'm having trouble using this. As soon as I copy the .Design.dll assemblies to the bin folder of the EntLib, the Configuration Tool stops working properly... Any thoughts?

brianpmucha Sep 28, 2008 at 11:16 PM 
...you could *LOCALIZE* the field names...

brianpmucha Sep 28, 2008 at 11:14 PM 
There is a resources file which would allow you to customize what you see in the Ent. Library Configuration tool's interface, which is what the red outlined image above shows. You could rationalize the field names and so on for your developers in this way.

brianpmucha Sep 28, 2008 at 11:05 PM 
The SqlExceptionWrapHandler doesn't seem to include any special support for localization. Everything the user might see (the message in the wrapped exception) is controlled by entries in the web.config file. The stock EHAB WrapHandler allows you to reference a Resources file instead of specifying text in the config. But again that isn't in THIS handler.

devnet247 Dec 28, 2007 at 10:51 AM 
Does anybody know of an example of how to handle exceptions in multiple languages(localization).This is vital if we were to adopt the Enterprise Library.
Thanks a lot

SBludov Dec 14, 2007 at 11:55 AM 
How to perfom ExceptionMessage localization?