C# SQL Server 的超时设置
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1354271/
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
Timeout setting for SQL Server
提问by George2
I am using VSTS 2008 + ADO.Net + C# + .Net 3.5 + SQL Server 2008. I am using ADO.Net at client side to connect to database server to execute a store procedure, then return result from the store procedure.
我正在使用 VSTS 2008 + ADO.Net + C# + .Net 3.5 + SQL Server 2008。我在客户端使用 ADO.Net 连接到数据库服务器以执行存储过程,然后从存储过程返回结果。
Here is my code. I have two issues about timeout,
这是我的代码。我有两个关于超时的问题,
If I do not explicitly set any timeout related settings, for the connection to database server, are there any timeout settings (e.g. if can not connect to database server for some default amount of time, there will be some timeout exception?)?
If I do not explicitly set any timeout related settings, for the execution of the store procedure, are there any timeout settings (e.g. if can not retrieve results from server to ADO.Net client for some default amount of time, there will be some timeout exception?)?
using (SqlConnection currentConnection = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Trusted_Connection=true;Asynchronous Processing=true")) { // check current batch conut currentConnection.Open(); using (SqlCommand RetrieveOrderCommand = new SqlCommand()) { RetrieveOrderCommand.Connection = currentConnection; RetrieveOrderCommand.CommandType = CommandType.StoredProcedure; RetrieveOrderCommand.CommandText = "prc_GetOrders"; RetrieveBatchCountCommand.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output; RetrieveBatchCountCommand.ExecuteNonQuery(); int rowCount = Convert.ToInt32(RetrieveOrderCommand.Parameters["@Count"].Value); } }
如果我没有明确设置任何与超时相关的设置,对于连接到数据库服务器,是否有任何超时设置(例如,如果在某些默认时间内无法连接到数据库服务器,会出现一些超时异常?)?
如果我没有明确设置任何与超时相关的设置,对于存储过程的执行,是否有任何超时设置(例如,如果在某些默认时间内无法从服务器检索结果到 ADO.Net 客户端,则会有一些超时例外?)?
using (SqlConnection currentConnection = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Trusted_Connection=true;Asynchronous Processing=true")) { // check current batch conut currentConnection.Open(); using (SqlCommand RetrieveOrderCommand = new SqlCommand()) { RetrieveOrderCommand.Connection = currentConnection; RetrieveOrderCommand.CommandType = CommandType.StoredProcedure; RetrieveOrderCommand.CommandText = "prc_GetOrders"; RetrieveBatchCountCommand.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output; RetrieveBatchCountCommand.ExecuteNonQuery(); int rowCount = Convert.ToInt32(RetrieveOrderCommand.Parameters["@Count"].Value); } }
回答by gbn
Yes, there are 2 kinds of timeout that can be set
是的,可以设置 2 种超时
Both default to 30 seconds in VBA, .net etc
在 VBA、.net 等中都默认为 30 秒
回答by marc_s
As gbn already mentioned, there are two types of timeouts:
正如 gbn 已经提到的,有两种类型的超时:
1) Connection Timeout: this is controlled by your connection string:
1)连接超时:这由您的连接字符串控制:
Data Source=.;Initial Catalog=TestDB;
Trusted_Connection=true;Asynchronous Processing=true
If you add a Connect Timeout=120
to this string, your connection will try for 120 seconds to get opened and then aborts.
如果您Connect Timeout=120
向该字符串添加 a ,您的连接将尝试 120 秒打开然后中止。
Data Source=.;Initial Catalog=TestDB;
Trusted_Connection=true;Asynchronous Processing=true;
Connect Timeout=120;
2) Command timeout: for each command, you can also specify a timeout - ADO.NET will wait for that amount of time before cancelling out your query. You specify that on the SqlCommand object:
2) 命令超时:对于每个命令,您还可以指定超时 - ADO.NET 将在取消查询之前等待该时间量。您在 SqlCommand 对象上指定:
using (SqlCommand RetrieveOrderCommand = new SqlCommand())
{
RetrieveOrderCommand.CommandTimeout = 150;
}
回答by user3775101
In sqlconnection
class, there is a property by name ConnectionTimeout
.
在sqlconnection
课堂上,有一个属性 name ConnectionTimeout
。
This cannot be directly used to set desired connection timeout value as it is readonly i.e. only "get" is implemented & "set" is not implemented on this property.So we have to use keyword "Connection Timeout" in the connection string itself & set the desired value.
这不能直接用于设置所需的连接超时值,因为它是只读的,即仅实现了“get”而未在此属性上实现“set”。因此我们必须在连接字符串本身中使用关键字“Connection Timeout”并设置所需的值。
exi:
出口:
Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";(30 means 30 seconds)
Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";(30 means 30 seconds)
30 seconds is the maximum time given to establish connection to the server(like 172.160.0.2
OR something like ADMINISTRATOR\\SQLEXPRESS
).If it could not immediately establish connection to server , then it will try up to 30 seconds.
30 秒是与服务器建立连接的最长时间(如172.160.0.2
OR 之类的ADMINISTRATOR\\SQLEXPRESS
)。如果无法立即建立与服务器的连接,则最多尝试 30 秒。
If server is valid & is able to connect to server & if database name or login credentials are invalid , then this timeout won't be applicable.It immediately throws exception for invalid credentials or database
如果服务器有效并且能够连接到服务器并且数据库名称或登录凭据无效,则此超时将不适用。它立即为无效凭据或数据库抛出异常