0

I'm retrieving huge amount of data from SQL Server. Everything works fine but when I try to close IDataReader inside using statement

try
    {
      using (SqlConnection con = new SqlConnection(connString))
       {
         con.Open();
         using (SqlCommand command = new SqlCommand(sql_query, con))
         {
           command.CommandTimeout = 0;

           using (IDataReader rdr = new SqlCommand(sql_query, con).ExecuteReader(CommandBehavior.SequentialAccess))
            {
              dataTable = GetDataTableFromDataReader(rdr);
            }
             ....

I'm getting: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Would it be better to use something like rdr = null? Or is there another better solution how to close IDataReader?

mateskabe
  • 289
  • 2
  • 13

1 Answers1

2

Note that there are timeout settings on both the connection and the command. You don't need to close or dispose manually inside a using block.

You are creating a second SqlCommand in the using block which does not have a timeout set. Change to:

using (IDataReader rdr = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
    dataTable = GetDataTableFromDataReader(rdr);
}
Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120
  • I need to keep it there because SqlDataAdapter da = new SqlDataAdapter(command); if (showprogress) { Console.WriteLine("Downloading data from sql server and pushing into DataTable object"); } da.Fill(dataTable); – mateskabe Jan 31 '18 at 12:28
  • I compared your solution and for 1e6 records takes 65200ms. Without using statement ends with rdr = null takes 38630ms. Seems it stucked on closing IDataReader. – mateskabe Jan 31 '18 at 12:50
  • Is it correct if just write: IDataReader rdr = new SqlCommand(sql_query, con).ExecuteReader(CommandBehavior.SequentialAccess);dataTable = GetDataTableFromDataReader(rdr); rdr = null; – mateskabe Jan 31 '18 at 12:51
  • Instead of using statement? – mateskabe Jan 31 '18 at 12:51
  • You don't have to use "using" blocks. If you do not then close and dispose the objects manually, not forgetting exception scenarios. In such cases it's usual to declare an object before the try-catch and dispose in the "finally" block (checking to see if exists and already disposed. You can also go an Async. This is Forms-based, but you'll get an idea https://stackoverflow.com/questions/45950113/using-executenonqueryasync-and-reporting-progress –  Jan 31 '18 at 15:58