Clever Geek Handbook

Primary and exclusive exception handling

I would like to catch a SQL exception and differentiate between a primary key violation and a unique key violation. These two exceptinons return the same ErrorCode 2627.

try
{

}
catch (SqlException ex)
{
    if (ex.Number == 2627)
    {
        throw new UniqueOrDuplicateKeyException("Unique key or Primary key duplication")
    }
}

This is fine, but I would like to add a UniqueKeyException or PrimaryKeyException. I know of a way to recognize which exception is thrown, but it handles an error message that starts with "UNIQUE KEY constraint violation" or "PRIMART KEY constraint violation". Of course, I would like to avoid this option.

Another possibility is to do this directly in my stored procedure, but I have a lot of stored procedures and it can be very frustrating to add it all over the place.

Do you know some possibilities how to properly handle this problem?

+3


source to share


2 answers


I see no way to differentiate other than the error message line, which is:

Violating PC:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__Foo'. Cannot insert duplicate key in object 'dbo.Foo'.

Unique constraint violation



Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'U_Foo'. Cannot insert duplicate key in object 'dbo.Foo'.

If you need it, can you consider changing the unique constraint to be a unique index?

If so, you might find between 2601 for a unique index violation and 2627 for a PK violation.

+3


source


Not sure why I can't post this as an answer, but please post this as a comment. This has already been answered. You can get your answer here .

In a nutshell, you can use the hack below:



if (ex.Message.Contains("Unique")) // It is an unique key violation.
+1


source






More articles:


All Articles