SQL Server 的超时设置 [英] Timeout setting for SQL Server

问题描述

我使用的是 VSTS 2008 + ADO.Net + C# + .Net 3.5 + SQL Server 2008.我在客户端使用 ADO.Net 连接到数据库服务器以执行存储过程，然后从存储过程返回结果.

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.

这是我的代码.我有两个关于超时的问题，

Here is my code. I have two issues about timeout,

1. 如果我没有明确设置任何与超时相关的设置，对于连接到数据库服务器，是否有任何超时设置(例如，如果在某些默认时间内无法连接到数据库服务器，则会有一些超时例外?)?

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

如果我没有明确设置任何超时相关设置，对于存储过程的执行，是否有任何超时设置(例如，如果在某些默认时间内无法从服务器检索结果到 ADO.Net 客户端，会有一些超时异常?)?

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);
        }
    }

推荐答案

正如 gbn 已经提到的，有两种类型的超时:

As gbn already mentioned, there are two types of timeouts:

1) 连接超时:这由您的连接字符串控制:

1) Connection Timeout: this is controlled by your connection string:

Data Source=.;Initial Catalog=TestDB;
   Trusted_Connection=true;Asynchronous Processing=true

如果您向该字符串添加 Connect Timeout=120，您的连接将尝试 120 秒打开然后中止.

If you add a Connect Timeout=120 to this string, your connection will try for 120 seconds to get opened and then aborts.

Data Source=.;Initial Catalog=TestDB;
   Trusted_Connection=true;Asynchronous Processing=true;
   Connect Timeout=120;

2) 命令超时:对于每个命令，您还可以指定超时 - ADO.NET 将在取消查询之前等待该时间量.您在 SqlCommand 对象上指定:

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:

    using (SqlCommand RetrieveOrderCommand = new SqlCommand())
    {
       RetrieveOrderCommand.CommandTimeout = 150;
    }

这篇关于SQL Server 的超时设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！