Catching specific exception

Question

How can I catch specific exception using c# ?
In my database there is unique index on some columns.
when user inserts duplicate record this exception has been throw :

Cannot insert duplicate key row in object 'dbo.BillIdentity' with unique index 'IX_BillIdentity'. The statement has been terminated.

How can I catch this exception?
Currently I am checking using this code :

 catch (Exception ex) { if (ex.Message.Contains("Cannot insert duplicate key row in object 'dbo._BillIdentity' with unique index 'IX__BillIdentity")) { string ScriptKey = "$(function() {ShowMessage('Error');});"; ScriptManager.RegisterStartupScript(Page, GetType(), "script", ScriptKey, true); } }

I think its bad smell code.
Is there any better way?

Why don't you check if the key exists before trying to run the insert statement? — Johann Blais
– Johann Blais, Commented May 25, 2011 at 6:47
Avoiding the crappy exception-based workaround you are asking for ;) — Johann Blais
– Johann Blais, Commented May 25, 2011 at 7:11
Checking first to see if the key exists is not foolproof. Who's to say that, between your check and your insert, someone else didn't execute an insert of their own? If they did, there's no guarantee the results of your pre-check are still valid, as that other person might have inserted the same key you are about to attempt, and you will still get the exception. — RSW
– RSW, Commented Apr 11, 2016 at 12:54

Alex Aza · Accepted Answer · 2012-05-06 23:33:14Z

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.

hello there Alex, great answer. Exactly what I i'm looking for Except it's for a different exception. Are the references about these exception numbers?
@LuukKrijnen - you can query sys.messages for those error codes.
@AlexAza Could I detect this way on which column violation occurred?... I may have more unique keys on one table and want to catch each of them separately
For my application it was 2627. Using Server 2014. Also I use Linq to SQL, maybe that accounts for the number difference.
2627 happens for a constraint violation (Primary Key). You can see this additional message by changing the mentioned query to select * from sys.messages where text like '%Cannot insert duplicate key%' (% at start of string)

Jon Skeet · Accepted Answer · 2011-05-25 06:33:10Z

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; } }

thanks , I'm handling this error In presentation layer , I can't Rethrow exception. Where am I wrong?
@Shaah, If you can't rethrow then why ask about catching a specific exc? You seem to want to handle it differently.
@shaahin: You probably should rethrow any errors you can't actually handle, and then have a consolidated error handling part of the presentation layer to give an appropriate error page.
@JonSkeet: Could I detect this way on which column violation occurred?... I may have more unique keys on one table and want to catch each of them separately

Tom Tom · Accepted Answer · 2013-05-14 15:07:28Z

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.

Will Menton · Accepted Answer · 2019-01-25 20:39:07Z

As documented here, you can use exception filters. Example:

try { /* your code here */ } catch (SqlException sqlex) when (sqlex.Number == 2627) { /* handle the exception */ }

Edwin · Accepted Answer · 2019-04-09 10:33:37Z

Working code for filter only duplicate primary key violation exception

using System.Data.Entity.Infrastructure; using System.Data.SqlClient; try { abc... } catch (DbUpdateException ex) { if (ex.InnerException.InnerException is SqlException sqlEx && sqlEx.Number == 2601) { return ex.ToString(); } else { throw; } }

Note fine detail :- ex.InnerException.InnerException not ex.InnerException

Mr47 · Accepted Answer · 2011-05-25 06:33:32Z

You could only catch the SqlException for starters

catch (SqlException ex) { if (ex.Message.Contains("Cannot insert duplicate key row in object 'dbo._BillIdentity' with unique index 'IX__BillIdentity")) { string ScriptKey = "$(function() {ShowMessage('Error');});"; ScriptManager.RegisterStartupScript(Page, GetType(), "script", ScriptKey, true); } }

Collectives™ on Stack Overflow

Catching specific exception

6 Answers 6

5 Comments

4 Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

4 Comments

Comments

Comments

Comments

Comments

Related