Timeout Exception Causes Sqldatareader To Close?

I'm trying to pull some binary data from a database and write them to pdf files. For the most part, this is going along swimmingly, but the occasional row of data seems to throw a

Solution 1:

It looks like your SqlCommand is timing out - when you call ExecuteReader, the associated command remains open and will be vulnerable to timeouts until you finish reading. As it says in the SqlCommand.CommandTimeout documentation:

This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

When the command times out, it closes the reader, from which you can't recover.

The first thing to try to solve this is to increase the CommandTimeout dramatically, just to make sure you can proceed.

Next, if you haven't done so already, it may help to use the ExecuteReader overload that allows you to specify a CommandBehavior, and pass CommandBehavior.SequentialAccess (per the recommendation in the MSDN topic "Retrieving Large Data (ADO.NET)").

Finally, you might also try breaking the reads into chunks of records.

Solution 2:

If the SQL Error Severity is less than 17, you can set SqlConnection.FireInfoMessageEventOnUserErrors = true to handle the exception as a warning. Anything greater than Severity 17 is going to close the connection no matter what.