ADO.NET is Microsoft's latest data access technology. As an integral part of the .NET Framework, ADO.NET is not an upgrade to ActiveX Data Objects (ADO) but a complete redesign. I remember the time when I was first introduced to the ADO.NET environment. As an ADO programmer, I was baffled by the new ADO.NET object model. I had pages of legacy code written against a Recordset object that had disappeared. I felt very comfortable programming the Recordset object, even with all of its shortcomings. I was confident with the handful of ADO objects that I used regularly: Command, Connection, Recordset, Error, Field, Parameter, Property, Record and Stream. It was a small, familiar working set. It took a little time to adjust to the new ADO.NET model. If you are new to ADO.NET, you too are probably wondering what happened to the Recordset. When transitioning from ADO to ADO.NET you don’t necessarily need to let go of everything you learned, but you have to let go of the dependency of the ADO object model. In this article I will outline some of the primary differences between ADO and ADO.NET and introduce you to some of the new objects in the ADO.NET object model. 

What’s in a Namespace?

One of the new changes in .NET is the introduction of namespaces. Simply put, a namespace is just a grouping of related classes. It's a method of putting classes inside a container so that they can be clearly recognized from other classes with the same name. A namespace is a logical grouping rather than a physical grouping. The physical grouping is accomplished by an assembly, which equates most directly to a dynamic link library (DLL), COM object, or OCX module, but a namespace does not always equate to a functional library.

How to Connect to the Database

Connections are used to communicate with databases, and are represented by provider-specific classes such as SQLConnection. Connections haven’t changed much with the introduction of .NET. The primary difference you will notice as a programmer is that we are utilizing a different class to create the connection. Below is an example of how to connect to a database using the Connection object in ADO and ADO.NET.

ADO Code Example

ADO.NET Code Example

Viewing the Data

In ADO the primary object we worked with to navigate through and manipulate data was the Recordset object. In ADO.NET the Recordset object no longer exists. Instead it has been replaced with three primary objects; the DataReader and DataAdapter for connected recordsets, and the DataSet for disconnected recordsets.

The DataReader object allows you to perform a single pass through a set of records as efficiently as possible and is used for dealing with large, read-only data. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader. The DataReader is similar to a Recordset object with CursorType = adOpenForwardOnly and LockType = adLockReadOnly.

There are two main situations where you would consider using DataReaders. If you use an application development model where you hand-code your user interface rather than use data binding, and hand-code your updates using SQL or stored procedures, then the DataReader provides an efficient tool for accessing relational data. In all other situations, DataReaders are useful where processing requires that you look up state held in a database (but you don’t need scrolling), binding, XML, or automatic updating capabilities including populating a list, or validating a product code.

The DataAdapter provides the association between the .NET Data Providers and the DataSet. It is essentially the mediator facilitating all communication between the database and a DataSet. The DataAdapter reads the data from a database into a DataSet with its Fill method. It also writes the changed data from the DataSet to the database calling the Update method. The DataAdapter provides four properties that represent database commands: SelectCommand, InsertCommand, DeleteCommand, and UpdateCommand. However, there are cases where the DataAdapter and DataSet objects are bypassed. In those cases, the DataReader object is used.

The DataSet is the core component of the disconnected architecture of ADO.NET that caches data locally on the client. The DataSet is explicitly designed for data access, independent of any data source. Another feature of the DataSet is that it tracks changes that are made to the data it holds before updating the source data. The DataSet also fully supports XML. It contains methods such as GetXml and WriteXml that respectively produce and consume XML data easily. The DataSet is similar to a Recordset with CursorLocation = adUseClient, CursorType = adOpenStatic, and LockType = adLockOptimistic. However, the DataSet has additional capabilities over the Recordset for managing application data.

ADO Code Example

set rsCustomers = Server.CreateObject("ADODB.recordset")

rsCustomers.Open "Select * from Customers", objConnection

do until rsCustomers.EOF

    for each x in rsCustomers.Fields

       Response.Write(x.name)

       Response.Write(" = ")

       Response.Write(x.value & "<br />") 

    next

    Response.Write("<br />")

    rsCustomers.MoveNext

loop

rs.close

objConnection.close

ADO.NET Code Example

Dim sSQL as string

Dim drSQL

  sSQL = "SELECT * FROM Products"

Dim cmdSQL as new SqlCommand(sSQL, objConnection)

  cmdSQL.CommandType = CommandType.Text;

  objConnection.Open();

  drSQL = oSelCmd.ExecuteReader();

  DataGrid1.DataSource = oDr;

  DataGrid1.DataBind();

Object Model Comparison

Now that you are familiar with the differences between ADO and ADO.NET connections and data display methods, let’s look at a comparison of the two object models. The graphical comparison in Figure 1 shows the breakdown of components within the two object models. It should be noted that this is not a complete representation of the entire object model.

Figure 1. Comparison of ADO and ADO.NET Object Models

Conclusion 

In this article we looked at the primary differences between ADO and ADO.NET. We have seen how new ADO.NET classes provide all of the features of classic ADO as well as additional functionality. Learning the new object model may take some time and hopefully this article helped ease your way into using ADO.NET.

References

How to convert an ADO application to ADO.NET

ADO.NET Architecture

ADO Object Model

Sharee English (MCSD, MCAD, MCT) is the Director of Information Services at SeattlePro Enterprises, an IT training and consulting company. She started her career as a programmer, delving into Web technologies almost twenty years ago. Today she is a highly educated executive with background in software development, training, authoring, management, operations, administration and sales. Sharee holds a Master of Arts in Management (emphasis in Information Systems), a Bachelor of Science (B.S.) in Computer Science and a B.S. in Mathematics.