How Datareader Works

How DataReader works?

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.

How much data can be stored in network buffer when datareader is used

Depending on your setup have a read of this Default Result Set Processing and Multiple Active Result Sets and Rowsets and SQL Server Cursors

Look into TcpClient.ReceiveBufferSize it will tell you how much raw data can be read in one operation.

"The ReceiveBufferSize property gets or sets the number of bytes that you are expecting to store in the receive buffer for each read operation. This property actually manipulates the network buffer space allocated for receiving incoming data.

Your network buffer should be at least as large as your application buffer to ensure that the desired data will be available when you call the NetworkStream.Read method. Use the ReceiveBufferSize property to set this size. If your application will be receiving bulk data, you should pass the Read method a very large application buffer.

If the network buffer is smaller than the amount of data you request in the Read method, you will not be able to retrieve the desired amount of data in one read operation. This incurs the overhead of additional calls to the Read method."

and then read about NetworkStream.Read

At which point you will have a better idea of the complexity of the answer to your question.

When DataReader become empty?

A data-reader is a hose of data. It becomes "empty" (or a better analogy: runs dry) when you consume all the data it is reading.

It is not a storage device (it isn't a bucket). If you want the data afterwards, store it somewhere - a DataTable or a class model, for example.

How does SqlCommand.ExecuteReader() work?

Why it doesn't show up in your process memory I cannot answer. But basically, once the query has started producing results, the server sends them to the client as fast as it can^1,2. Various levels of buffering may be involved and some of these (e.g. buffers inside the network drivers) will not be directly accounted for within your process.

As soon as possible, once all the results are delivered, the server can free up the resources that were required for your query and start using them to satisfy other queries (for other clients).

Having only a single result stored on the client and requiring every call to Read to pull a new row from the server requiring a network round trip each time would be spectacularly inefficient.

---

¹Subject to whatever limitations are imposed by e.g. TCP windows.

²Often referred to as "firehose mode", as opposed to if you explicitly create a cursor for your query in which you do control the rate of retrieval from the server.

SqlDataReader and method scope

You are using DataReader.GetSchemaTable which returns a DataTable with all schema informations for a given table.

It has following columns:

ColumnName
ColumnOrdinal
ColumnSize
NumericPrecision
// .. 26 others

So you don't find your id-column which belongs to your table. That's why you get the error "'id' column doesnt exist". I doubt that your second approach works. I don't see why you need GetSchemaTable at all. You just have to advance the reader to the next record:

if (requestReader.HasRows && requestReader.Read())
{
   int id = requestReader.GetInt32(requestReader.GetOrdinal("id"));
   // ...
}

DataReader does not return data but the number of rows

You need to read the reader

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader#:~:text=To%20retrieve%20data%20using%20a,rows%20from%20a%20data%20source.

SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
    while (reader.Read())
    {
           // 
    }
}

How do I loop through rows with a data reader in C#?

That's the way the DataReader works, it's designed to read the database rows one at a time.

while(reader.Read()) 
{
  var value1 = reader.GetValue(0); // On first iteration will be hello
  var value2 = reader.GetValue(1); // On first iteration will be hello2
  var value3 = reader.GetValue(2); // On first iteration will be hello3
}

---

Related Topics