Differences between classic ADO and ADO.NET

Classic ADO and ADO.NET

ADO is based on COM Technology and it used OLEDB data provider for accessing data . It has a limited number of data types which are defined by the COM standard. ADO.NET basically designed by .NET Framework for smooth interaction of application and database, it support large and rich datatypes.

Classic ADO used OLE DB data provider to access data and is COM based, while ADO.net uses XML as the format for transmitting data to and from your database and applications. It is compatible with any component on any platform that understands XML.

ADO works with connected data architecture. 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. This is barring, of course, you have to develop special routines to pull all your data into temporary tables.

ADO.NET uses data in a disconnected manner. 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. This makes ADO.NET efficient to use in networking environment.

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

ADO allows you to create client side cursors only, whereas ADO.NET gives you the choice of either using client side or server side cursors. In ADO.NET, classes actually handle the work of cursors. This allows a choice for programmers. In networking development environment , this choice is crucial in creating efficient applications.

In ADO.Net we can send multiple transactions using a single connection instance, whereas in ADO, we cannot send multiple transactions using a single connection instance.

In ADO, it is sometime create problems because firewall prohibits many types of request, while in ADO.net there is no such problem because XML is completely firewall-proof.