CODEWITHSUNDEEP
.netsql-serverdatabasetimeout
Ian Boyd
Ian Boyd

Reputation: 256951

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

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:

Upvotes: 8

Views: 7859

Answers (3)

Ian Boyd
Ian Boyd

Reputation: 256951

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

Upvotes: 3

MADMap
MADMap

Reputation: 3192

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

Upvotes: 0

cruizer
cruizer

Reputation: 6151

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.

Upvotes: 17

Related Questions