DataReader, DataAdapter & DataSet - When to use?

DataReader, DataAdapter & DataSet - When to use?

ADO.NET offers two central Data Access Components. In this Article we will discuss about their features and when to use what.

ADO.NET provides two central Data Access Components. The excellent thing is that, they are common across all Databases, be it SQL Server or other competitive databases. Its only the namespace to be used, that differs, while using a Database other than SQL Server.

The two Data Access Compnents are:


1. DataSet

2. The .NET data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects.


Its common that the doubt arises on when to use DataReader and DataAdapter, DataSet.

The thumb rule I would suggest is,


1. 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.


DataReader offers a forward only read stream of access to the records. It is very useful in cases where we just grab the data from the database to display in DataGrid, Label and other Webcontrols.

However, DataReader requires the connection with the database open until its operation is completed.


2. 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.

Hope it provided some insight into DataReader, DataSet and DataAdapter.