I'm not trying to re-ignite this age-old debate but am merely using it as a starting point to explore the data access options available to date and where we may be heading in the near future. In the next post, I will look at multi-tier design and the use of custom entity class objects. Later on, we may look at that new animal called LINQ. I recommend the following as an introduction to ADO.NET:

Best Practises for Using ADO.NET

Working with Data in ASP.NET 2.0 

Many programmers mistakenly view the DataSet as the only real option for data access as far as Web applications are concerned. We often choose between DataReader and DataSet based either on our familiarity with the syntax of one over the other or our lack of understanding of the basic differences between them. More often than not, we usually end up using the wrong data access method for the wrong reasons. People typically see it as a simple choice: the DataReader for speed and the DataSet for data manipulation. In reality, the choice involves a whole range of trade-offs. As an overview, here are the basic characteristics of the both DataReader and the DataSet:

DataReader:

  * Connected
  * Quick
  * Forward-only, read-only access
  * One row at a time is stored in memory and either written over or discarded
  * Light on resources such as IIS
  * Cannot be persisted to cache or session
  * Holds on to the data connection

DataSet:

  * Disconnected
  * Read-write
  * Can navigate backwards and forwards
  * Stores all data in memory
  * More intense use of IIS and memory resources
  * Connections closed immediately the data is gathered
  * Relational-data-aware; can consist of collections of related tables
  * Can make updates back to the database
  * Data can be stored in session

Things We Tend to Forget:

  * To close the DataReader and/or Connection
  * Data-bound controls keep their own copy of any data to which they are bound
  * To use the DataReader's HasRows and IsDBNull properties to avoid errors
  * To use the DataReader for simple data-binding where no caching of data is necessary
  * With DataSets, primary keys and relationships have to be re-created in code
  * DataSets support data transactions and data filtering
  * Unlike DataReaders, DataSets support binding to multiple controls
  * DataSets can be used to manipulate the data as XML

If there is one lesson to be learned here, it is not to make snap decisions when it comes to choosing between these two data access models. You need to think it through each and every time you are accessing the database and binding to a control. The DataReader may be fast but it doesn't support binding to multiple controls. So, you won't gain much mileage from trying to use it to sort and filter with rich controls. The DataSet is particularly useful when used to intermittently connect to the database as with salespeople on the road. The data can be serialized to XML and stored offline.

I mentioned that data-bound controls keep their own copy of the data to which they are bound: by my calculations, that makes for three distinct copies of the data when using a DataSet; any questions?!!