How to know from SqlException if connecting to SQL Server failed?

Question

I am calling a method from a third-party dll in my ASP.Net app. This method reads and writes to a SQL Server database.

When the SQL Server service is not running, then the above method call throws a SqlException whose details are as below.

My problem is how I can determine that the error occurred because connection to SQL Server could not be established. If the exception was called SqlConnectionFailedException or something more specific for failed connections then it would be easy since I could just check for type of Exception in the catch block. A SQLException is too generic an exception.

One solution is that I could check if message of SqlException contains the string A network-related or instance-specific error occurred while establishing a connection to SQL Server but may be there is a better way to solve this type of problem.

Question: How can I know in catch block that error occurred because connection to SQL Server database could not be established?

Exception thrown when SQL Server service is not running

  System.Data.SqlClient.SqlException occurred
  _HResult=-2146232060
  _message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)
  HResult=-2146232060
  IsTransient=false
  Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  _doNotReconnect=false
  Class=20
  LineNumber=0
  Number=2
  Server=""
  State=0
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
  InnerException: System.ComponentModel.Win32Exception
       _HResult=-2147467259
       _message=The system cannot find the file specified
       HResult=-2147467259
       IsTransient=false
       Message=The system cannot find the file specified
       ErrorCode=-2147467259
       NativeErrorCode=2
       InnerException:

C# code that fails when SQL Server service is not running

try {
 //call a method from third-party dll that reads/writes to a SQL Server database
 Hangfire.GlobalConfiguration.Configuration.UseSqlServerStorage("Jobs");
}
catch(SqlException sqlEx) {
 //I want to know if error was due to not being to connect to SQL Server database
}
catch(Exception ex) {
 //some custom error handling logic
}

Can you obtain the Connection object prior to calling the method that does read/write? — ahoxha, May 04 '17 at 05:46
I am calling a method from a third-party dll, so I cannot obtain the Connection object being used by this thrird-party dll. — Sunil, May 04 '17 at 05:49
@ahoxha reading the docs is better that trial and error. And trying to access the SqlConnection object is pointless - a closed connection can't tell you *why* it couldn't open. — Panagiotis Kanavos, May 04 '17 at 07:01
@ahoxha besides, the exception posted by the OP shows that `Number` is 2, not 0 in this case. The error code is -2146232060 — Panagiotis Kanavos, May 04 '17 at 07:03

score 2 · Answer 1 · edited May 23 '17 at 11:47

Instead of checking the string, you could check the error numbers. This would probably be the best solution.

You can use this SQL Query to get all the possible error Codes:

SELECT * FROM sysmessages

You should then work with a switch, case to get the right errors and handle them differently.

try    
{
    // Your Hangfire / SQL Call
}
catch (SqlException ex)
{
     switch (ex.Number) 
     { 
          case 262: //%ls permission denied in database '%.*ls'.
                //Do something
                break;
          default:
                //Do something
                break;
     }
}

SqlException.Number is the wrapper for SqlException.Errors[1].Number eg. the error-number of the first error in SqlException.Errors

Also make sure to log enough data when catching exceptions, this can save you a lot of time in the future.

You should also check these questions:

SqlException catch and handling

Know when to retry or fail when calling SQL Server from C#?

These should get you quite far.

How to know from SqlException if connecting to SQL Server failed?

1 Answers1