How to catch a specific SqlException error?

Question

Q: Is there a better way to handle SqlExceptions?

The below examples rely on interpreting the text in the message.

Eg1: I have an existing try catch to handle if a table does not exist.
Ignore the fact that I could check if the table exists in the first place.

try
{
    //code
}
catch(SqlException sqlEx)
{
        if (sqlEx.Message.StartsWith("Invalid object name"))
        {
            //code
        }
        else
            throw;
}

Eg2: without the try catch showing duplicate key exception

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

Solution: The start of my SqlExceptionHelper

//-- to see list of error messages: select * from sys.messages where language_id = 1033 order by message_id
public static class SqlExceptionHelper
{
    //-- rule: Add error messages in numeric order and prefix the number above the method

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

Tippler

Answer 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.
}

Edgerton

And you can filter by the language: select * from sysmessages where msglangid=1033. That is for English – Vladimar

Hi @Richard Schneider I know this answer has aged, but I was wondering are there new (better) solutions to also create the message automated, since I use MVC, and every model property has its own "translation" I would assume that I could write a standard message with the property name (because I map everytime they are exactly the same) and than get the renamed property name to show the message like "There is already a record known with [Value] as [FieldName] in our system, please choose differently". – Stirk

Would an catch (Exception e) catch an SQL exception? Or is it mandatory to use catch (SqlException e)? Because somehow catch (Exception e) does't catch "SqlException: Cannot insert duplicate key row". – Glidebomb

@JedatKinports we would need to see your code, but a general exception will catch an SQL exception as the later inherits from the former. For example there's no number, server, state or client connection id properties on a general exception. If you want the specific details available from an SqlException you need to explicitly catch it. Generally you chain multiple catches together, starting with the most specific and ending with a general Exception catch – Plasticity

Kudos for the C# 6.0 tip – Flyfish

Please note that SqlException.Number might be 0 on Linux. github.com/dotnet/SqlClient/issues/1773 – Karynkaryo

Answer 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.

Croatian

Thank you for the advice and links. btw: I like catching sex :) I will start using that instead of sqlEx for fun. Reminds me of the old classic asp days On Error Goto Hell – Tippler

This just doesn't sound right, err in sex.Errors ;) – Persistence

Inside wont' handle timeout and transport exceptions, so even with perfect handling with a sproc, it is still appropriate for call to handle. – Pyrope

Answer 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%'

Intrinsic

thanks for the advice. I will create a SqlException helper. Also, thank you very much for pointing me to the sql table of errors. select * from sys.messages where language_id = 1033 – Tippler

select * from master.dbo.sysmessages where msglangid=1033 or SELECT * FROM sys.messages WHERE language_id = 1033 to filter only english messages. Besides, the message_id in system table is not a one-one map as SqlException.Number. I've verified on C# and SQL Server 2008 R2, for Timeout error, the SqlException.Number is -2, but there's no such an error definition in system tables. – Beezer

How get language_id ? – Hillell

Answer 4

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

SELECT *
FROM master.dbo.sysmessages

Esurient

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

Rusk

Answer 6

If you are looking for a better way to handle SQLException, there are a couple things you could do. First, Spring.NET does something similar to what you are looking for (I think). Here is a link to what they are doing:

http://springframework.net/docs/1.2.0/reference/html/dao.html

Also, instead of looking at the message, you could check the error code (sqlEx.Number). That would seem to be a better way of identifying which error occurred. The only problem is that the error number returned might be different for each database provider. If you plan to switch providers, you will be back to handling it the way you are or creating an abstraction layer that translates this information for you.

Here is an example of a guy who used the error code and a config file to translate and localize user-friendly error messages:

https://web.archive.org/web/20130731181042/http://weblogs.asp.net/guys/archive/2005/05/20/408142.aspx

Faxun

Used the archive.org link to fix the broken link. – Faxun

Answer 7

With MS SQL 2008, we can list supported error messages in the table sys.messages

SELECT * FROM sys.messages

Knoll

Answer 8

I am working with code first, C# 7 and entity framework 6.0.0.0. it works for me

Add()
{
     bool isDuplicate = false;
     try
     {
       //add to database 
     }
     catch (DbUpdateException ex)
     {
       if (dbUpdateException.InnerException != null)
       {
          var sqlException = dbUpdateException.InnerException.InnerException as SqlException;
          if(sqlException != null)
             isDuplicate = IsDuplicate(sqlException);
       } 
     }
     catch (SqlException ex)
     {
        isDuplicate = IsDuplicate(ex);
     }  
     if(isDuplicate){
       //handle here
     }
}

bool IsDuplicate(SqlException sqlException)
{
    switch (sqlException.Number)
    {
        case 2627:
            return true;
        default:
            return false;
    }
}

N.B: my query for add item to db is in another project(layer)

Kelsy

I am having issues finding a namespace that contains DbUpdateException . I am using Entity Framework 5.0.0.0. But when I try to declare a Exception type in the catch statement of DbUpdateException it shows me as missing an assembly or reference. When I looked up the that exception type in MSDN it said it was part of the EntityFrameworkCore 2.0. – Swacked

Never mind. Found it in System.Data.Entity.Infrastructure. But shouldn't the code read if(sqlException != null) isDuplicate = IsDuplicate(sqlException); – Swacked

@Swacked I think it execute first catch (DbUpdateException ex). it same here – Kelsy

I like this answer as I'm doing Code First too. I want to say I agree with @user1161391. Inside the catch (DbUpdateException ex), the line if(sqlException == null) isDuplicate = IsDuplicate(sqlException); will always send null to IsDuplicate(), or are we missing something? – Doriandoric

Answer 9

For those of you rookies out there who may throw a SQL error when connecting to the DB from another machine(For example, at form load), you will find that when you first setup a datatable in C# which points to a SQL server database that it will setup a connection like this:

this.Table_nameTableAdapter.Fill(this.DatabaseNameDataSet.Table_name);

You may need to remove this line and replace it with something else like a traditional connection string as mentioned on MSDN, etc.

http://www.connectionstrings.com/sql-server-2008

Contrariety