Mapping SQL Server Errors to .NET Exceptions (the fun way!)

As I mentioned in my last post, I've been having some fun discovering what it's like to use patterns & practices deliverables on a real enterprise application. One of the challenges I've faced was figuring out an exception management strategy that included the ability to convert cryptic database error codes to .NET exceptions that are more meaningful for the business layer. My first instinct was to write code something 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);
        }
    }

}

Even ignoring problems like the fact that I'm only looking at the first error, and swallowing the exception in the unlikely event that there are no included errors, I hate code like this. It's using an imperative syntax for something inherently declarative, and the code would likely need to be replicated in many different places. Moving this code into a reusable utility method would help, but it would still be ugly and hard to maintain.

But wait - isn't there an application block designed to address just this problem? Well, yes - the Exception Handling Application Block is designed to let you externalise routine exception handling tasks, such as logging exceptions, wrapping or replacing on exception to another, or mapping to fault contract objects. But while the EHAB is completely capable of performing the wrapping exercise we want here, it unfortunately doesn't offer enough flexibility in selecting which policies should apply when. The block only lets you choose a policy based on the type of the exception and the policy name - there isn't any way to influence the behaviour based on more subtle details such as the value of the exception's properties or nested SqlError objects. In an ideal world, the EHAB would provide more flexibility (and even extensibility) in how policies are chosen (and I've suggested as much to Grigori). But because I couldn't wait for a new release of Enterprise Library to solve this problem, I decided to take things into my own hands.

My solution was to extend the Exception Application Block with a new SqlException Wrap Handler. I'll admit that it's a bit of a frankenhandler, as it combines the functionality of the existing Wrap Handler with some more flexible selection logic (which ideally wouldn't be in a handler at all). But with what I had to work with, it seems to do the job pretty well. The handler allows you to configure the various SQL Server error codes you expect to see, and specify a different .NET exception to wrap around each one. The handler also loops through all of the errors contained in the SqlException, and the wrapping exception will be chosen based on the first error that matches a configuration entry. If none match, you get the option of returning the original exception, or wrapping it in a specified default exception type. But since a picture tells 2¹⁰ words, here's what the handler's configuration looks like in the Enterprise Library Configuration Editor:

With the SqlException Wrap Handler properly configured, the code sample above can be simplified into this:

 try
{
    Database db = DatabaseFactory.CreateDatabase("MyDatabase");
    db.ExecuteNonQuery("spMySproc", param1, param2);
}
catch (Exception ex)
{

    if (ExceptionPolicy.HandleException(ex, "Data Access Policy"))

        throw;

}

This is a lot nicer, but it still requires that the boilerplate exception logic is included in every data access method. But with the magic of the Policy Injection Application Block, you don't even need to do this. By configuring a policy in the PIAB configuration, or decorating your data access class or individual methods with the [ExceptionCallHandler] attribute, you can get the desired behavior with no exception handling logic in the code at all!

If you've ever had a similar design challenge and think this approach will help, you're next question is probably "where do I get the code?". The good news is that I've checked this handler into the EntLibContrib project, which is an open-source library of extensions for Enterprise Library. My extension has only just been checked in, and it hasn't gone through any real reviews or testing, so please exercise a degree of caution at this time (meaning, you're the test team!). I missed the cut-off for the first official release, so for now you'll need to download the code directly from the source tree. If you find any issues or have any suggestions, please log them through the issue tracker tool. Or better yet, get involved with the project so you can improve the code yourself and submit your own extensions.

Comments

• Anonymous
  July 31, 2007
  Not to mention having to write all the individual exception classes to handle Number being 1 to 21999+.  This is a corner exception handing case where the standard exception handling practices become a hindrance, obviously it would not be good to have 21000+ individual exception classes. One of the wonderful things about exception handling is the ability to rethrow new exceptions.  If an exception can't be handled but the code that caught the exception can add value to it because of it's context, one useful tool is to rethrow a new exception.  I don't really see that possibility here (except to throw a different exception from HandleException, which doesn't smell right).  I think the ability to get a new exception from HandleException (or TryHandleException) that the catch block could re-throw or do something with (since HandleException is an abstraction) could prove useful.

• Anonymous
  July 31, 2007
  Peter - this example is all about throwing new exceptions. The Exception Handling Application Block can be configured to do any of the following actions after executing the exception handling pipeline:

• Swallow exceptions (HandleException returns false)
• Notify that the original exception should be rethrown (HandleException returns true)
• Throw the exception coming out of the pipeline (This happens inside HandleException so nothing is returned). When using the SqlExceptionWrapHandler (or the built-in WrapHandler and ReplaceHandler), the only option that makes sense is to throw the new exception. Also I agree you wouldn't want to create 21,000 exception classes, but this solution doesn't require you to do so. In most situations there are only a handful of error codes that you will likely justify a new exception type, since, as you mentioned the whole point here is to add context that a higher layer might be able to deal with - and there is a good chance that 99% of possible SQL errors won't be dealt with individually.

• Anonymous
  July 31, 2007
  I forgot to mention that from EntLib 3.0, there is a new overload to ExceptionPolicy.HandleException that returns the exception coming out of the pipeline as an out parameter, rather than automatically throwing it. I think this is exactly what you suggested Peter.

• Anonymous
  August 01, 2007
  Tom, yes the ExceptionPolicy.HandleException overload seems to be exactly what I was talking about... With regard to 21000 exception classes; I was basically agreeing that the finally exception handling code in your blog is necessary.  It's effectively impossible to write 21000 exception classes to make to each of the SQL Server error codes.  But, in the general case it's preferred to have a error-condition-to-exception mapping.

• Anonymous
  August 02, 2007
  But if we use [ExceptionCallHandler]? How could we capture the error messages and present under the Web Pages? We still need to try.. catch to make it work if we really want to show that error message in the web page. Is this true?

• Anonymous
  August 03, 2007
  Alex - the solution described in this post is about wrapping SqlExceptions into more meaningful .NET exceptions as they leave the data access layer. In order to display messages to users in an ASP.NET application, you'd most likely want to use a standard try... catch approach.

• Anonymous
  August 13, 2007
  Hi, I would appreciate it if the ErrorCode in the handlers configuration could contain a comma separated list of values like: 547,2601,2602,2603,2604,2605 or even better: 547,2601-2605

• Anonymous
  September 12, 2007
  The comment has been removed

• Anonymous
  October 01, 2007
  In keeping with the p&p team's tradition of naming a release after the month that's just finished,

• Anonymous
  October 01, 2007
  This morning I woke up to Tom's announcement on the new EntLib Contrib release. No big surprises