CODEWITHSUNDEEP
c#sqlexception
y0j0
y0j0

Reputation: 3602

Primary vs Unique key violation exception handling

I'd like to catch SQL exception and differ between primary key violation and unique key violation. These two types of exceptinons returns the same ErrorCode 2627.

try
{

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

It is nice but I'd like to throw UniqueKeyException or PrimaryKeyException. I know possibility to recognize which exception to throw, but it is parsing error message that is starting with 'Violation of UNIQUE KEY constraint' or 'Violation of PRIMART KEY constraint'. Of course, I'd like to avoid this option.

Another possibility is to do it directly in my stored procedure but I have a lot of stored procedures and it coul'd be quite annoying to add it everywhere.

Do you know some possiblities how to handle this issue in elegant way?

Upvotes: 3

Views: 2568

Answers (2)

SouravA
SouravA

Reputation: 5243

Dont know why I am not able to post this as an answer, but asking me to post this as a comment. This has been answered before. You can get your answer here.

In a nutshell, you could use the hack below:

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

Upvotes: 1

StuartLC
StuartLC

Reputation: 107267

I can't see a way of differentiating other than the error message string, viz:

PK Violation:

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 its in your control to do so, you could consider changing the Unique Constraint to a Unique Index?

If so, you could then detect between 2601 for a unique index violation, and 2627 for the PK Violation.

Upvotes: 3

Related Questions