.NET: SqlDataReader.Close or .Dispose results in Timeout Expired exception

Question

When trying to call Close or Dispose on an SqlDataReader i get a timeout expired exception. If you have a DbConnection to SQL Server, you can reproduce it yourself with:

String CRLF = "\r\n";
String sql = 
    "SELECT * " + CRLF +
    "FROM (" + CRLF +
    " SELECT (a.Number * 256) + b.Number AS Number" + CRLF +
    " FROM master..spt_values a," + CRLF +
    "  master..spt_values b" + CRLF +
    " WHERE  a.Type = 'p'" + CRLF +
    "  AND b.Type = 'p') Numbers1" + CRLF +
    " FULL OUTER JOIN (" + CRLF +
    "  SELECT (print("code sample");a.Number * 256) + b.Number AS Number" + CRLF +
    "  FROM master..spt_values a," + CRLF +
    "   master..spt_values b" + CRLF +
    "  WHERE  a.Type = 'p'" + CRLF +
    "   AND b.Type = 'p') Numbers2" + CRLF +
    " ON 1=1";

DbCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
DbDataReader rdr = cmd.ExecuteReader();
rdr.Close();

If you call reader.Close() or reader.Dispose() it will throw a System.Data.SqlClient.SqlException:

• ErrorCode: -2146232060 (0x80131904)
• Message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Answer 1

it's because you have just opened the data reader and have not completely iterated through it yet. you will need to .Cancel() your DbCommand object before you attempt to close a data reader that hasn't completed yet (and the DbConnection as well). of course, by .Cancel()-ing your DbCommand, I'm not sure of this but you might encounter some other exception. but you should just catch it if it happens.

Answer 2

Where do you actually read the data? You're just creating a reader, but not reading Data. It's just a guess but maybe the reader has problems to close if you're not reading ;)

DbDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
    int index = rdr.GetInt32(0);
}

Answer 3

Cruizer had the answer: call command.Cancel():

using (DbCommand cmd = connection.CreateCommand())
{
    cmd.CommandText = sql;
    using (DbDataReader rdr = cmd.ExecuteReader())
    {
       while (rdr.Read())
       {
          if (WeShouldCancelTheOperation())
          {
             cmd.Cancel();
             break;
          }
       }
    }    
}

It is also helpful to know that you can call Cancel even if the reader has already read all the rows (i.e. it doesn't throw some "nothing to cancel" exception.)

DbCommand cmd = connection.CreateCommand();
try
{
    cmd.CommandText = sql;
    DbDataReader rdr = cmd.ExecuteReader();
    try
    {
       while (rdr.Read())
       {
          if (WeShouldCancelTheOperation())
             break;
       }
       cmd.Cancel();
    }    
    finally
    {
       rdr.Dispose();
    }
}
finally
{
   cmd.Dispose();
}