Valamas - AUS Valamas - AUS - 3 months ago 41
C# Question

SqlException catch and handling

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

Answer

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