Effective SqlException Handling: Precise Error Catching Based on Error Numbers

Introduction

In database application development, proper handling of SQL exceptions is crucial for system robustness. Many developers habitually identify specific errors by parsing exception message text—an approach that, while intuitive, has significant drawbacks. Message text can vary due to database version, language settings, or specific context, making code fragile and hard to maintain.

Limitations of Traditional Approaches

Consider this common scenario: catching a "table does not exist" error. Traditional implementations typically check if the message starts with a specific string:

try
{
    // Database operation code
}
catch(SqlException sqlEx)
{
    if (sqlEx.Message.StartsWith("Invalid object name"))
    {
        // Handle table non-existence
    }
    else
        throw;
}

Similarly, for duplicate key errors:

if (sqlEx.Message.StartsWith("Cannot insert duplicate key row in object"))

The problem with this method is message text instability. Different language versions of SQL Server may use varying message formats, and future versions could adjust wording. Additionally, messages may contain dynamic content (e.g., table names, index names), complicating string matching.

Solution Based on Error Numbers

The SqlException class provides a Number property corresponding to SQL Server's system error numbers. Each error has a unique numeric identifier, unaffected by language or format changes.

A basic implementation uses a switch statement:

catch (SqlException e)
{
   switch (e.Number)
   {
      case 2601:
         // Handle duplicate key error
         break;
      default:
         throw;
   }
}

Here, 2601 is the fixed number for the "Cannot insert duplicate key" error. The core advantage of this approach is stability: error numbers maintain backward compatibility across SQL Server versions.

C# 6.0 Enhancement: Exception Filters

C# 6.0 introduced exception filters, allowing conditional checks before catch block execution:

catch (SqlException e) when (e.Number == 2601)
{
   // Execute only when error number is 2601
}

This syntax is more concise and does not pollute the exception stack. When the condition is not met, the exception continues to propagate upward instead of being caught and re-thrown.

Systematic Error Handling Framework

For large projects, it is advisable to create a dedicated helper class encapsulating error-checking logic:

public static class SqlExceptionHelper
{
    // 208: Invalid object name '%.*ls'.
    public static bool IsInvalidObjectName(SqlException sex)
    { return (sex.Number == 208); }

    // 2601: Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.
    public static bool IsDuplicateKey(SqlException sex)
    { return (sex.Number == 2601); }
}

This method enhances code readability and maintainability. The correspondence between error numbers and meanings can be obtained by querying system tables:

SELECT * FROM sys.messages WHERE language_id = 1033 ORDER BY message_id

It is recommended to document common error numbers or create a constants class for team sharing.

Practical Recommendations

1. Prioritize Error Numbers: Always use the Number property over message text when precise error identification is needed.
2. Handle Unknown Errors: For unhandled error numbers, re-throw the exception or log it to avoid silent failures.
3. Consider Performance: Exception handling should serve as an error recovery mechanism, not normal flow control. Frequent exception catching can impact performance.
4. Test Multilingual Environments: If the application supports multilingual databases, ensure error handling logic works correctly across different language settings.

Conclusion

Identifying errors via the SqlException.Number property offers a more reliable and maintainable solution than parsing message text. Combined with C# 6.0's exception filter feature, developers can write concise and robust exception handling code. Establishing a systematic framework based on error numbers is essential for long-term maintenance and cross-environment deployment.