如何捕获SQLServer超时异常-HowtocatchSQLServertimeoutexceptions

134  

To check for a timeout, I believe you check the value of ex.Number. If it is -2, then you have a timeout situation.

为了检查超时，我相信您检查了ex.Number的值。如果是-2，就会出现超时情况。

-2 is the error code for timeout, returned from DBNETLIB, the MDAC driver for SQL Server. This can be seen by downloading Reflector, and looking under System.Data.SqlClient.TdsEnums for TIMEOUT_EXPIRED.

-2是用于SQL Server的MDAC驱动程序DBNETLIB返回的超时错误代码。这可以通过下载Reflector和查看System.Data.SqlClient查看。TdsEnums TIMEOUT_EXPIRED。

Your code would read:

你的代码会读:

if (ex.Number == -2)
{
     //handle timeout
}

Code to demonstrate failure:

代码来演示失败:

try
{
    SqlConnection sql = new SqlConnection(@"Network Library=DBMSSOCN;Data Source=YourServer,1433;Initial Catalog=YourDB;Integrated Security=SSPI;");
    sql.Open();

    SqlCommand cmd = sql.CreateCommand();
    cmd.CommandText = "DECLARE @i int WHILE EXISTS (SELECT 1 from sysobjects) BEGIN SELECT @i = 1 END";
    cmd.ExecuteNonQuery(); // This line will timeout.

    cmd.Dispose();
    sql.Close();
}
catch (SqlException ex)
{
    if (ex.Number == -2) {
        Console.WriteLine ("Timeout occurred");
    }
}

13  

here: http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-10/msg00064.html

You can read also that Thomas Weingartner wrote:

这里:http://www.techarchive.net/archive/dotnet/microsoft.public.dotnet.framework.adonet/2006-10/msg00064.html

Timeout: SqlException.Number == -2 (This is an ADO.NET error code)
General Network Error: SqlException.Number == 11
Deadlock: SqlException.Number == 1205 (This is an SQL Server error code)

超时:SqlException异常。Number == -2(这是一个ADO。一般网络错误:SqlException。数字== 11死锁:SqlException。Number == 1205(这是一个SQL服务器错误代码)

...

…

We handle the "General Network Error" as a timeout exception too. It only occurs under rare circumstances e.g. when your update/insert/delete query will raise a long running trigger.

我们也将“一般网络错误”作为超时异常处理。只有在极少数情况下才会发生，例如，当您的更新/插入/删除查询将引发一个长时间运行的触发器。

1  

Whats the value for the SqlException.ErrorCode property? Can you work with that?

SqlException的值是多少。ErrorCode财产吗?你能解决这个问题吗?

When having timeouts, it may be worth checking the code for -2146232060.

当有超时时，检查-2146232060的代码是值得的。

I would set this up as a static const in your data code.

我将它设置为数据代码中的静态const。