Best way to catch sql unique constraint violations in c# during inserts

Question

I have a loop in c# that inserts into a table. pretty basic stuff. Is there something insdie the exception object that's thrown when a unique constraint is violated that i can use to see what the offending value is?

Or is there a way to return it in the sql? i have a series of files whose data im loading into tables and i'm banding my head trying to find the dupe.

I know I could slap together something purely IO-based in code that can find it but I'd like something I could use as a more permanent solution.

Answer 1

What you are looking for is a SqlException, specifically the violation of primary key constraints. You can get this specific error out of this exception by looking at the number property of the exception thrown. This answer is probably relevant to what you need: How to Identify the primary key duplication from a SQL Server 2008 error code?

In summary, it looks like this:

// put this block in your loop try {    // do your insert } catch(SqlException ex) {    // the exception alone won't tell you why it failed...    if(ex.Number == 2627) // <-- but this will    {       //Violation of primary key. Handle Exception    } } 

EDIT:

This may be a bit hacky, but you could also just inspect the message component of the exception. Something like this:

if (ex.Message.Contains("UniqueConstraint")) // do stuff 

Answer 2

In addition of Bill Sambrone's answer,

Two error codes are used to check unique key violation

1. 2601 - Violation in unique index
2. 2627 - Violation in unique constraint (although it is implemented using unique index)

Either you can use one or both according to your needs:

try {  } catch(SqlException ex) {    if(ex.Number == 2601)     {       // Violation in unique index    }    else if(ex.Number == 2627)    {       // Violation in unique constraint    } } 

OR

try {  } catch(SqlException ex) {    if(ex.Number == 2601 || ex.Number == 2627)    {       // Violation in one on both...    } }