Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

exception

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?

like image 972
user192415 Avatar asked Nov 03 '09 11:11

user192415


3 Answers

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 == ...)
{
    ...
}
like image 70
Heinzi Avatar answered Oct 14 '22 01:10

Heinzi


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");
}
like image 38
Wael Dalloul Avatar answered Oct 14 '22 02:10

Wael Dalloul


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.

like image 28
mcauthorn Avatar answered Oct 14 '22 02:10

mcauthorn