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?
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?
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.