How to handle an exception from a specific database error

Question

I am trying to create a transaction as follows:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { try { dbContext.MyTable.PartnerId = someGuid; dbContext.SaveChanges(); scope.Complete(); dbContext.AcceptAllChanges() } catch (Exception ex) { log.LogMessageToFile("Exception - ExceptionType: " + ex.GetType().ToString() + "Exception Messsage: " + ex.Message); } } 

I know that if I try to insert a manully element in sql with a duplicate in a specific column, I get the following error from sql:

Cannot insert duplicate key string in object 'dbo.MyTable' with unique index 'idx_PartnerId_notnull'. The duplicate key value is (7b072640-ca81-4513-a425-02bb3394dfad).

How can I programmatically catch this exception, so I can act on it.

This is the limitation that I put in my column:

 CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull ON YourTable(yourcolumn) WHERE yourcolumn IS NOT NULL; 

Answer 1

Try the following:

 try { } catch (SqlException sqlEx) { } catch (Exception ex) { } 

SQL errors and server-side warnings fall into this exception. Read about it here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception(v=vs.110).aspx

The above answer will allow you to catch a SqlException, but you will need to further refine the processing in the catch block of SqlException if you want to inform the user about a specific error. The SqlException class has the ErrorCode property, from which you can get the actual error generated by the server. Try to do something like:

 try { } catch (SqlException sqlEx) { if(sqlEx.ErrorCode == 2601) { handleDuplicateKeyException(); } } 

2601 is the actual error code generated by SQL Server for the specific error. For a complete list, just run SQL:

SELECT * FROM sys.messages

Answer 2

Use the SqlException number property.

For a repeating error, the number is 2601.

 catch (SqlException e) { switch (e.Number) { case 2601: // Do something. break; default: throw; } } 

Error Code List

  SELECT * FROM sysmessages 

Answer 3

You can catch it by type:

  try { // ... } catch (SpecialException ex) { } catch (Exception ex) { } 

EDIT: According to Ivan G, you will get a SqlException that has the ErrorCode property, which is probably specific. Therefore, you should check the error code for this type of error.

Answer 4

you can check the exception text or other parameters when throwing it, so you can act as if you are conditionally

like:

 catch(SqlException ex) { if(ex.Message.Contains("Cannot insert duplicate key row in object")) { } } 

or exception number, for example

 catch(SqlException ex) { switch (ex.Number) { case : someNumber: { //..do something break...; } } }