Differences between classic ADO and ADO.NET

ADO	ADO.NET
ADO is base on COM Technology : Component Object Modelling.	ADO.NET is based on CLR : Common Language Runtime.
ADO works in connection oriented environment , means it requries continuous active connection. (That means, when you access the data from data source, such as viewing or updating data, ADO recordset is keeping connection with the data source)	ADO.NET works in disconnected environment , means does not require continuous connection. (That means, when you access data, ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates and immediatly close the connection after operation)
It has limited number of data types which are defiend by the COM standard.	It support large number of datatypes .
In ADO you communicate with the database by making calls to an OLE DB provider .	In ADO.NET you communicate with the database through a data adapter (an OleDbDataAdapter, SqlDataAdapter, OdbcDataAdapter, or OracleDataAdapter object).
ADO stores data in binary format .	ADO.NET stores data in XML format i.e, parsing of data.
ADO can't be integrated with XML because ADO have limited acess of XML .	ADO.NET can be integrated with XML as having robust support of XML .
In ADO, the memory representation of data is RecordSet . (Classic ADO has one main object that is used to reference data, called the Recordset object, it acts like a single table or query result. If an ADO recordset is to contain data from multiple database tables, it should use a JOIN query, which assembles the data from the different database tables into a single result table.) .	In ADO.NET, the memory representation of data is DataSet or DataAdaptor . (In ADO.NET, you have various objects that allow you to access data in various ways. ADO.NET DataSet can contain multiple tables from various data sources. The tables within a dataset are called data tables. The DataSet object will actually allow you to store the relational model of your database. If a dataset contains data from multiple database tables, it will typically contain multiple DataTable objects. That is, each DataTable object typically corresponds to a single database table or view) .
In ADO, we cannot send multiple transactions using a single connection instance.	In ADO.NET we can send multiple transactions using a single connection instance.
In ADO, it sometimes create problems because firewall prohibits many types of request.	While in ADO.NET there is no such problem because XML is completly firewall-proof .
Transmitting an ADO disconnected recordset between application is harder process where you use COM marshalling.	Transmitting an ADO.NET dataset between applications is much easier than transmitting an ADO disconnected recordset.

ADO.NET Architecture

ADO stands for Active Data Object and ADO.NET is a set of .NET libraries for ADO. ADO.NET provides a bridge between the front end controls and the back end database. System.Data namespace is the core of ADO.NET. The two key components of ADO.NET are Data Providers and DataSet.

Data Providers

A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. .NET Framework data providers are lightweight, creating a minimal layer between the data source and code, increasing performance without sacrificing functionality.
The .Net Framework includes mainly three Data Providers. They are

1. Microsoft SQL Server Data Provider : SQL Server uses the SqlConnection object
2. OLEDB Data Provider : OLEDB uses the OleDbConnection Object
3. ODBC Data Provider : ODBC uses OdbcConnection Object

And the Data Providers in the ADO.NET consists of four Objects. They are

Connection Object:

The Connection Object provides physical connection to the Data Source. Once the Database activity is over, connection should be closed.

Command Object:

The Command Object in ADO.NET is use to executes SQL statement or stored procedure at the Data Source.

DataReader Object:

The DataReader object is an alternative to the DataSet and DataAdapter combination. DataReader is used to read the data from database and it is a read and forward only connection oriented architecture. DataReader will fetch the data very fast when compared with dataset. Generally we will use ExecuteReader object to bind data to datareader. To bind DataReader data to GridView we need to write the code like as shown below:

DataAdapter Object:

The DataAdapter object acts as a bridge between the DataSet and the database. This DataAdapter object is used to read the data from database and bind that data to dataset. Dataadapter is a disconnected oriented architecture. This helps the Dataset to contain data from multiple databases or other data source.

DataSet

DataSet class provides mechanism for managing data when it is disconnected from the data source. To update the database a reconnection is required. The DataSet contains DataTable objects and DataRelation objects . The DataRelation objects represent the relationship between two tables. And the DataTable contains a collection of Data Rows and Data Coulumns Object which contains Data. There is no much difference between dataset and datatable, dataset is simply the collection of datatables.



`

DataReader vs DataSet

DataReader	DataSet
DataReader provides forward-only and read-only access to data.	DataSet object can contain multiple rowsets from the same data source as well as from the relationships between them.
DataReader is connected architecture.	DataSet is a disconnected architecture.
DataReader can not persist data.	DataSet can persist data.
It has live connection while reading data.	A DataSet is well suited for data that needs to be retrieved from multiple tables.
Speed performance is better in DataReader.	DataSet is slower than DataReader due to overhead.

DataReader vs DataAdapter

DataReader	DataAdapter
Can Access one table at a Time.	Can Access Multiple Table at a Time.
Handle DataBase Tables.	Handles XML Files, Text Files, DataBase Tables.
Only READ Operation.	Both Read/Write Operation.
DataBase Connection Mode.	DataBase DisConnection Mode.
No Storage Capacity.	Temporary Storage Capacity.
Faster than DataAdapter.	Less Faster than DataReader.

Update, Delete, Search, Clear, Insert, First & Last

Update

Delete

Search

Clear

Insert

First

Last