Comparison of Ado.net and ADO

ado| comparison

You can understand the functionality of ado.net by comparing the features of ado.net with the specific features of an ActiveX data object (ADO).

In-memory representation of data

In ADO, the memory representation of data is a recordset. In Ado.net, it is a dataset. There are important differences between them.

Number of tables

The recordset looks like a single table. If the recordset will contain data from multiple database tables, it must use a JOIN query to combine data from each database table into a single result table.

Instead, the dataset is a collection of one or more tables. Tables in a dataset are called data tables; they are, specifically, DataTable objects. If the 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. This allows the dataset to mimic the structure of the underlying database.

Datasets typically also contain relationships. A relationship within a dataset is similar to a foreign key relationship in a database, that is, it causes rows in multiple tables to be associated with one another. For example, if the dataset contains a table about investors and another table about the stock purchases of each investor, the dataset might also contain a relationship to connect the rows of the investor table and the corresponding rows of the purchase table.

Because a dataset can hold multiple separate tables and maintain information about the relationships between tables, it can save a much richer data structure than a recordset, including self-associated tables and tables with many-to-many relationships.

Data navigation and cursors

In ADO, you use the ADO MoveNext method to scan the rows of a recordset sequentially. In ado.net, rows are represented as collections, so you can access a particular row through a table, either sequentially through any collection, or through an ordinal index or a primary key index. The DataRelation object maintains information about master records and detail records, and provides methods that allow you to obtain records related to the record being manipulated. For example, starting with the "Nate Sun" line from the investor table, you can navigate to the set of rows in the Purchase table that describe their purchase.

A cursor is a database element that controls the visibility of record navigation, the ability to update data, and other users ' changes to the database. Ado.net does not have intrinsic cursor objects, but rather contains data classes that provide traditional cursor functionality. For example, the ability to provide a forward-only, read-only cursor in the Ado.net DataReader object. For more information about cursor functionality, see data access technology.

Minimizes time to open a connection

In Ado.net, the time to open a connection is sufficient to perform database operations, such as Select or update. You can read rows into a dataset and then use them without maintaining a connection to the data source. In ADO, a recordset can provide connectionless access, but ADO is designed primarily for connection access.

There is a significant difference between disconnected processing in ADO and ado.net. In ADO, you communicate with the database by calling an OLE DB provider. But in ado.net, you pass data adapters ( OleDbDataAdapter, SqlDataAdapter, odbcdataadapter, or OracleDataAdapter objects) with Database communication, which invokes the API provided by the OLE DB provider or the underlying data source. The main difference between ADO and ado.net is that in Ado.net, the data adapter allows you to control how changes made to the dataset are transferred to the database by performing performance optimizations, executing data validation checks, or adding any additional processing.

Note data adapters, data connections, data commands, and data readers are components that make up the. NET Framework data Provider. Microsoft and Third-party vendors may provide additional providers that can also be integrated into Visual Studio. For information about different. NET data providers, see . NET data providers.

Sharing data between applications

It is much easier to transfer ado.net datasets between applications than it is to transport an ADO connectionless recordset. To transfer an ADO disconnected recordset from one component to another, use COM marshaling. To transfer data in Ado.net, use a dataset that can transfer XML streams.

Compared to COM marshaling, the transfer of XML files offers the following advantages:

Richer data types

COM marshaling provides a limited set of data types (those defined by the COM standard). Because the dataset transport in Ado.net is based on XML format, there is no restriction on the data type. As a result, components of a shared dataset can use any rich set of data types that these components will typically use.

Performance

Transmission of large ADO recordsets or large ado.net data gatherings using network resources; As the volume of data increases, so does the pressure on the network. Both ADO and ado.net allow you to minimize the data that you transmit. But Ado.net also offers another performance advantage: Ado.net does not require data type conversions. For ADO that requires COM marshaling to transfer a recordset between components, you need to convert the ADO data type to a COM data type.

Penetrate the firewall

A firewall can affect two components that attempt to transmit disconnected ADO recordsets. Keep in mind that firewalls are typically configured to allow HTML text to pass, but prevent system-level requests, such as COM marshaling, from passing.

Because the component uses XML to Exchange Ado.net databases, the firewall can allow the dataset to pass.