This is a “letter to the editor“ I sent to Mike Otey at SQL Server magazine in reference to his ADO.NET tips from the March issue.
Mike, I thought your ADO.NET suggestions were right on—all good suggestions except for one. I’ve talked with a number of development shops and individual developers that avoid the DataReader as they think it’s too expensive to build and support. Sure it returns a fast connected data stream, but because you have to handle connections manually (and carefully), walk through each row and column one-at-a-time the amount of code needed (and schema-aware code at that) is too expensive to write, test and maintain. If the schema changes, the query changes or the stored procedure you execute changes, you’re pooched. Many of the problems associated with the connection pool can be traced back to connection management issues—often caused by improper handling of DataReaders passed from layer to layer. In an ASP.NET application it’s easy to bind a DataReader to a grid (which closes the DataReader, but does not close the connection if you don’t ask for CommandBehavior.CloseConnection.)
We feel that the DataAdapter.Fill method is a far better solution. It’s fast (within a few percent of the DataReader) for reasonable-sized rowsets. Yes, it creates a persistent DataTable so it has a memory footprint, but the added utility of having a sortable, filterable, selectable and updatable DataTable can be a big plus. Using or binding to the DataView (as you suggest) is a great idea, but can only be done if you use Fill.
Performance has two components: code performance and developer performance. Like any tool the DataReader is great for specific cases, but should not be a developer’s first choice.
Amen !!
Two notable things in this letter -
1. A lot of people are still very CPU performance centric, and even then they forget to consider the highly concurrent environments which are completely different.
2. Bill Vaughn's favorite phrase "you're pooched".
I have to say, that for many cases, in the ASP.NET world, a DataReader really is the right tool for the job, though I will admit if not used (and more importantly, closed) correctly, it can be a problem.
I do not index DataReaders by field number, any more than I would index a DataSet by field numbers, and so generally, adding or rearranging the column list in a select or Stored Procedure will have no impact on my code.
I have been very rigorous to ensure that all code closes the DataReader, and in many sites, and many pages and many millions of hits, I have not seen a problem tied to not closing a datareader. I have certainly seen other code that does, but it has not been my experience. All of my methods returning DataReaders uses the CloseConnection, and I always do the close in a finally block...
If I found a guy on my team passing a datareader to and from the presentation layer I would take him out to the woodshed for some intensive counseling. IMHO there is nothing better than a properly structured data access object or layer. But given the history of 6M VB programmers moving to .NET, I'm not surprised there are a few with problems. By the way, your own ADO.NET book suggested a solution with datareaders that was very nicley reasoned out.