如何增加 web.config 中执行 sql 查询的时间

How to increase time in web.config for executing sql query

提问by Jui Test

When I am running a query in web application, I'm getting a nullvalue. Same query directly in SQL Management Studio returns results.

当我在 Web 应用程序中运行查询时，我得到了一个null值。直接在 SQL Management Studio 中的相同查询返回结果。

I think that the problem is a timeout. How can I increase the time for execution of query in web application? In my web.config : connectionstring, there is no code for timeout. If I choose a timeout there, will that affect other parts of my system?

我认为问题是超时。如何增加在 Web 应用程序中执行查询的时间？在我的 web.config : 中connectionstring，没有超时代码。如果我在那里选择超时，是否会影响我系统的其他部分？

回答by

You can do one thing.

你可以做一件事。

1. In the AppSettings.config (create one if doesn't exist), create a key value pair.
2. In the Code pull the value and convert it to Int32 and assign it to command.TimeOut.

1. 在 AppSettings.config（如果不存在则创建一个）中，创建一个键值对。
2. 在代码中提取值并将其转换为 Int32 并将其分配给 command.TimeOut。

like:- In appsettings.config ->

像：- 在 appsettings.config ->

<appSettings>    
   <add key="SqlCommandTimeOut" value="240"/>
</appSettings>

In Code ->

在代码中 ->

command.CommandTimeout = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SqlCommandTimeOut"]);

That should do it.

那应该这样做。

Note:- I faced most of the timeout issues when I used SqlHelper class from microsoft application blocks. If you have it in your code and are facing timeout problems its better you use sqlcommand and set its timeout as described above. For all other scenarios sqlhelper should do fine. If your client is ok with waiting a little longer than what sqlhelper class offers you can go ahead and use the above technique.

注意：- 当我使用 Microsoft 应用程序块中的 SqlHelper 类时，我遇到了大部分超时问题。如果您的代码中有它并且遇到超时问题，最好使用 sqlcommand 并按照上述方法设置其超时。对于所有其他情况，sqlhelper 应该可以正常工作。如果您的客户端可以等待比 sqlhelper 类提供的稍长一点的时间，您可以继续使用上述技术。

example:- Use this -

示例：- 使用这个-

 SqlCommand cmd = new SqlCommand(completequery);

 cmd.CommandTimeout =  Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SqlCommandTimeOut"]);

 SqlConnection con = new SqlConnection(sqlConnectionString);
 SqlDataAdapter adapter = new SqlDataAdapter();
 con.Open();
 adapter.SelectCommand = new SqlCommand(completequery, con);
 adapter.Fill(ds);
 con.Close();

Instead of

代替

DataSet ds = new DataSet();
ds = SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.Text, completequery);

Update: Also refer to @Triynko answer below. It is important to check that too.

更新：另请参阅下面的@Triynko 答案。检查这一点也很重要。

回答by ibo.ezhe

You should add the httpRuntimeblock and deal with executionTimeout(in seconds).

您应该添加httpRuntime块并处理executionTimeout（以秒为单位）。

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
...
 <system.web>
   <httpRuntime executionTimeout="90" maxRequestLength="4096"
    useFullyQualifiedRedirectUrl="false"
    minFreeThreads="8"
    minLocalRequestFreeThreads="4"
    appRequestQueueLimit="100" />
 </system.web>
... 
</configuration>

For more information, please, see msdn page.

有关更多信息，请参阅msdn 页。

回答by Jeremy McGee

SQL Server has no setting to control query timeout in the connection string, and as far as I know this is the same for other major databases. But, this doesn't look like the problem you're seeing: I'd expect to see an exception raised

SQL Server 没有设置来控制连接字符串中的查询超时，据我所知，其他主要数据库也是如此。但是，这看起来不像您看到的问题：我希望看到引发异常

Error: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

错误：System.Data.SqlClient.SqlException：超时已过期。操作完成前超时时间已过或服务器未响应。

if there genuinely was a timeout executing the query.

如果确实存在执行查询超时。

If this does turn out to be a problem, you can change the default timeout for a SQL Server database as a property of the database itself; use SQL Server Manager for this.

如果这确实是一个问题，您可以将 SQL Server 数据库的默认超时更改为数据库本身的属性；为此使用 SQL Server 管理器。

Be sure that the query is exactlythe same from your Web application as the one you're running directly. Use a profiler to verify this.

确保来自您的 Web 应用程序的查询与您直接运行的查询完全相同。使用分析器来验证这一点。

回答by kesse

I realise I'm a litle late to the game, but just spent over a day on trying to change the timeout of a webservice. It seemed to have a default timeout of 30 seconds. I after changing evry other timeout value I could find, including:

我意识到我玩游戏有点晚了，但刚刚花了一天多的时间尝试更改网络服务的超时。它似乎有一个 30 秒的默认超时。我更改了我能找到的所有其他超时值，包括：

• DB connection string Connect Timeout
• httpRuntime executionTimeout
• basicHttpBinding binding closeTimeout
• basicHttpBinding binding sendTimeout
• basicHttpBinding binding receiveTimeout
• basicHttpBinding binding openTimeout

• 数据库连接字符串连接超时
• httpRuntime executionTimeout
• basicHttpBinding 绑定 closeTimeout
• basicHttpBinding 绑定 sendTimeout
• basicHttpBinding 绑定 receiveTimeout
• basicHttpBinding 绑定 openTimeout

Finaley I found that it was the SqlCommand timeout that was defaulting to 30 seconds.

Finaley 我发现它是默认为 30 秒的 SqlCommand 超时。

I decided to just duplicate the timeout of the connection string to the command. The connection string is configured in the web.config.

我决定将连接字符串的超时时间复制到命令中。连接字符串在 web.config 中配置。

Some code:

一些代码：

namespace ROS.WebService.Common
{
  using System;
  using System.Configuration;
  using System.Data;
  using System.Data.SqlClient;

  public static class DataAccess
  {
    public static string ConnectionString { get; private set; }

    static DataAccess()
    {
      ConnectionString = ConfigurationManager.ConnectionStrings["ROSdb"].ConnectionString;
    }

    public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] sqlParams)
    {
      using (SqlConnection conn = new SqlConnection(DataAccess.ConnectionString))
      {
        using (SqlCommand cmd = new SqlCommand(cmdText, conn) { CommandType = cmdType, CommandTimeout = conn.ConnectionTimeout })
        {
          foreach (var p in sqlParams) cmd.Parameters.Add(p);
          cmd.Connection.Open();
          return cmd.ExecuteNonQuery();
        }
      }
    }
  }
}

Change introduced to "duplicate" the timeout value from the connection string:CommandTimeout = conn.ConnectionTimeout

引入了从连接字符串“复制”超时值的更改：CommandTimeout = conn.ConnectionTimeout

回答by Muhammad Akhtar

I think you can't increase the time for query execution, but you need to increase the timeout for the request.

我认为你不能增加查询执行的时间，但你需要增加请求的超时时间。

Execution Timeout Specifies the maximum number of seconds that a request is allowed to execute before being automatically shut down by ASP.NET. (Default time is 110 seconds.)

执行超时指定请求在被 ASP.NET 自动关闭之前允许执行的最大秒数。（默认时间为 110 秒。）

For Details, please have a look at https://msdn.microsoft.com/en-us/library/e1f13641%28v=vs.100%29.aspx

有关详细信息，请查看https://msdn.microsoft.com/en-us/library/e1f13641%28v=vs.100%29.aspx

You can do in the web.config. e.g

你可以在 web.config 中做。例如

<httpRuntime maxRequestLength="2097152" executionTimeout="600" />