2/16/2007

YEESH! FORGET DATAREADERS!

I see so much of this on forums and newsgroups, I almost feel compelled to say "something". It seems that somewhere along the way new .NET developers read (or are told) that the DataReader is the "most efficient way" to get data out of a database. What happens next is they take this 100% literally, and get themselves into all kinds of trouble - blowing up connection pools, trying to do things that you cannot do, and so on. This becomes the idee fixe de-facto method for getting data, no matter what.

Yes! The DataReader IS the fastest way to get data out of a database. However, that comes with a price that you need to evaluate. DataReaders hold the connection open. You cannot have a method that just "returns a DataReader" - leaving you to blithely do whatever you want -- unless CommandBehavior.CloseConnection was used in the intial SQL call and you are prepared to call the Close method on your reader.

In addition, DataReaders offer only ONE TIME firehose-style, forward-only access through the resultset. You cannot "reset" a DataReader; you cannot "go backwards". One time through, and that's it, pal! The ONLY other thing you can do is switch to the next resultset in the reader, if there is one. And if you do not explicitly call the Close method on the Reader (or the connection), preferably in a finally block, your app will come to a screeching halt when the 100 connections in the pool are gone. PLONK!

By contrast, the TableAdapter (in .NET 2.0) has a Fill method that uses a DataReader under the hood to fill the DataTable, automatically closes the connection for you, and now you've got a DataTable that you CAN do all those cool things with. If you are going to want to Sort, filter, Page, choose a row, or any of a dozen other things then FORGET about using DataReaders!

I should also add, based on one of the comments, that the TableAdapter is a control and comes with a lot of overhead. If you just want a DataTable programmatically, you can use the DataAdapter's Fill method (which also uses DataReader "under the hood") and get your table very efficiently. If you use the Patterns and Practices Group's SqlHelper class ("Microsoft.ApplicationBlocks.Data") you don't even have to worry about connections, because it cleans up after itself automatically.

In my opinion, the slight additional overhead of filling a DataTable with the contents of the temporary DataReader is so small as to be inconsequential. This does not even address the fact that any good developer is usually able to cache most database data, another useful feature that is overlooked a good part of the time. I have finally trained myself to ask, every time I get data out of a database, "Can I cache this? If so, what kind of caching do I want to use?".

In most cases, unless you know exactly what you are doing and why you need a DataReader, developers will be better served by FORGETTING ABOUT DATAREADERS, and using DataTables and DataSets.

Just my two cents.