How to catch a specific SqlException error?

Solution 1:

The SqlException has a Number property that you can check. For duplicate error the number is 2601.

catch (SqlException e)
{
   switch (e.Number)
   {
      case 2601:
         // Do something.
         break;
      default:
         throw;
   }
 }

To get a list of all SQL errors from you server, try this:

 SELECT * FROM sysmessages

Update

This can now be simplified in C# 6.0

catch (SqlException e) when (e.Number == 2601)
{
   // Do something.
}

Solution 2:

Sort of, kind of. See Cause and Resolution of Database Engine Errors

class SqllErrorNumbers
{ 
   public const int BadObject = 208;
   public const int DupKey = 2627;
}

try
{
   ...
}
catch(SqlException sex)
{
   foreach(SqlErrorCode err in sex.Errors)
   {
      switch (err.Number)
      {
      case SqlErrorNumber.BadObject:...
      case SqllErrorNumbers.DupKey: ...
      }
   }
}

The problem though is that a good DAL layer would us TRY/CATCH inside the T-SQL (stored procedures), with a pattern like Exception handling and nested transactions. Alas a T-SQL TRY/CATCH block cannot raise the original error code, will have to raise a new error, with code above 50000. This makes client side handling a problem. In the next version of SQL Server there is a new THROW construct that allow to re-raise the original exception from T-SQL catch blocks.

Solution 3:

It is better to use error codes, you don't have to parse.

try
{
}
catch (SqlException exception)
{
    if (exception.Number == 208)
    {

    }
    else
        throw;
}

How to find out that 208 should be used:

select message_id
from sys.messages
where text like 'Invalid object name%'

Solution 4:

If you want list of error messages met in Sql server, you can see with

SELECT *
FROM master.dbo.sysmessages

Solution 5:

You can evaluate based on severity type. Note to use this you must be subscribed to OnInfoMessage

conn.InfoMessage += OnInfoMessage;
conn.FireInfoMessageEventOnUserErrors = true;

Then your OnInfoMessage would contain:

foreach(SqlError err in e.Errors) {
//Informational Errors
if (Between(Convert.ToInt16(err.Class), 0, 10, true)) {
    logger.Info(err.Message);
//Errors users can correct.
} else if (Between(Convert.ToInt16(err.Class), 11, 16, true)) {
    logger.Error(err.Message);
//Errors SysAdmin can correct.
} else if (Between(Convert.ToInt16(err.Class), 17, 19, true)) {
    logger.Error(err.Message);
//Fatal Errors 20+
} else {
    logger.Fatal(err.Message);
}}

This way you can evaluate on severity rather than on error number and be more effective. You can find more information on severity here.

private static bool Between( int num, int lower, int upper, bool inclusive = false )
{
    return inclusive
        ? lower <= num && num <= upper
        : lower < num && num < upper;
}