C# 如何捕获 SQLServer 超时异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to catch SQLServer timeout exceptions
提问by brodie
I need to specifically catch SQL server timeout exceptions so that they can be handled differently. I know I could catch the SqlException and then check if the message string Contains "Timeout" but was wondering if there is a better way to do it?
我需要专门捕获 SQL 服务器超时异常，以便可以不同地处理它们。我知道我可以捕获 SqlException，然后检查消息字符串是否包含“超时”，但想知道是否有更好的方法来做到这一点？
try
{
//some code
}
catch (SqlException ex)
{
if (ex.Message.Contains("Timeout"))
{
//handle timeout
}
else
{
throw;
}
}
采纳答案by Jonathan
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");
}
}
回答by Rob Cooper
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.
我会在您的数据代码中将其设置为静态常量。
回答by Roland Pihlakas
here: http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-10/msg00064.html
You can read also that Thomas Weingartnerwrote:
此处：http: //www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-10/msg00064.html
您还可以阅读Thomas Weingartner写道：
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.NET 错误代码）
一般网络错误：SqlException.Number == 11
死锁：SqlException.Number == 1205（这是一个 SQL Server 错误代码）
...
...
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.
我们也将“一般网络错误”作为超时异常处理。它仅在极少数情况下发生，例如当您的更新/插入/删除查询将引发长时间运行的触发器时。
回答by John Evans
Updated for c# 6:
为 c# 6 更新：
try
{
// some code
}
catch (SqlException ex) when (ex.Number == -2) // -2 is a sql timeout
{
// handle timeout
}
Very simple and nice to look at!!
非常简单，好看！！
回答by hamid reza Heydari
I am not sure but when we have execute time out or command time out The client sends an "ABORT" to SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released. to solve this problem I Remove transaction in Stored-procedure and use SQL Transaction in my .Net Code To manage sqlException
我不确定，但是当我们执行超时或命令超时时，客户端向 SQL Server 发送“ABORT”，然后简单地放弃查询处理。没有事务回滚，没有锁被释放。为了解决这个问题，我在存储过程中删除事务并在我的 .Net 代码中使用 SQL 事务来管理 sqlException
回答by Srinivas Pakalapati
When a client sends ABORT, no transactions are rolled back. To avoid this behavior we have to use SET_XACT_ABORT ON https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15
当客户端发送 ABORT 时，不会回滚任何事务。为了避免这种行为，我们必须在https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15上使用 SET_XACT_ABORT
