.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 = "rn";
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:
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.
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 (ie 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();
}
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);
}
上一篇: Reportins服务超时过期