How to Identify the primary key duplication from a SQL Server 2008 error code?

Question

I want to know how we identify the primary key duplication error from SQL Server error code in C#.

As a example, I have a C# form to enter data into a SQL Server database, when an error occurs while data entry, how can I identify the reason for the error from the exception?

score 68 · Accepted Answer · edited Nov 27 '17 at 15:44

68

If you catch SqlException then see its number, the number 2627 would mean violation of unique constraint (including primary key).

try
{
    // insertion code
}
catch (SqlException ex)
{
    if (ex.Number == 2627)
    {
        //Violation of primary key. Handle Exception
    }
    else throw;
}

MSSQL_ENG002627

This is a general error that can be raised regardless of whether a database is replicated. In replicated databases, the error is typically raised because primary keys have not been managed appropriately across the topology.

edited Nov 27 '17 at 15:44

huysentruitw

27,376
9
90
133

answered Mar 05 '13 at 07:06

Habib

219,104
29
407
436

4

@Roshan You have to use `SqlException`, not `System.Exception`, if you use them both, make sure that more specific exception has its catch block above the less specific exception (in this case catch block of `SqlException` has to be above the catch block of `Exception`). – Ivan Golović Mar 05 '13 at 07:20
5

I believe 2601 can also be a unique index violation – Brain2000 Jul 14 '15 at 16:06
2

also worth noting that you can use exception filtering so you end up with: `try{ //insertion code } catch(SqlException ex) when (ex.Number == 2627){ //do something }` – MJJames Oct 26 '16 at 10:51

score 10 · Answer 2 · edited Nov 27 '17 at 15:42

This is an old thread but I guess it's worth noting that since C#6 you can:

try
{
    await command.ExecuteNonQueryAsync(cancellation);
}
catch (SqlException ex) when (ex.Number == 2627)
{
    // Handle unique key violation
}

And with C#7 and a wrapping exception (like Entity Framework Core):

try
{
    await _context.SaveChangesAsync(cancellation);
}
catch (DbUpdateException ex) 
   when ((ex.InnerException as SqlException)?.Number == 2627)
{
    // Handle unique key violation
}

The biggest advantage of this approach in comparison with the accepted answer is:

In case the error number is not equal to 2627 and hence, it's not a unique key violation, the exception is not caught.

Without the exception filter (when) you'd better remember re-throwing that exception in case you can't handle it. And ideally not to forget to use ExceptionDispatchInfo so that the original stack is not lost.

usefulBee · Answer 3 · 2017-08-17T18:08:31.663

In case of Entity Framework, the accepted answer won't work and the error will end up not being caught. Here is a test code, only the entity catch statement will be hit or of course the generic exception if entity statement removed:

try
{
    db.InsertProcedureCall(id);
}
catch (SqlException e0)
{
   // Won't catch
}
catch (EntityCommandExecutionException e1)
{
    // Will catch
    var se = e1.InnerException as SqlException;
    var code = se.Number;
}
catch (Exception e2)
{
   // if the Entity catch is removed, this will work too
    var se = e2.InnerException as SqlException;
    var code = se.Number;
}

score 0 · Answer 4 · answered Apr 09 '19 at 09:31

Working code for filter only duplicate primary key voilation 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 detial :- ex.InnerException.InnerException not ex.InnerException

How to Identify the primary key duplication from a SQL Server 2008 error code?

4 Answers4

Linked

Related