When to use DataReader, DataSet [ DataReader v/s DataSet ]

When to use DataReader and DataSet
ADO.NET provides two objects for retrieving relational data and storing it in memory: the DataSet and the DataReader. The DataSet provides an in-memory relational representation of data, a complete set of data that includes the tables that contain, order, and constrain the data, as well as the relationships between the tables. 
The DataReader provides a fast, forward-only, read-only stream of data from a database.
When using a DataSet, you will often make use of a DataAdapter (and possibly a CommandBuilder) to interact with your data source. Also, when using a DataSet, you may employ a DataView to apply sorting and filtering to the data in the DataSet. The DataSet can also be inherited to create a strongly typed DataSet in order to expose tables, rows, and columns as strongly typed object properties.


DataSet vs. DataReader

To determine whether to use the DataSet or the DataReader when you design your application, consider the level of functionality that is needed in the application.

Use the DataSet in order to do the following with your application:

1. Navigate between multiple discrete tables of results.
2. Manipulate data from multiple sources (for example, a mixture of data from more than one database, from an XML file, and from a spreadsheet).
3. Exchange data between tiers or using an XML Web service. Unlike the DataReader, the DataSet can be passed to a remote client.
4. Reuse the same set of rows to achieve a performance gain by caching them (such as for sorting, searching, or filtering the data).
5. Perform a large amount of processing per row. Extended processing on each row returned using a DataReader ties up the connection serving the DataReader longer than necessary, impacting performance.
6. Manipulate data using XML operations such as Extensible Stylesheet Language Transformations (XSLT transformations) or XPath queries.

Use the DataReader in your application if you:

1. Do not need to cache the data.
2. Are processing a set of results too large to fit into memory.
3. Need to quickly access data once, in a forward-only and read-only manner.

How DataReader Works in Connected Mode
The DataReader has a defined set of operations that revolve around its connected, forward-only, read-only nature (the read-only DataReader is also known as the firehose cursor of ADO.NET). A DataReader is a stream of data that is returned from a database query. When the query is executed, the first row is returned to the DataReader via the stream. The stream then remains connected to the database, poised to retrieve the next record. The DataReader reads one row at a time from the database and can only move forward, one record at a time. As the DataReader reads the rows from the database, the values of the columns in each row can be read and evaluated, but they cannot be edited.
 The following code demonstrates how to retrieve a list of products from the Northwind database using a SqlDataReader object:

   1:  string sSQL = "SELECT * FROM Products";

   2:  string sConnString =

   3:      "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4:  using (SqlConnection oCn = new SqlConnection(sConnString))

   5:  {

   6:      SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   7:      oSelCmd.CommandType = CommandType.Text;

   8:      oCn.Open();

   9:      SqlDataReader oDr = oSelCmd.ExecuteReader();

  10:      DataGrid1.DataSource = oDr;

  11:      DataGrid1.DataBind();

  12:  }

Both a SqlConnection and a SqlCommand object are created. The SqlConnection is opened and the SqlCommand object executes the SQL query, returning the first row to the SqlDataReader. At this point the connection to the database is still open and associated with the SqlDataReader. This code shows how a SqlDataReader can be bound to a bindable object such as an ASP.NET DataGrid.

Alternatively, a DataReader could be used to retrieve the rows and then loop through them manually, one by one. It can support several resultsets as well. For example, a list of products and categories could be retrieved from a database. The following code retrieves a SqlDataReader and loops through its rows, writing the first column's value for each row to the console:

   1:  SqlDataReader oDr = oCmd.ExecuteReader();

   2:  while(oDr.Read()) {

   3:      Console.WriteLine(oDr[0]);

   4:  }

How Dataset Works in Disconnected Mode

The DataSet is the main data storage tool in the ADO.NET disconnected architecture. Unlike the DataReader, the DataSet is not connected directly to a database through a Connection object when you populate it. Instead, to fill a DataSet from a database you first create a DataAdapter object (such as a SqlDataAdapter) for the provider and associate it with a SqlConnection object. Then the SqlDataAdapter can broker the data retrieval for the DataSet by issuing a SqlCommand against the database through the SqlConnection, retrieving the data, and filling the DataSet.

You can think of the SqlDataAdapter as a bridge between the connected and disconnected objects. One of its purposes is to serve as the route for a rowset to get from the database to the DataSet. For example, when the SqlDataAdapter's Fill method is executed it opens its associated SqlConnection object (if not already open) and issues its associated SqlCommand object against the SqlConnection. Behind the scenes, a SqlDataReader is created implicitly and the rowset is retrieved one row at a time in succession and sent to the DataSet. Once all of the data is in the DataSet, the implicit SqlDataReader is destroyed and the SqlConnection is closed.
The following code shows how a DataSet can be filled from the Products table of the Northwind database. Notice that there is no explicit SqlDataReader object in this code sample:

   1:  string sSQL = "SELECT * FROM Products";

   2:  string sConnString = 

   3:      "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4:  SqlDataAdapter oDa = new SqlDataAdapter();

   5:  DataSet oDs = new DataSet();

   6:  using(SqlConnection oCn = new SqlConnection(sConnString))

   7:  {

   8:      SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   9:      oSelCmd.CommandType = CommandType.Text;

  10:      oDa.SelectCommand = oSelCmd;

  11:      oDa.Fill(oDs, "Products");

  12:  }

 Unlike the DataReader, the DataSet is not read-only. A DataSet can be modified, and rows can be added or deleted. Changes to a DataSet can be sent to the database via a managed provider's objects. Another key difference between the DataSet and the DataReader is that the DataSet is fully navigable. Its rows can be traversed forward or backward. The DataReader can be traversed forward only. In addition, a DataSet is highly flexible in that its DataTable objects can be filtered vertically or horizontally and they can be sorted or even searched. The DataSet is independent of any one data provider as it relies on a DataAdapter specific to each provider to broker the data between the DataSet and the database.

Source: MSDN
http://msdn.microsoft.com/en-us/library/ms971481.aspx
http://msdn.microsoft.com/en-us/magazine/cc188717.aspx