working with DataReader , DataSet and DataAdapter , when to use what !?

 If your Data access operations is mainly fetching and displaying the records and doesnt involve insert/update/delete statements and other manipulations (forward only, read only) actions, go for the DataReader.

Advantages of DataReader :

• Using a DataReader is quick and efficient, as it doesn’t need to worry about keeping
track of every bit of data.
• A DataReader doesn’t need to store data in memory, so it uses fewer resources in
creating a page.

Disadvantages :
• You can’t do anything with the data, such as sending changes back to the database. This
would mean referring to data already passed through the reader, which isn’t possible;
DataReaders work only from database to client. If you need to send changes back to the
database, you’ll need to make a separate query to the database, as you’ll see in Chapter 8.
• DataReaders require exclusive access to a connection. Once a DataReader is open,
nothing else can use a connection until the DataReader is closed.
DataReaders are quick and fast, but they’re much like pay-per-view television. The only way to
watch a film again once you’ve finished watching it is to go back to the channel and request it
again. A DataSet, on the other hand, works like a video recorder; you can record the film off the
television and watch it as many times as you like, rewinding and fast-forwarding through it as
much as you like.
With a DataSet, you can store any data that you may have use for throughout the lifetime
of a page. This idea of persisting data away from the database is known as disconnected data. In
fact, it’s even better than a video recorder, because once you have data inside a DataSet, you
can alter that data, add to it, delete from it, and send all the changes back to the database relatively
easily. This is handy (don’t you wish you could do that with some movies?).

If your Data access operations extend to as simple as an Insert/Update/Delete statement to as complex as using it as a return type from a web service, go for the DataSet. The DataSet has the following advantages.

i. Complex Data type supporting numerous methods such as ReadXML which reads new XML data to form a dataset, WriteXML which provides an XML format of the existing data, etc.,

ii. Works on the disconnected architecutre i.e. The connection doesnt need to be on for the DataSet to perform the manipulations and other actions and is required only at the time of Updating the records to the Database Table.

iii. Provides an In-memory representation of the Data. Can contain one or more DataTable objects that have primary key, foreign key, and constraints between them and can enforce constraints such as unique or be configured to ignore them

iv. Has Individual elements such as DataTable, DataRow and DataColumn to access specific values.

v. While using DataSet and DataAdapter, you don't need to explicitly open the connection. DataAdapter automatically opens and closes the connection as and when required.

Note : DataSet may not rely on a connection to a database, but it still lasts only for the lifetime of
the page. If the page posts back and must be reassembled, so, too, must the DataSet. Either that, or it must
be persisted somehow for retrieval by the next page. As a result, take care to query only for the data that will
be needed on the page. A DataSet is resident in memory, so the smaller it is, the fewer resources required
to keep it there, and the better the page performs and scales.