SqlDataReader: Buffered vs Unbuffered readers - How to?

c# dapper datareader sqldatareader

Question

I was reading Dapper's document at github until I came to this section: Buffered vs Unbuffered readers. Which you will see:

Dapper's default behavior is to execute your sql and buffer the entire reader on return. This is ideal in most cases as it minimizes shared locks in the db and cuts down on db network time.

What did they mean by this paragraph? It's actually the first time I'm hearing this. Is there any way to read a, say SqlDataReader, without iterating? Read all rows at once?

Popular Answer

As I recall, the difference between buffered and unbuffered is a call to the .ToList() extension method to force the objects to be materialised before the call to Query returns control to the caller.

Essentially, when Dapper reads the database it can either materialise everything up front (buffered), or it can materialise as you need the data (unbuffered).

In unbuffered mode when you iterate over the results it is reading from the internal data reader and converting that to the object you've specified on each loop. If you take a long time to do your work then it potentially keeps the data reader open for a long time.

In buffered mode you know that the data reader has completed by the time you get the result back and you can take as long as you want with the result safely knowing you aren't keeping the data reader alive longer than it needs to and you can close the connection to the database if you want.

Also, be aware that in unbuffered mode you cannot iterate of the results of an initial query and initiate another on the same connection.

Why use one over the other?

Buffered:

  • Smaller data sets
  • When you want to ensure the reader is complete quickly
  • So you can close the connection after the query
  • or you can reuse the connection for another query while iterating over the results of the first.

Unbuffered:

  • Larger datasets where you don't want to pre-load everything into memory at once.
  • When you can afford to keep the data reader alive and the connection open for a longer period of time.

I'd say that for the vast majority of cases I use buffered mode. It is very rare I have a data set large enough that I want unbuffered results.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why