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.



`

DataSet/DataAdapter vs DataReader

DataSet/DataAdapter	DataReader
Database Connecting Mode. Disconnected architecture.	Database Connecting Mode. Connected architecture.
Data Navigation: Bidirectional i.e, data can navigate back and forth	Data Navigation: Unidirectional i.e, Forward Only
Read / Write: Both Read and Write operations are possible.	Read / Write: Only Read operation can be carried out.
Data Handling: Handles text file, XML file and Database table.	Data Handling: Handles Database table.
Storage Capacity: Temporary Storage Capacity i.e, in-memory cache of data	Storage Capacity: No storage
Accessibility:Can contain or access more than one table and relationships.	Accessibility:Can contain or access only one row at a time.
Speed: Less faster than data reader.	Much faster than DataSet.

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

Update

Delete

Search

Clear

Insert

First

Last

Previous

Next

DataGridView

• DataGridView provides a visual interface to data. It provides a powerful and flexible way to display data in a tabular format.
• DataGridView control can be used to show read-only views of a small amount of data, or scale it to show editable views of very large sets of data.
• With the DataGridView control, you can display and edit tabular data from many different kinds of data sources.

Provider Question

Is it possible to establish the connection with database without provider? If yes then explain the condition where the provider is not used? [5] **

Connection - oriented architecture makes use of "data providers" such as
1. Microsoft SQL Server Data Provider : (namespace:System.Data.SqlClient)
2. OLEDB Data Provider : (namespace:System.Data.Oledb)
3. ODBC Data Provider : (namespace:System.Data.Odbc)

It is not possible to establish connection with database without provider but once data is retrieved from the database and placed in the DataSet using another ADO.NET object – the DataAdapter , the provider is no longer required to maintain connection with the database. And the condition where provider is not required is in connectionless architecture . However to update the database a reconnection is required.

Once you make objects of DataAdapter(sda) and DataSet(ds) and write ad.Fill(ds) , this dataset gets its buffer (all the tables) from the database and now connection with database automatically breaks. All the future queries gets executed on this buffer (all the tables) and then automatically, this updates your database after. In summary, your database is used only two times:
1. when dataset gets its buffer;
2. when updating the database takes place and rest all the time remains "Disconnected".

By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance.