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

my question is how to handle s开发者_Go百科ql 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?

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 == ...)
{
    ...
}

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");
}

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.

Catch SqlException

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

    }
}

The general-purpose exception class is DbException, but you'd be limited to parsing the error message text.

For finer-grained handling, you'll need to catch the provider-specific implementation of DbException for the specific DB you are using. These usually provide access to the error code and other details. Here are some common ones:

• For SQL Server DB, SqlException
• For Oracle DB, OracleException
• For MySql DB, MySqlException
• For SQLite DB, SQLiteException