how to handle db exception in c#

Question

Hi, 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

Catch SqlException

catch(SqlException ex)
{
    foreach(SqlError error in ex.Errors)
    {

    }
}

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

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. In VB.NET, you could use conditional catch:

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

In C#, you will have to resort to

try {
    ...
} catch (SqlException ex) {
    if (ex.Number == ...) {
        ...
    } else if (ex.Number == ...) {
        ...
    } else {
       throw;   // re-throw exception instead of handling it
    }
}

Answer 4

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.