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

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