CODEWITHSUNDEEP
c#sql-servertry-catch
MadBoy
MadBoy

Reputation: 11104

Using Unique Key in SQL with or without try/catch as a valid way to verify integrity of data

I have a table with some columns and I've set Unique Key on 3 of those columns to make sure there are no duplicates in the table. Now I was wondering if using try / catch to swallow exceptions throw on duplicates and continue updating inserting next rows in foreach is a good way to go?

            try {
                sqlWrite.ExecuteNonQuery();
            } catch (SqlException sqlException) {
                if (!sqlException.ToString().Contains("Violation of UNIQUE KEY constraint")) {
                    MessageBox.Show("Error - " + Environment.NewLine + sqlException.ToString(), "Error SQL");
                }
            } catch (Exception exception) {
                MessageBox.Show("Error - " + Environment.NewLine + exception.ToString(), "Error SQL");
            }

Or should I do SELECT inside insert query to check if row exists and if it does skip insert? I have read that it's not good to use exceptions as part of your data verification but some things are supposed to be used like that (for example How to check For File Lock in C#? is supposed to be used with try/catch).

Upvotes: 2

Views: 909

Answers (2)

JotaBe
JotaBe

Reputation: 39025

You should never throw and catch exceptions if you have an alternative way to do it. ANd you now you have it. Use it!

You can create a single sql script or stored procedure that checks and inserts with IF NOT EXISTS(SELECT ...). Making two queries is slower.

Upvotes: 1

Brian Hoover
Brian Hoover

Reputation: 7991

I would generally say that the primary key relationship should be the absolute last and fall back position for data verification. If your program has gotten that far and is trying to insert a row that fails unique key, something has gone very wrong.

In the most simple case, it's not too bad. For inserting a single row, without any other dependencies, you can make an argument that trying to insert the record and throwing a caught error might be more efficient then doing a select to see if the ID already exists.

Now, think about a transaction where you might be inserting/updating 10s or even hundreds of rows at a single time. Every one of the rows has a unique key, and any of them might throw an error. Then the DB has to roll back the whole transaction when an error is caught. Sure C# HAS a transaction mode, but you then have to roll back the DB and roll back any objects that might have been updated when inserting the records. It get's very complicated very fast.

Upvotes: 0

Related Questions