How do I handle specific SQL exceptions (for example, a unique constraint violation) in C#?

Question

my question is how to handle sql exception in c#, is there anyway to check what kind of the sql exception throws from data access layer? For example, if db throws an unique constraint exception, or foreign key exception, is there any way to catch it from c#? what's the exception handling pattern you are using for these db exception?

Answer 1

Have a look at the documentation of the SqlException class, in particular, at its properties: SqlException.Number, for example, should allow you to identify which type of SqlException (unique constraint, foreign key, ...) occurred.

You can use filtered exceptions to catch specific errors. VB.NET:

Try
    ...
Catch ex as SqlException When ex.Number = ...
    ...
Catch ex as SqlException When ex.Number = ...
    ...
End Try

C# (Version 6 and above):

try
{
    ...
}
catch (SqlException ex) when (ex.Number == ...)
{
    ...
}
catch (SqlException ex) when (ex.Number == ...)
{
    ...
}

Answer 2

you can check message text,Number and do switch case on it to know the error...

try {
}
catch (SqlException ex)
{
 string str;
        str = "Source:"+ ex.Source;        
        str += "\n"+ "Number:"+ ex.Number.ToString();
        str += "\n"+ "Message:"+ ex.Message;
        str += "\n"+ "Class:"+ ex.Class.ToString ();
        str += "\n"+ "Procedure:"+ ex.Procedure.ToString();
        str += "\n"+ "Line Number:"+ex.LineNumber.ToString();
        str += "\n"+ "Server:"+ ex.Server.ToString();

        Console.WriteLine (str, "Database Exception");
}

Answer 3

It depends on the exception and your database backend. You database will produce a unique error code for the specific things like constraints, permissions, etc. but that error code varies from DB to DB. Oracle has a MASSIVE pdf (2000+ pages) that lists every possible error it can throw and I am sure Sqlserver has something similar. My point is you looking for specific error codes then you need to trp just those in the catch section and handle them differently then the multitude of other errors you can get.