How to increase time in web config for executing sql query

Question

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

User · Answer

You should add the httpRuntime block and deal with executionTimeout  in seconds     lt  xml version  1 0  encoding  utf-8    gt   lt configuration gt        lt system web gt      lt httpRuntime executionTimeout  90  maxRequestLength  4096      useFullyQualifiedRedirectUrl  false      minFreeThreads  8      minLocalRequestFreeThreads  4      appRequestQueueLimit  100    gt    lt  system web gt        lt  configuration gt    For more information  please  see msdn page

User · Answer

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

User · Answer

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   lt httpRuntime maxRequestLength  2097152  executionTimeout  600    gt

User · Answer

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

User · Answer

You can do one thing    In the AppSettings config  create one if doesn t exist   create a key value pair  In the Code pull the value and convert it to Int32 and assign it to command TimeOut    like - In appsettings config -    lt appSettings gt          lt add key  SqlCommandTimeOut  value  240   gt   lt  appSettings gt    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

[asp.net] How to increase time in web.config for executing sql query

Examples related to asp.net

Examples related to sql

Examples related to web-config

Examples related to connection-string