Wednesday, July 11, 2007

Past, Present, and Future of ADO.NET

ADO .NET was designed specifically for the Web with scalability, statelessness, and XML in mind. The ADO.NET interface is readily accessible using any of the .NET-compliant languages.

ADO.NET is essentially a collection of classes that expose methods and attributes used to manage communications between an application and a data store. An integral part of the .NET Framework, ADO.NET simplifies a variety of development needs, including the creation of front-end database clients and middle-tier business objects used in distributed, data-intensive applications.

ADO.NET is not just another object model for working with data. In essence, it is ADO revisited on a wider-range, cross-platform scenario. ADO.NET is ADO extended to work in cooperation with any software component, on any platform, that understands XML.

ADO .NET supports a rich disconnected view of data as represented by data sets, cursors, batching, data shaping, connection pooling, meta data access, and a host of other techniques that will allow you to build performance, reliability, and robustness in your data applications.

Evolution of ADO.NET from ADO

Under the .NET Framework, data access is handled by a set of classes called ADO.NET; these are essentially an augmentation of the existing ActiveX Data Objects (ADO). There are some big changes, both internally and on the surface. The most obvious internal change is that ADO.NET is based entirely on XML. Externally, the biggest change is that there is no Recordset object.

The Recordset mechanism was used to interact with the results of an SQL Query; later, it grew into a structure that supports disconnected or even fabricated recordsets. The challenge it faced was the sophisticated SQL generation layer capable of simulating optimistic locks without needing to hold physical locks that otherwise restrict scalability.

Under ADO.NET, the functionality of the Recordset has been split into three groups, which are explained below:

  1. The DataReader object allows you to perform a single pass through a set of records as efficiently as possible whereas in ADO, this was achieved using a forward-only, server-side cursor.
  2. The DataSet and DataSetCommand objects allow you to create a client-side cache of one or more related recordsets and process it in a disconnected fashion whereas in ADO, a client-side cursor was used for much the same thing. By separating out these two very different sets of functionality, ADO.NET can provide tailored support for each approach, as well as exposing more power.
  3. Updation of data through pessimistic locking is not supported in ADO.NET. It is targeted at n-tier, Web-friendly applications and it does not intend to tread on ADO's toes in all areas. In ADO, updating data is done through connection-oriented pessimistic locking.

The other major differences between ADO and ADO .NET are as follows:

  1. XML in ADO.NET allows data to pass through a firewall. Data cannot pass through a firewall through the ADO recordset.
  2. ADO.NET supports the typed dataset, which means they support all the dataset capabilities built into .NET, including binding and XML DOM interoperation. In ADO, recordset fields are not strongly typed and are not supported by IntelliSense.
  3. ADO.NET DataSet can hold multiple tables and therefore is very flexible. In ADO, a recordset holds data only for one table.

Full Size Image)


ADO.NET is an attempt to encourage developers to use the disconnected model. Disconnected data is more scalable because it reduces the demand on database servers. It is much easier to scale the middle tier using parallel servers than it is to scale the data tier, and therefore scalable applications need to use data tier resources carefully. DataSets are always client-side.

The DataSet object contains a collection of zero or more DataTable objects, each of which is an in-memory representation of a single table. DataColumns and Constraint collection together make up the schema of the table. A DataTable also contains a DataRows collection, which contains the actual data in DataSet.

The DataSet contains a DataRelation collection. A DataRelation object allows you to create association between rows in one table and rows in another table.

DataSet neither knows nor cares about databases or SQL. It simply allows tables of data to be manipulated, exchanged, or bound to user interfaces. There are two standard ways of getting tables of data into a DataSet. One is by using a DataSetCommand object that turns the results of a database query into XML. This generates database updates in much the same way that the ADO Client Cursor Engine does, but with far greater control.

The second approach is to work directly with XML. The DataSet object has methods for reading and writing XML data and schemas, and can work closely with an XMLDataDocument object. The XMLDataDocument class in the .NET Framework inherits from the general-purpose XMLDocument class, extending it with specific functionality for manipulating tabular data and forming a bridge between a DataSet and a generic XML DOM.

To create a DataSet using an OLE DB Provider, you need to use an instance of the ADODataSetCommand class.

The following example explains how the dataset is loaded with data:

Dim Sql As New ADODataSetCommand("select emp_id, emp_fname,
emp_lname from employee",
Dim ds As New DataSet()
Sql.Fill(ds, "Employee")

The above dataset is is filled with data executed by a query.

Multiple DataTables can be created into a single DataSet, whereby relationships can be created between them to aid navigation.

The following code explains the relationship of two tables within a dataset:

Dim t1() As DataRow
Dim t2() As DataRow
Dim dr As DataRelation
Dim ds As New DataSet()
dr = New DataRelation("Employee",

The above example uses two tables, Employee and Department, to relate the department ID of the employee table with the department ID in the Department table. This establishes a parent-child relationship between these two tables.


All .NET data providers are designed to be lightweight. They consist of a minimal layer between the data source and your code. This extends functionality without sacrificing performance.

There are four core objects that make up a .NET data provider:

The main design goals of the .NET Data Provider was to implement a generic client layer enabling the re-use of our existing data access components for connecting to remote data sources. This has been achieved with both our Managed and Unmanaged .NET Data Providers, each of which has a common namespace that eliminates the need to recompile applications should the need arise to change the remote data source.

Managed Data Provider

The .NET Data Provider is built with managed code, enabling it to run completely within the .NET Framework runtime, delivering better security and performance. This generic managed .NET Data Provider connects to the remote data source via one of two forms currently.

UnManaged Data Provider

The Unmanaged .NET Data Provider enables connectivity to any ODBC data source by acting as a bridge between ADO.NET and ODBC. This provider is provided as a stop-gap solution enabling connectivity to data source for which managed .NET Providers are not already available. So, it does not provide the benefits of security and performance available from its managed counterpart.

The following example explains the steps of a data adapter for SQL Server.

Dim da As SqlClient.SqlDataAdapter
Dim cm as SqlClient.SqlCommand
Dim cn as SqlConnection
Dim ds as Data.DataSet
Dim StrSql as String
Cn=Initial Catalog=pubs;DataSource=local);userid=sa;pswd=;
StrSql=select * from user
Cm=new SqlClient.SqlCommand(StrSql,new SqlClient.SqlConnection(cn))

The above example executes the query and reads all data from the user table.

Associated Namespace of ADO.NET


The System.Data namespace consists mostly of the classes that constitute the ActiveX Data Objects architecture in the .NET Framework (ADO.NET). ADO.NET provides the programming interfaces for building data-driven Web and Windows applications.

This namespace includes the centerpiece of the ADO.NET architecture, the dataset, which represents a relational database in memory.


The System.Data.Common namespace consists of the classes shared by the OLE DB and the SQL Server .NET data providers.

A .NET data provider describes a collection of classes used to manage connections between an application and a data store, such as a relational database or a hierarchical XML file.


The System.Data.OleDb namespace is the OLE DB .NET Data Provider.

A .NET data provider describes a collection of classes used to manage connections between an application and a data store, such as a relational database or a hierarchical XML file. This namespace includes the core OleDb classes for accessing and manipulating any OLE DB-compliant data source, and essentially consists of the OleDbConnection, the OleDbCommand, the OleDbdataReader, and the OleDbDataAdapter classes.


The System.Data.SqlClient namespace is the SQL Server .NET Data Provider.

A .NET data provider describes a collection of classes used to manage connections between an application and a data store, such as a relational database or a hierarchical XML file. This namespace includes the core SqlClient classes for specifically accessing and manipulating an SQL Server data source, and essentially consists of the SqlConnection, the SqlCommand, the SqlDataReader, and the SqlDataAdapter classes.


The System.Xml namespace is an integral player in the data access scheme for the .NET framework. This is a close relationship with the dataset that relies entirely on XML for its own internal schema and data representation.

Filtering and Sorting in ADO .Net

ADO.NET supports two fundamental approaches for performing these operations:


The various features supported by ADO.Net 2.0 are:

All the above features are achieved through a simple solution.

Advantages and Disadvantages of ADO.NET

The benefits of using ADO.NET are are follows:

The drawbacks of using ADO.NET are:

Future of ADO.NET

In the future version of ADO.NET, the following points will be taken care of:

No comments: