SqlException conversion to a custom Exception handler

Question

I have an existing application which uses MS SQL stored procedures to enforce some business rules. When an error is detected it is raised as an exception using RAISERROR back to my .Net application.

The .Net application can then use Try/Catch blocks to catch and exceptions and perform and business logic. The problem is that there are several business rules validated in a single stored procedure. Which could raise different exceptions. What is the best way to capture these SQL exceptions and convert them to custom .Net Exception handlers.

For example my stored procedure may throw an exception for RuleA and RuleB. In my .Net code I can only capture SqlException. My custom error message for either RuleA or RuleB is returned in the SqlException inner exception. I could parse the Message string but this is UGLY and if someone changes the implementation in the stored proc. my logic would not pick it up.

What is a preferred method to translate the generic SqlException into MyRuleAException or MyRuleBException?

Answer 1

Normally the way to do it is to define the error constants in your .Net code and then you can check the value in your exception handling code. You can use constants to make the code more readable, something like this:

/// <summary>
/// Represents the error code returned from stored procedure when entity could not be found.
/// </summary>
private const int SQL_ERROR_CODE_ENTITY_NOT_FOUND = 50001;

/// <summary>
/// Represents the error code returned from stored procedure when entity to be updated has time mismatch.
/// </summary>
private const int SQL_ERROR_CODE_TIME_MISMATCH = 50002;

/// <summary>
/// Represents the error code returned from stored procedure when a persistence exception occurs (ex.
/// billing flag is invalid, child records exist which prevent a delete, etc.).
/// </summary>
private const int SQL_ERROR_CODE_PERSISTENCE_ERROR = 50003;

Then, you can handle the exceptions like this, and it makes your code much more readable and maintainable:

    if (e.InnerException is SqlException)
    {
        // verify exception code from SP and throw proper exception if required
        var sqlException = (SqlException)e.InnerException;
        if (sqlException.Number == SQL_ERROR_CODE_ENTITY_NOT_FOUND)
        {
            e = new EntityNotFoundException(e.Message, e);
        }
        else if (sqlException.Number == SQL_ERROR_CODE_TIME_MISMATCH)
        {
            e = new EntityTimestampMismatchException(e.Message, e);
        }
        else if (sqlException.Number == SQL_ERROR_CODE_PERSISTENCE_ERROR)
        {
            e = new EntityServicePersistenceException(e.Message, e);
        }
    }

This is about as clean as you can make it in my opinion, but it's still ok because you define the error codes in one place, so if anything ever changes, you just change the one constant.

And to raise the error, you can do something like this in T-SQL:

 -- record wasn't found, raise an error
 DECLARE @l_error NVARCHAR(1000)
 SET @l_error = 'Record with ' + @p_IdFieldName + ' = ' + CONVERT(VARCHAR(128), @p_id)
    + ' does not exist in table [' + @p_TableName + ']'
 EXEC sp_addmessage @msgnum=50001, @severity=16, @msgtext=@l_error, @replace='replace'
 RAISERROR(50001, 16, 1)

The 50001 represents the error number that will be in the SqlException.Number.