c# - 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.

Answer 1

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;    

       } 
}