In Case You Missed It: Async and Streaming for ADO.NET 4.5

For those .NET developers who still choose to work directly with the DataReader series of classes, .NET 4.5 brings some new async and streaming support your way.

SqlDataReader allows the developer to get better performance at the cost of reduced convenience. For example, this class normally reads an entire row at a time regardless of how many packets it has to wait for from the server. And if there are multiple large object columns, they are stored in memory at once. If you switch to sequential access then you no longer need to buffer the entire row, but you must read the columns in order.

With .NET 4.5 developers using sequential access can further fine tune performance by selectively using NextResultAsync, ReadAsync, IsDBNullAsync, and GetFieldValueAsync<T>. It should be made clear that the asynchronous methods for ADO.NET 4.5 are not an automatic performance boost and will probably do nothing for single-threaded speed. They can help when dealing with lots of simultaneous requests because they reduce blocking on threads. The downside is that you have to build Task objects, which can put pressure on the garbage collector. In general the advice is:

1. Use NextResultAsync whenever possible to handle packet processing asynchronously.
2. Prefer ReadAsync in either mode. Again, much of the packet processing can be done asynchronously.
3. Do NOT use IsDBNullAsync and GetFieldValueAsync in non-sequential mode. In this mode the columns are already buffered and you create Task objects for nothing.

For sequential mode, the decision to use GetFieldValueAsync is a bit more complicated. Daniel Paoliello explains,

However, if you called Read in non-sequential access mode, or if you are using sequential access mode, then the decision is much harder as you need to consider how much data you need to read to get to your desired column and how much data that column may contain. If you’ve read the previous column, and the target column is small (like a Boolean, a DateTime or a numeric type) then you may want to consider using a synchronous method. Alternatively, if the target column is large (like a varbinary(8000)) or you need to read past large columns, then using an asynchronous method is much better. Finally, if the target column is massive (like a varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML) then you should consider the new GetStream, GetTextReader or GetXmlReader methods instead.

There are advantages to using the stream-based methods when working large files stored in the database. For example, you can hand-off the stream to a WCF or ASP.NET response instead of bringing in the whole file into memory at one time. This is especially important for .NET developers because the large object heap is very susceptible to fragmentation.