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.