Convert SqlException to a custom exception handler

Question

I have an existing application that uses MS SQL stored procedures to enforce certain business rules. When an error is detected, it throws as an exception, using RAISERROR back to my .Net application.

The .Net application can then use the Try / Catch blocks for catch and exceptions, as well as for execution and business logic. The problem is that several business rules are validated in one stored procedure. This may cause various exceptions. What is the best way to catch 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 catch a SqlException. My custom error message for RuleA or RuleB is returned in an internal SqlException. I could parse the Message line, but this is UGLY, and if someone changes the implementation in a stored proc. my logic would not have caught him.

What is the preferred method for throwing a generic SqlException into MyRuleAException or MyRuleBException?

Answer 1

- . Net-, . , , :

/// <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;

, , :

    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);
        }
    }

, , , , , , - , .

, - 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)

50001 , SqlException.Number.

Answer 2

dcp. , . .

Answer 3

Can you indicate msg_idwhen you are causing the error? If so, I believe it can be found in the SqlException.Number member. Then you can do if / else. I would just make sure that it documents the stored procedure well.

UPDATE:

On closer inspection, I think you might be better off indicating different levels of errors when calling RAISERROR, and then checking that level through a member of SqlException.Class. 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){}
}