Catching specific exception

Handle SqlException only in this case.

[Edit]

To check duplicate key exception in MS SQL server:

try
{
    // try to insert
}
catch (SqlException exception)
{
    if (exception.Number == 2601) // Cannot insert duplicate key row in object error
    {
        // handle duplicate key error
        return;                  
    }
    else
        throw; // throw exception if this exception is unexpected
}

Edit: Where 2601 come from?

select *
from sys.messages
where text like 'Cannot insert duplicate key%'

Returns:

message_id  language_id severity is_event_logged text
----------- ----------- -------- --------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2601        1033        14       0               Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

Using exception.Number and referencing sys.messages view you can handle any specific MS SQL exception.

I've just picked up a project where someone went down this route:

Catch ex As SqlException
    Select Case ex.Number
            Case 2601
                ...

Note the following (from sys.messages in SQL Server):

2601 - Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.

But what about this..?

2627 - Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'."

I just spent some time tracking down exactly this problem.

And what if we change DB provider? Presumably 2601 is not absolutely universal... This stinks, IMO. And if you are (were) dealing with this in your presentation layer, I think there are bigger questions to ask.

If this must be the mechanism of choice, bury it deep, deep down in the DAL and let a custom Exception percolate up. That way, changes to the data store (or, ideally, this mechanism) have a much more limited area of effect and you can handle the case consistently without any questions in the presentation layer.

I'm currently leaning towards doing a light-weight SELECT for an ID on an open connection and avoiding the exception altogether.

You haven't shown the type of exception which is thrown, but you can catch that specific exception type. For example:

catch (DuplicateKeyException e) {
    ...
}

It's possible that there won't be a specific exception type for just this error - but if you have to catch something fairly general like SqlException you can then look for more details within the class itself. For example in SqlException there's an Errors property where you can look at more detailed information about each of the (possibly multiple) errors at the database side. Each SqlError then has a Number property which will give the type of error. You can always fall back to the message if you absolutely have to, but you then need to be aware of the possibility of the message changing for different cultures etc.

Note that if you're not really handling the exception, you should probably rethrow it:

catch (SqlException e) {
    if (CheckWeCanHandle(e)) {
        // Mess with the ScriptManager or whatever
    } else {
        throw;
    }
}