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.

If you are inserting to SQL table using loop you are doing it wrong — Tzah Mama, Jul 14 '14 at 15:45
It's a bit harsh to say "wrong" without knowing the scenario, but there is likely a better way to do it — DavidG, Jul 14 '14 at 15:46
Why's it wrong or inefficient? Link me to some literature on the correct way to do it. I'm still learning. Thanks. — user609926, Jul 14 '14 at 15:51
How do I avoid the repeated server calls? I use ADO.Net so are you talking SqlBulkCon instead? I usually wrap the loop in a using statement with a sqlconnection object. If there's a more efficient way can someone please provide links? It would be greatly appreciated. — user609926, Jul 14 '14 at 16:01

score 33 · Accepted Answer · edited May 23 '17 at 10:29

33

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

edited May 23 '17 at 10:29

Community

1
1

answered Jul 14 '14 at 15:44

Bill Sambrone

4,334
4
48
70

1

May not be a PK error, it could be a unique constraint (which is what the OP says in the question) – DavidG Jul 14 '14 at 15:49
as long as it catches a constraint which indicates a duplicate record (which could be either) that's good enough for me. I did a little more research and according to sys.messages table the message is 'Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.' which I think covers me in either case. Both of your posts were helpful though. – user609926 Jul 14 '14 at 16:08
4

Check also for `ex.Number == 2601` https://stackoverflow.com/questions/31515776/how-can-i-catch-uniquekey-violation-exceptions-with-ef6-and-sql-server – Jaider Sep 17 '17 at 20:35

score 20 · Answer 2 · answered Jul 21 '18 at 11:32

In addition of Bill Sambrone's answer,

Two error codes are used to check unique key violation

2601 - Violation in unique index
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...
   }
}

score 2 · Answer 3 · edited May 23 '17 at 12:18

2

You could wrap the insert into a stored procedure that first validated there was no duplicates before inserting. That way, you can control exactly what comes back when the value is duplicated.

Additionally, you may find that shifting the insert logic into an SP will allow you do do the bulk insert that you appear to be doing without making repeated calls the the DB.

For an answer to your actual question:

Unique Key Violation in SQL Server - Is it safe to assume Error 2627?

edited May 23 '17 at 12:18

Community

1
1

answered Jul 14 '14 at 15:45

Paul Michaels

16,185
43
146
269

Everyone reading this should look at the accepted answer in the [link posted in this answer](http://stackoverflow.com/questions/6483699/unique-key-violation-in-sql-server-is-it-safe-to-assume-error-2627). It gives the most insight. – neizan Dec 08 '16 at 14:47

score 1 · Answer 4 · answered Jul 14 '22 at 11:04

Here is an alternative using C# 6.0 Exception Filters. The syntax is more concise, easier to maintain and has other advantages as per the article.

try
{
    // Insert record
}
catch (SqlException ex) when (ex.Number == 2601)
{
    // Violation in unique index (record already exists)
}
catch (SqlException ex) when (ex.Number == 2627)
{
    // Violation in unique constraint
}

score 0 · Answer 5 · answered May 26 '16 at 14:25

0

You could do it this way as well:

var result = DbCon.CheckForPrimaryKey(value).ToList();
if(result.Count() == 0)
       DbCon.InsertValue(value);
else
     // Do Nothing

answered May 26 '16 at 14:25

usefulBee

9,250
10
51
89

score 0 · Answer 6 · answered May 11 '22 at 23:04

If in the excepted answer, you are not getting the Exception from db.SaveChanges(), then it means you have not defined constraint properly while creating the model.

LINQ don't through Unique Key Constraint if you don't define the constraints properly while creating the Model.

Must check this answer to know, how you can define the constraints in EF Core or EF 6: https://stackoverflow.com/a/72208419/4393351

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

6 Answers6

Linked