如果连接到SQL Server失败，则如何从SQLEXCEPTION中知道

Question

我正在我的ASP.NET应用中的第三方DLL调用方法。此方法读取并写入SQL Server数据库。

当SQL Server服务不运行时，上述方法调用将抛出SQLEXCEPTION，其详细信息如下。

我的问题是如何确定发生错误，因为无法建立与SQL Server的连接。如果该异常称为SQLConnectionFaileDexception或更具体的连接连接，则很容易，因为我只能在捕获块中检查类型的异常。Sqlexception太通用了。

一种解决方案是我可以检查sqlexception的消息是否包含字符串A network-related or instance-specific error occurred while establishing a connection to SQL Server，但可能有更好的方法来解决此类问题。

问题：我怎么能在捕获块中知道发生错误，因为无法建立与SQL Server数据库的连接？

当SQL Server服务未运行

时，会引发异常

  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＃C＃当SQL Server服务未运行

时失败的代码

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
}

Answer 1

而不是检查字符串，您可以检查错误号码。这可能是最好的解决方案。

您可以使用此SQL查询来获取所有可能的错误代码：

SELECT * FROM sysmessages

然后，您应该使用开关工作，以获取正确的错误并以不同的方式处理它们。

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是SqlException.Errors[1].Number的包装器。SqlException.Errors中第一个错误的错误数

还要确保在捕获异常时登录足够的数据，这可以为您节省很多时间。

您还应该检查以下问题：

sqlexception捕获和处理

知道从C＃？

调用SQL Server时何时重试或失败

这些应该使您很远。