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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-06 15:32:00  来源:igfitidea点击:

Timeout setting for SQL Server

c#.netsql-servervisual-studio-2008ado.net

提问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,

这是我的代码。我有两个关于超时的问题,

  1. 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?)?

  2. 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);
            }
        }
    
  1. 如果我没有明确设置任何与超时相关的设置,对于连接到数据库服务器,是否有任何超时设置(例如,如果在某些默认时间内无法连接到数据库服务器,会出现一些超时异常?)?

  2. 如果我没有明确设置任何与超时相关的设置,对于存储过程的执行,是否有任何超时设置(例如,如果在某些默认时间内无法从服务器检索结果到 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 种超时

  1. Connection timeout
  2. Command timeout
  1. 连接超时
  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=120to 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 sqlconnectionclass, 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.2OR 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.2OR 之类的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

如果服务器有效并且能够连接到服务器并且数据库名称或登录凭据无效,则此超时将不适用。它立即为无效凭据或数据库抛出异常