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?

Answer 1

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.

Answer 2

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.