.NET: SqlDataReader.Close or .Dispose Results in Timeout Expired Exception

Question

When I try to call Close or Dispose in SqlDataReader, I get an expired exception. If you have DbConnection for SQL Server, you can play it yourself:

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: "Timed out. The timeout period expires before the operation is completed or the server is not responding."

Answer 1

. .Cancel() DbCommand, , ( DbConnection ). , .Cancel() - DbCommand, , - . , .

Answer 2

Cruizer : call command.Cancel():

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

, Cancel, (.. ", " ).

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

Answer 3

Where do you actually read the data? You simply create a reader, but do not read the "Data". This is just a hunch, but the reader may have problems closing if you don't read;)

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