CODEWITHSUNDEEP
asp.netsqlweb-configconnection-string
Jui Test
Jui Test

Reputation: 2409

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

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

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?

Upvotes: 13

Views: 129991

Answers (5)

kesse
kesse

Reputation: 158

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:

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

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

I decided to just duplicate the timeout of the connection string to the command. The connection string is configured in the 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

Upvotes: 3

user3328730
user3328730

Reputation:

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.

like:- In 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.

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.

Upvotes: 13

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

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

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

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

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

Upvotes: 1

Jeremy McGee
Jeremy McGee

Reputation: 25200

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

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

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.

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

Upvotes: 3

maxk
maxk

Reputation: 642

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

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

Upvotes: 2

Related Questions