Execution Timeout Expired when closing IDataReader

Question

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?

I doubt that disposing the datareader causes the timeout. You can execute `GetDataTableFromDataReader` fully? Have you debugged? — Tim Schmelter, Jan 31 '18 at 12:19
Why you use `SequentialAccess` at all? Do you read huge binary data? This enables to read rows not at once but also as a stream, then you can use `GetBytes` to read the binary partially. — Tim Schmelter, Jan 31 '18 at 12:22
This works fine but falling into exception when it leaves last curly bracket. — mateskabe, Jan 31 '18 at 12:22
Yes, SequentialAccess was advised on MSDN in case of huge data. Not binary data. — mateskabe, Jan 31 '18 at 12:23
Not huge data but huge binary data. If you just select a huge amount of rows you should not use `SequentialAccess` — Tim Schmelter, Jan 31 '18 at 12:24

score 2 · Accepted Answer · edited Jan 31 '18 at 12:28

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

edited Jan 31 '18 at 12:28

Camilo Terevinto

31,141
6
88
120

answered Jan 31 '18 at 12:23

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

Execution Timeout Expired when closing IDataReader

1 Answers1