Computer Science: ADO .net tutorial

Computer Science: ADO .net tutorial: "ado .net tutorial contain"

ado .net tutorial

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.

Is Dataset slower than Data Reader due to…?

A) Datasets can be 10+ times slower than Data Reader at retrieving data from DB. I assume this is due to overhead of Datasets having to deal with relations etc.

B) But is the speed difference between Datasets and Data Reader due to Datasets having to retrieve more data ( information about relations ... ) from DB, or due to application having to do more processing, or both?

C) I assume Data Adapter uses Data Reader under the hood and thus the number of commands application needs to execute in order to retrieve 100 rows with Data Adapter is equal to or greater than number of commands app needs to execute if these 100 rows are retrieved directly by Data Reader?!

2) Does Data Reader retrieve one row at the time or one field (of a particular row) at the time?