Retrieving multiple rows from Database Tables with ADO .NET

With ADO .NET there are two ways of retrieving multiple rows from a database table:

1. Using SqlDataAdapter to generate DataSet or DataTable
2. Using SqlDataReader to provide a read-only, forward-only data stream

The choice between the two approaches is one between performance and functionality. DataReader gives better performance, while DataAdapter approach provides additional functionality and flexibility. Following is a list of points telling you when to use which approach.

Use DataSet with SqlDataAdapter when:

1. You require a disconnected, memory-resident cache of data
2. You want to update some or all of the retrieved rows and use batch update facilities of the DataAdapter
3. You want to bind the data with a control that requires a data source that supports IList

Good-to-know points about SqlDataAdapter:

1. Fill method of SqlDataAdapter opens and closes the database connection for you. So you don't need to do connection management.
2. However, that means, if you require the connection to be open after the Fill Method, open the connection yourself before calling the Fill method, thus avoiding unnecessary close-open of the connection.

Use SqlDataReader when:

1. You are dealing with large volumes of data that is too much for maintaining in a single cache.
2. You want to simply read the data and present to the user (read-only data)
3. You want to bind the data with a control that requires a data source that implements IEnumerable

Good-to-know points about SqlDataReader:

1. Remember to call Close on SqlDataReader as soon as possible, since the connection to the database remains open as long as the data reader is active.
2. The database connection can be closed implicitly by passing the CommandBehavior.CloseConnection value to the ExecuteReader method - it ensures that the connection is closes when the data reader is closed.
3. Use typed accessor methods (GetInt32, GetString etc.) if the column's data type is know while reading data from the reader. This reduces the amount of type conversion required, improving performance.
4. If you want to stop pulling data from the server to client, call Cancel method on SqlDataReader before calling Close method. Cancel method ensures that the data is discarded. Calling Close directly however, will make the reader pull all the data before closing the stream.

The main advantage of the SqlDataReader approach over the DataSet approach is that the former avoids the object creation overhead associated with a DataSet. Note that the DataSet object creation will result in creation of many other objects like DataTable, DataRow, DataColumn and the Collection objects used to hold all these sub-objects.