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

Answer 1

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.

Answer 2

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