Need a way to intercept only a Duplicate key exception in MSSQL

Question

Let's imagine that you have got a unique index constraint violation in your MSSQL 2008 database like this:

Violation of PRIMARY KEY constraint 'PK_ManufacturerCode'. Cannot insert duplicate key in object 'dbo.ManufacturerCode'. The duplicate key value is (8410179)

Currently I am handling this exception by a global exception handler in a thick client (written in Delphi 6 using ADO, but it does not matter, I plan do use C#.NET in a future) and the exception message is appended to a log box.

But I want to handle this specific duplicate key exception personally and display an appropriate error message in a client using thick client's own text. I could parse the error message for match, but won't do this out of two reasons:

• This is not a right way to do the thing - it is better to rely on some error codes;
• The thick client is intended to use variety of MSSQL server versions in diverse languages which may have different error texts

It does not cheer me up to do the job with RETURNs with custom exit codes or constantly calling a special stored procedure to check for duplicates.

Answer 1

In ADO, you'd access the connections Errors property after an error occurs, and examine the NativeError property to get the SQL Server specific error numbers.

When you switch to .NET, the same number is exposed as the Number property of SqlException or SqlError

However, finding the specific error numbers can be a bit of a chore - For Duplicate Key, it's error number 2627.

Answer 2

AFAIK, the only way to tell a duplicate key exception apart is to parse the error message.

A better solution is to prevent the duplicate key exception in the first place. Write the insert not to insert duplicate keys, and check with @@rowcount if a row was added:

insert  YourTable
        (id, col1, col2)
select  1, 'a', 'b'
where   not exists
        (
        select  *
        from    YourTable yt2
        where   yt2.id = 1
        )

if @@rowcount = 0
    -- Duplicate key!

Answer 3

Fortunately, you don't have to parse the text. A SqlException has a Number property. Oberserve, what number is being returned and match on it. The numbers are well-defined. You can even look at all of them in one of the system catalog tables (don't have the name handy).

It is a very good idea of yours to catch only a very specific error instead of just catching all SqlExceptions (for example).