Retrieve the data from sql database and don`t encounter timeout exception

Question

I run the below code to extract the data from sql, q is a query I use. The set of data is huge and it takes > 2 min to bring it Unfortunately I get an exception:

Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to conne ct to the routing destination. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wr apCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHa sConnectionLock, Boolean asyncClose)

How can it be resolved and despite the ling time I will be able to retrieve all the data I need.

   List<string> dataList = new List<string>();
   using (SqlConnection connection = new SqlConnection(csb.ConnectionString))
   {
         connection.Open();
         using (SqlCommand command = new SqlCommand(q, connection))
         {
               using (SqlDataReader reader = command.ExecuteReader())
               {
                      if (reader.HasRows)
                      {
                           while (reader.Read())
                           {
                               dataList .Add(reader.GetString(0));
                           }
                      }
               }
         }
    }

As @Rohit suggested set the Time out for the SQL Command. Setting `command.CommandTimeout = 0;` would enable your program/ function to wait for data from SQL. — fujiFX, Jun 12 '15 at 05:28
@fujiFX - I Would not suggest using `Timeout=0`, it should be set to a reasonable number to fetch data and should not cause deadlocks or infinite waiting. — Rohit Vipin Mathews, Jun 12 '15 at 05:34
@Rohit - Yeah, you correct. Even I would not set it 0 and suggested to set it to 0 since Yakov mentioned that it is huge data table. Thank you. — fujiFX, Jun 12 '15 at 05:37

score 0 · Answer 1 · edited May 23 '17 at 12:05

You will have to set the Timeout property of your SQL Command, so that query would throw the Timeout Exception only after waiting those many seconds.

Timeout is an integer value to specify the number of seconds to wait before Timeout exception will be thrown.

Please check SqlCommand.CommandTimeout

The default value is 30 seconds as per MSDN.

This SO Answer gives a good advice in using Timeout. (quoted below)

A 1-minute timeout seems reasonable for most queries. An 8+ hour timeout doesn't seem reasonable.

Do you have queries you're expecting to take longer than a minute? If so, raise it to a value you expect to be higher than anything you'd see if everything is working properly, but not so high as to take forever to alert you to a problem. (For example, you might go from 1 minute to 5 minutes.)

Bonus (source):

Setting the CommandTimeout to zero will remove the time limit when waiting for the query to run.

However, if the database goes offline your program will wait indefinitely for something that will not happen, so you should consider setting a long time instead, so that the command will timeout eventually instead of never.

Bennie van der Walt · Answer 2 · 2015-06-12T05:49:25.433

0

Modifying your code as follows should allow for the command to have enough time for the results to be returned : NOTE: 3 minutes used

using (SqlCommand command = new SqlCommand(q, connection))
                    {
                        command.CommandTimeout = 180;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    dataList .Add(reader.GetString(0));
                                }
                            }
                        }
                    }

More info: SqlCommand.CommandTimeout

edited Jun 12 '15 at 05:49

answered Jun 12 '15 at 05:36

Bennie van der Walt

343
3
7

As per OP's problem, *The set of data is huge and it takes > 2 min to bring it*, you advice to set `Timeout=60`? – Rohit Vipin Mathews Jun 12 '15 at 05:37
Will 3 minutes suffice then? – Bennie van der Walt Jun 12 '15 at 05:42
It's your suggestion. Also please make it a practice to add reasons to support your answer instead of just code. – Rohit Vipin Mathews Jun 12 '15 at 05:43

Retrieve the data from sql database and don`t encounter timeout exception

2 Answers2