I've recently renewed my effort to "use the right tool for the job" when developing xhtml and CSS, which got me to thinking. If using a <ul> rather than a <div> is better in a certain application, are there better ASP.NET tools for other jobs?
With that mindset I came across a task that required connecting to one of our databases. Rather than building a DAL (and no, I'm not LINQified yet) I decided to just grab the data though a SqlConnection and go from there. So far so good. I then was faced with how to "deal" with the stuff returned from the SqlCommand. Here ASP.NET has a couple controls that do can read the data from your SqlCommand: DataReader and DataSet. But which to use?
I've always been partial to using what you know, and knowing what you use. So for almost all my cases I've used the DataSet control. This has allowed me to not only get stuff out of a database, but update and insert stuff as well. In those cases, DataSet is ideal. But, with all that extra functionality comes extra overhead.
DataReader, on the other hand, doesn't "do everything for you" and then some. It does its job, and does it well, and doesn't do anything else (sound like a UNIX concept, doesn't it?). With DataReader you are doing just that, reading the data from the database, and handling it (parsing it into an object, writing it onto the page, etc.), and closing the SqlConnection.
4GuysFromRolla describe the DataReader thusly:
"[...] the DataReader is just a ferry of data between the application and database. [...] Furthermore, a DataReader is limited to being read-only and forward-only. That is, the information retrieved from the database cannot be modified by the DataReader, nor can the DataReader retrieve records in a random order. Instead, a DataReader is limited to accessing the records in sequential order, from the first one to the last one, one record at a time."
Wow, that seems awfully limited, doesn't it? If you want to do updates and inserts, sure. If you're only grabbing data from your database, DataReader is most likely the ideal tool for the job: Open the connection, grab the data, handle it, and close the connection. And that's where DataReader really shines; check out the graph. As far as graphs go, it doesn't get more dramatic than that. Even at 100 records being returned, the results are alarming, up that to 1,000 records and you can easily see why you'd want to use a DataReader over a DataSet (again, unless you need the added features that DataSet gives you).
You can read more (and dig into the minutia of the test results) at 4GuysFromRolla.