How to know actual problem because of which SqlException is thrown?

Question

I want to handle different problems, while doing database operations, differently.

e.g. The operation may fail because of wrong database credentials or due to network problem. Or it may fail because the query is not correct (if string value is being passed in the int type column)

In my C# code, we only have SqlException which has collection of SqlErrors. However there are many severity levels.

How can i easily identify the cause of the SqlException ? How can i determine the exception is because of the connectivity problem or authentication failure or because of the problem with the query.

I am using SQL Server 2005.

I tried to reach for a general purpose solution, too. My goal was to present more user-friendly error messages to the end-user. I found no working solution so far. — Uwe Keim, Jan 28 '11 at 06:08

score 10 · Accepted Answer · answered Jan 28 '11 at 06:24

10

Try something like this, this will help you in handling different conditions.

use a try catch block like this:

try    
{
  ...
  ...
}
catch (SqlException ex)
{
  switch (ex.Number) 
    { 
        case 4060: // Invalid Database 
                  ....
                  break;

        case 18456: // Login Failed 

                  ....

                  break;

        case 547: // ForeignKey Violation 

                  ....

                  break;

        case 2627: 
                // Unique Index/ Primary key Violation/ Constriant Violation 

                  ....

                  break;

        case 2601: // Unique Index/Constriant Violation 

                  ....

                  break;

        default: 

                  ....

                  break;    

       } 
}

answered Jan 28 '11 at 06:24

JPReddy

63,233
16
64
93

2

From where did you get these specific numbers for the commented reasons? I want such numbers for a few more reasons like network failuer etc. System table of messages in SQL gives thousands of messages and so that is not quite handy for me. – Learner Jan 28 '11 at 10:17
@CSharpLearner: I got this error messages from various sources such as blogs and earlier code in my project etc. May be you can get these error codes from your database, try this query "SELECT * FROM sysmessages" – JPReddy Jan 28 '11 at 10:29
1

This answer should mention that [`SqlException.Number`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.number.aspx) is just a wrapper for the **first** exception in [`ex.Errors`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.errors.aspx). So you have to loop that collection instead. – Tim Schmelter Mar 16 '13 at 14:58

score 0 · Answer 2 · answered Jan 28 '11 at 06:10

0

SQLException exposes the property Class which should give you the severity level.

More information here.

answered Jan 28 '11 at 06:10

richard

12,263
23
95
151

How to know actual problem because of which SqlException is thrown?

2 Answers2

Linked

Related