How DataReader works?

Question

I was thinking that the SQLDataReader should not work if there is no connection to the SQLServer.

I experimented this scenario. I execute the ExecuteReader then stop the SQLServer Service and tried to iterate through the DataReader. What I expected was an exception, but it gave the results one after the other. Ideally the DataReader should read one row at a time from the stream that gets connected to the DB server and which should throw an exception if we disconnect the DB server?

I don't know, What is it that I am missing here.

Answer 1

I strongly suspect that the reader reads a batch of results at a time. That's a lot more efficient than one row at a time (think about the situation where a single row is only a few bytes... you don't want a network packet per row when it could have retrieved lots of rows in a single packet). It also potentially allows the database to release its internal resources early - if the data reader has read all the results (when there are only a few) it can effectively forget about the query.

I suspect if you try the same type of thing with a query returning lots of results, you'd get the expected exception.