CODEWITHSUNDEEP
c#asp.netsqlentity-framework
anthonypliu
anthonypliu

Reputation: 12437

How to handle exception from specific database error

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;

Upvotes: 4

Views: 4059

Answers (4)

Ivan Golović
Ivan Golović

Reputation: 8832

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

Upvotes: 7

m4ngl3r
m4ngl3r

Reputation: 560

you can check exception text or it's other parameters when it is thrown, so then you can act like you wan conditionally

like :

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

   } 
}

or exception number like 

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

Upvotes: 0

A Developer
A Developer

Reputation: 1031

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

Upvotes: 2

laszlokiss88
laszlokiss88

Reputation: 4071

You can catch it by its type:

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

EDIT: According to Ivan G's answer, you will get an SqlException, which has an error ErrorCode property that probably specific. So you have to check the error code for this type of error.

Upvotes: 0

Related Questions