调整超时后 C# 中的 SQL Server TimeOut [英] SQL Server TimeOut in C# After Adjusting timeout
问题描述
我有一个用 C# 编写的 Windows 窗体应用程序，它将查询传递到 SQL Server 数据库，然后在数据视图网格中显示结果.传递给数据库的查询取决于表单中选择的选项.
I have a windows form application written in C# that passes a query to a SQL Server database and then displays the results in a dataviewgrid. The query that is passed to the database depends on the option selected in the form.
一个特定的查询在管理工作室中运行需要一分钟多一点的时间，但是当它从程序传递到数据库时会超时.以下是我为尝试解决这种情况而采取的步骤:
One particular query takes a little over a minute to run in management studio, but timeouts when it is passed to the database from the program. here are the steps I have done to try to resolve the situation:
通过在 sql 连接字符串中将连接超时选项设置为 300 秒，在程序中添加了 5 分钟超时.示例:
Data Source=ab;Initial Catalog=abc;User ID=user;密码=pw;连接超时=300
将 SQL Server 实例中的远程查询超时设置为 0(意思是没有超时).示例:EXEC sp_configure '远程查询超时', 0 ;去
Setting the remote query timeout in the SQL Server instance to 0 (meaning, no timeout). Example: EXEC sp_configure 'remote query timeout', 0 ; GO
这些选项都不起作用.尽管实现了它们，但 c# 程序在不到一分钟后就抛出了 sql timeout 错误.
Neither of these options work. Despite implementing both of them, the c# program throws back a sql timeout error after less than a minute.
是否有解决方法?我在堆栈溢出上搜索过这个主题，到目前为止所有的建议都是做 1 或 2(我已经完成了).
Is there a workaround for this? I have searched for this topic on stack overflow and so far all of the suggestions have been to do either 1 or 2 (which I have done).
作为参考，我使用的是 Visual Studio 17 社区版和 SQL Server 2016 开发者版.
For reference, I am using Visual Studio 17 Community edition and SQL Server 2016 Developer edition.
任何帮助将不胜感激.
谢谢！
推荐答案
连接超时和命令超时是两个不同的东西.
连接超时在分配的超时期限内无法从连接池中检索到连接时发生.
A connection timeout occurs when a connection cannot be retrieved from the connection pool within the allotted timeout period.
命令超时发生在检索到连接时，但针对它执行的查询未在分配的命令超时期限内返回结果.ADO.NET 中的默认命令超时时间为 30 秒.
A command timeout occurs when a connection has been retrieved, but the query being executed against it doesn't return results within the allotted command timeout period. The default command timeout period in ADO.NET is 30 seconds.
如果您已将连接超时设置为 300 秒，但仍然出现超时，则可能是命令超时.正如 walkers01 所说，将命令超时设置为合适的秒数.300 秒应该足够了；如果您的查询在一分钟内在 SSMS 中执行，90 秒的超时时间就足够了.
If you've set the connection timeout to 300 seconds and you're still getting a timeout, it's likely a command timeout. As walkers01 said, set your command timeout to a suitable number of seconds. 300 seconds should be far more than sufficient; if your query executes in one minute in SSMS, a timeout of 90 seconds should suffice.
这篇关于调整超时后 C# 中的 SQL Server TimeOut的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
