How DataReader works???

Question

Hi,

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.

Answer 2

The underlying connection type may well impact how much data is supplied at a time. For a small amount of data using the shared memory connector it is quite possible that all the data is sent together.

Shared memory is the default protocol when client and server are on the same machine.

Answer 3

It reads them as it has time in the background. By the time you had gone to SQL Server and closed the connection, all the data had transferred over in the background. What happens when you execute the reader, is that it calls SQL Server, and tells it to start sending results. As soon as the query is done executing (parses correctly, query is valid), but before it is finished running, it returns. At that point it you can start calling the read method. However, it still reads and buffers data in the background so that when you call read again, the next row is ready, waiting in the buffer, and it doesn't have to go to the database.

Answer 4

The data reader reads a record at a time, but it reads it from the underlying database driver. The database driver reads data from the database in blocks, typically using a buffer that is 8 kilobytes.

If your result records are small and you don't get very many, they will all fit in the buffer, and the database driver will be able to feed them all to the data reader without having to ask the database for more data.

If you fetch a result that is larger than the buffer, you will only be able to read the first part of it, before the database driver needs to ask the database for more data. At that time you will get an exception if the database is no longer accessible.