Catching SQL unique key exceptions in .NET

Question

I was wondering if anyone had a more elegant way to check for unique key exceptions from SQL in .NET other than parsing the error message? Right now I am calling the sproc in SQL, then using a try catch block in .NET. In the try catch block I parse the error message and it is a Unique key error I throw an instance of a custom error to the calling class, if not I just throw the original exception the the calling class. This seems awfully inefficient to me.

Answer 1

Obvious answer: Attempt to fetch that value from the database before inserting (or updating) it and notify the user before you do the insert.

Alternative answer: Check for the uniqueness inside the SP and return that as an error message from the SP, then you don't have to parse any errors. They're all bad.

Answer 2

Why don't you just query if the unique id exists ? It's better then getting exception.

Answer 3

If you catch a SqlException, you should be able to enumerate the "Errors" collection holding a number of "SqlError" objects.

The SqlError has among other things properties like "Class" and "Number" - unique key violations are class = 14 and number = 2601. Check for those numbers to find your error precisely.

Those are the same error codes you get when you try to execute the query in SQL Management Studio:

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object ..........

The statement has been terminated.

The "Msg" translates to the "Number" property on SqlError, the "Level" to "Class".

try
{
  _cmd.ExecuteNonQuery();
}
catch(SqlException sqlExc)
{
   foreach (SqlError error in sqlExc.Errors)
   {
      string msg = string.Format("{0}: {1}", error.Number, error.Message);
   }
}

That way, you can easily and EXACTLY identify a "unique constraint violated" error.

Marc

Answer 4

Parsing error message is bad idea. For example if you are using Ms SQL message can be localized (in different language) and you will not find word that you are looking for.

If you are using Ms SQL you should check Number property.