Alex Rivera | Logout

How to catch a specific SqlException error?

Asked 2011-06-03T00:54:03.120
74

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

2 Answers

9

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%'
answered 2011-06-03T01:03:51.423
4

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

SELECT *
FROM master.dbo.sysmessages
answered 2012-06-21T13:04:12.733

Your Answer