C# 如何延长 SQL 查询的超时时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1111837/
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 extend the timeout of a SQL query
提问by BoltBait
This is not a connection timeout as a connection to the database is made fine. The problem is that the stored procedure that I'm calling takes longer than, say, 30 seconds and causes a timeout.
这不是连接超时，因为与数据库的连接正常。问题是我正在调用的存储过程花费的时间超过 30 秒并导致超时。
The code of the function looks something like this:
该函数的代码如下所示：
SqlDatabase db = new SqlDatabase(connectionManager.SqlConnection.ConnectionString);
return db.ExecuteScalar(Enum.GetName(typeof(StoredProcs), storedProc), parameterValues);
The ExecuteScalar call is timing out. How can I extend the timeout period of this function?
ExecuteScalar 调用超时。如何延长此功能的超时时间？
For quick stored procedures, it works fine. But, one of the functions takes a while and the call fails. I can't seem to find any way to extend the timeout period when the ExecuteScalar function is called this way.
对于快速存储过程，它工作正常。但是，其中一个函数需要一段时间并且调用失败。当以这种方式调用 ExecuteScalar 函数时，我似乎找不到任何方法来延长超时时间。
采纳答案by Chris Porter
If you are using the EnterpriseLibrary (and it looks like you are) try this:
如果您正在使用 EnterpriseLibrary（看起来您是），请尝试以下操作：
Microsoft.Practices.EnterpriseLibrary.Data.Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("ConnectionString");
System.Data.Common.DbCommand cmd = db.GetStoredProcCommand("StoredProcedureName");
cmd.CommandTimeout = 600;
db.AddInParameter(cmd, "ParameterName", DbType.String, "Value");
// Added to handle paramValues array conversion
foreach (System.Data.SqlClient.SqlParameter param in parameterValues)
{
db.AddInParameter(cmd, param.ParameterName, param.SqlDbType, param.Value);
}
return cmd.ExecuteScalar();
Edited to handle the paramValues array directly based on the comments. I also included your ConnectionString value:
编辑以直接根据注释处理 paramValues 数组。我还包括您的 ConnectionString 值：
Microsoft.Practices.EnterpriseLibrary.Data.Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase(connectionManager.SqlConnection.ConnectionString);
System.Data.Common.DbCommand cmd = db.GetStoredProcCommand("StoredProcedureName", parameterValues);
cmd.CommandTimeout = 600;
return cmd.ExecuteScalar();
回答by Mladen Prajdic
you do this by setting the SqlCommand.CommandTimeoutproperty
您可以通过设置SqlCommand.CommandTimeout属性来执行此操作
回答by n8wrl
Mladen is right but if you have to do this you probably have a bigger problem with the proc itself. Under load it might take much longer than your new timeout. Might be worth spending some quality time with the proc to optimize.
Mladen 是对的，但如果您必须这样做，则 proc 本身可能存在更大的问题。在负载下，它可能需要比新超时更长的时间。可能值得花一些时间在 proc 上进行优化。
回答by Ozgur
Try this one
试试这个
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connection.ConnectionString);
connectionStringBuilder.ConnectTimeout = 180;
connection.ConnectionString = connectionStringBuilder.ConnectionString;
connection.Open();
SqlCommand command = new SqlCommand("sp_ProcedureName", connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = connection.ConnectionTimeout;
command.ExecuteNonQuery();
connection.Close();
回答by BSalita
Timeout can occur due to a Microsoft issue. Seems to still occur on my Windows 8 system.
由于 Microsoft 问题，可能会发生超时。在我的 Windows 8 系统上似乎仍然发生。
回答by BSalita
I think this might be a better way to do this (as of Enterprise Library 6.0):
我认为这可能是一个更好的方法（从 Enterprise Library 6.0 开始）：
SqlDatabase db = new SqlDatabase(connectionManager.SqlConnection.ConnectionString);
System.Data.Common.DbCommand cmd = db.GetStoredProcCommand(storedProc, parameterValues);
cmd.CommandTimeout = 600;
return db.ExecuteScalar(cmd);
