How to handle exception from specific database error

Question

I am trying to create a transaction like so:

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 if I try to insert an item manully in sql with a duplicate in a specific column, I get the following error from sql:

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

How can I programatically catch this exception specifically, so I can act upon it.

This is the constraint I put on my column:

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

Answer 1

Try this:

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

SQL errors and warnings that happen on the server side are caught in this exception. Read about it here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception(v=vs.110).aspx

The above answer would allow you to catch the SqlException, but you would need to further refine the handling within the 'SqlException' catch block if you only want to inform the user of a particular error. The SqlException class has a property of 'ErrorCode' from which you can derive the actual error being produced by the server. Try doing something like below:

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

2601 is the actual error code produced by SQL Server for you particular error. For a full list just run the SQL:

SELECT * FROM sys.messages

Answer 2

Use SqlException's number property.

For duplicate error the number is 2601.

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

List of error codes

 SELECT * FROM sysmessages