Saturday, May 05, 2007

Dataset vs Datareader

One of the most common coding we do is fetch bunch of rows from the database and view them or manipulate them. In such cases, we have 2 choice with us. Either to use DataReader or DataSet. How do you decide between these choice?

It needs to be quick!!! - DataReader is quick when it comes to just read the data and your intention is not modify the data but just view them

I need to modify or cache or transfer between multiple tiers!!! - DataSet gives us a flexible data structure to handle, it is disconnected, can be cached and you can modify them and save them.

The DataReader approach is generally quicker because it avoids the overhead that is associated with created a DataSet object. The overhead that is associated with a DataSet object includes creating DataSet subobject such as DataTable, DataRows and DataColumns. However, the DataReader provides less flexibility, and is less suited to situations where you have to cache data and pass the data to components in an application that has multiple tiers. The DataAdapter used to fill the DataSet uses a DataReader internally.

DataReader is recommended when the following conditions are true:
  • You need forward-only, read-only access to data and you want to access the data as quickly as possible, and you do not need to cache it.
  • You have a data container such as a business component that you can put the data in.

DataSet is recommended when the following conditions are true:

  • You have to cache or pass the data between layers.
  • You require an in-memory relational view of the data for XML or non-XMLmanipulation. like constraint, parent child, search,
  • You want to update some or all the retrieved rows, and you want to use the batchupdate facilities of the SqlDataAdapter class.
  • You have to bind data to a control type that the DataReader cannot be bound to. Many Windows Forms controls capable of data binding require a data source that implements the IList interface. The DataSet implements IList, but the DataReader implements IEnumerable. IEnumerable supports data binding to most Web Formcontrols but not to certain Windows Forms controls.
  • You have to access multiple sets of data at the same time, and you do not want to hold open server resources.


varunkrish said...

Ok.. which one do you prefer to use ?

Praveen said...

Varun, I could not reply in a brief way, so I wrote separetly. Please check out this Dataset Vs DataReader - Again!!