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.

Answer 2

Can you specify a msg_id when you raise an error? If so I believe this could be found in the SqlException.Number member. Then you could do an if/else on that. I would just make sure to document it well in the stored proc.

UPDATE:

On closer inspection I think you might be better off specifying different error levels when you call RAISERROR and then check that level through the SqlException.Class member. Example:

--Rule A
RAISERROR (N'Rule A violation.', -- Message text.
           10, -- Severity,
           1, -- State)

--Rule B
RAISERROR (N'Rule B violation.', -- Message text.
           9, -- Severity,
           1, -- State)

--Rule C
RAISERROR (N'Rule C violation.', -- Message text.
           8, -- Severity,
           1, -- State)

then in code:

catch(SqlException qex)
{
  if(qex.Class == 10){}
  else if(qex.Class == 9){}
  else if(qex.Class == 8){}
}

Answer 3

I agree with dcp. The process requires you to generate list of constants and is bit long process to implement. But will be very easy to maintain.