Access through DataReader
So, what is a DataReader?
It is a one-way, forward-only method of reading data. A common use of the DataReader to read through a result set would look like the following:
while ( DataReader.Read() )
Console.WriteLine("Name: " + DataReader["Name"].ToString();
When you get the results into the DataReader, it’s important to note that the "cursor" or logical pointer into the returned data is just before the first row. You need to call .Read() once to read any data. .Read() will return true as long as there is an additional row to be read.
Access through DataSet
Another way to access data with ADO.NET is to use a DataSet. A DataSet is a database-independent, in-memory data store that enables the developer to directly access all rows and columns of one or many tables that a DataSet can contain. The DataSet has a Tables collection of DataTable objects, and the DataTable has a Rows collection that can be indexed to get to a particular row by number. The Rows collection has a .Count property that enables the developer to determine the number of rows in any of the tables in a DataSet.
One thing missing in the classic ADO recordset was the ability to control exactly how an update would take place. The DataSet enables you to update the in-memory copy of the data, and use a DataAdapter specific to a data provider to have those changes persisted back in the database.
If you run a shop where database updates are done using stored procedures, you can use a properly configured DataAdapter to ensure that changes to the database are made using stored procedures. The important thing to remember is that the DataSet is independent of any particular database; the only connection that exists is when the DataSet is used in conjunction with a DataAdapter.
My predisposition to using DataReaders for ASP.NET applications and DataSets for Windows Forms applications has many exceptions. One of the features of the DataSet that makes it flexible is that it can be easily serialized into XML. It is often convenient to use the .WriteXml method to write out the contents of the DataSet. You cannot write out a single DataTable in a DataSet in the 1.x version of ADO.NET, however.
In addition to writing the DataSet to an XML file, .NET Web Services accepts parameters of type DataSet and return values of type DataSet. While this can be powerful, it does limit your application to .NET consumers of your web service, since the DataSet is not accepted by web services created with any other tools I am aware of. Many people consider it evil to return a DataSet from a web service, but it can be a good idea, especially if you are certain that only .NET consumers will ever need to access your web service.