Data Points: Migrating from ADO to ADO.NET

Article
10/11/2019

Data Points

Migrating from ADO to ADO.NET

John Papa

Contents

The Evolution of ADO
Connections
Recordset to Readers
Single-Value Commands
Until Next Time

In recent years ADO has taken the lead as the preferred method for implementing data access in Windows®-based applications. Huge numbers of ADO applications are in use today and many developers are well versed in ADO development. With the introduction of the Microsoft® .NET Framework came ADO.NET—the evolution of ADO. While there are similarities between ADO and ADO.NET, the way they operate and their foundations are quite different. To help you make a smooth transition to ADO.NET, I'm going to take a look at how some common tasks are performed in ADO.NET.

I will discuss several data access situations, show how to tackle them with ADO, and show how they are accomplished with ADO.NET in ASP.NET using C#. I'll begin with the similarities in connecting to a data source. Then, I'll look at how the ADO Recordset object has evolved into many distinct and focused objects and methods in ADO.NET. Finally, I will explore firehose cursors, returning a single value from a rowset, and working with XML.

The Evolution of ADO

Some features of traditional ADO, such as establishing a connection to a data source, have changed little between versions. Other features have evolved considerably such as the ability to represent a disconnected rowset, to persist it to XML, and to transform it into a hierarchical rowset. One reason for the significant changes is that XML and data-shaping features were late additions to ADO; in ADO.NET they were built into the design up front.

Traditional ADO is very lightweight when compared with the data access tools of old such as DAO and RDO. One feature that made ADO so popular in n-tiered development with Visual Basic® 6.0 is its simple, easily navigated object model. The ADO Connection and Command objects translate to ADO.NET Connection and Command objects in a relatively straightforward manner, but the ADO Recordset object's features translate to several different objects and methods in ADO.NET.

ADO has remained a powerful and popular data access tool in part because of its XML features and its ability to manage disconnected rowsets. An ADO Recordset can be disconnected from its data source and Connection object by setting its CursorLocation property to adUseClient, its CursorType to adOpenStatic, and its LockType to adLockBatchOptimistic. Then, once the recordset has been opened and loaded, it can be disconnected by setting the recordset's ActiveConnection property to Nothing:

'---Disconnecting an ADO Recordset
oRs.CursorLocation = adUseClient
oRs.CursorType = adOpenStatic
oRs.LockType = adLockBatchOptimistic '— Or use adLockReadOnly
oRS.Open
Set oRS.ActiveConnection = Nothing

XML features were not integrated into ADO from the start, but as XML became more prevalent, its features were stapled onto later versions of ADO. The Save method of the ADO Recordset object can take the recordset's rows and columns and translate them to a predefined XML schema, then persist them to a file or a stream. The XML schema is not very flexible, but it was the first real attempt at persisting XML from a ADO rowset and it gave developers a glimpse of where development was heading. The recordset can also be loaded from an XML file as long as it uses the same XML schema that it expects. The following code shows how ADO can save a recordset to an XML file:

.Save "c:\MyRowSet.xml", adPersistXML

The rowset can be saved to a stream such as the output of the Response object from an ASP page. For example, some ASP code could field a request, retrieve a rowset, and stream the rowset to the Response object, which would return it to the client. The following code demonstrates how to save a recordset's contents to a Stream object. Alternatively, the Save method's first argument could be replaced with the Response object so it would stream the XML rowset to the calling browser:

Dim oStm As ADODB.Stream
Set oStm = New ADODB.Stream
oRs.Save oStm, adPersistXML

Traditional ADO and ASP have the ability to stream. Of course, with .NET this can be accomplished using Web services, which offer more features than any solution you can easily build using traditional ADO and ASP. In fact, Web services is a technology designed to process requests for data and transmit data back in XML across HTTP. While ADO can stream XML, this was an afterthought in its design. The ADO.NET DataSet object has WriteXml and WriteXmlSchema methods that allow its contents to be exported to an XML file or stream. So while the ADO Recordset's Save method has been retrofitted to be able to export its contents to XML, the ADO.NET DataSet had this functionality from the start.

Not only can ADO.NET persist a DataSet to XML and load one from XML, it also uses its XML-like structure to its advantage in other ways. For example, because the DataSet is fundamentally represented as XML, it can be passed between physical and logical layers with ease. This means that the XML can be passed through secured networks via HTTP as it takes the form of text-based XML.

Since it's built upon XML, ADO.NET can work disconnected. While a traditional ADO Recordset could either be connected or disconnected depending on several property settings (such as CursorType = adOpenStatic and CursorLocation = adUseClient), ADO.NET has dedicated RowSet objects that are either connected to a data source (the DataReader) or disconnected (the DataSet).

Connections

Creating connections with traditional ADO and with ADO.NET is a very similar process. Basically, you declare your connection object, instantiate it, set its connection string, and open it. Take a look at Figure 1. The first example demonstrates how to open a connection using ASP and ADO, and the second example shows how to open a connection using ASP.NET and ADO.NET.

Figure 1 Opening a Connection

Traditional ASP and ADO in VBScript

dim oCn 
dim sCn
set oCn = Server.CreateObject("ADODB.Connection")
sCn = "Provider=SQLOLEDB; Data Source=(local);" & _
      "Initial Catalog=Northwind;Integrated Security=SSPI"
oCn.Open sCn

ASP.NET and ADO.NET in Visual Basic .NET

Dim sCn As String = "Provider=SQLOLEDB; Data Source=(local); Initial" & _    
             "Catalog=Northwind;Integrated Security=SSPI" 
Dim oCn As New SqlConnection(sCn)
oCn.Open()

ASP.NET and ADO.NET in C#

String sCn = "Provider=SQLOLEDB; Data Source=(local); Initial" +    
             "Catalog=Northwind;Integrated Security=SSPI"; 
SqlConnection oCn = new SqlConnection(sCn);
oCn.Open();

A major difference in creating connections with ADO and ADO.NET is that ADO fits all connections to all types of data sources into a single Connection object. ADO.NET can have separate objects that represent connections to different data sources. For example, ADO.NET has a namespace called System.Data.SqlClient which houses all the SQL Server™-specific ADO.NET objects including the SqlConnection object. The SqlConnection object is built specifically to communicate with SQL Server databases and thus is the fastest and most feature-rich way of interacting with SQL Server. There is also a more generic namespace called System.Data.OleDb which can communicate with any OLE DB-compliant data source. In ADO.NET you can create multiple data provider namespaces to connect specifically with a particular data source, making access faster and more efficient and allowing each namespace to exploit the features of its targeted data provider. If an application must either change the kind of data provider on the fly or focus on a provider that does not have a specific ADO.NET connection object, it can use the OleDbConnection.

Recordset to Readers

The ADO Recordset, which behaves differently depending on how its properties are set, has been broken up into many different objects and methods in ADO.NET. This allows ADO.NET's objects to work more efficiently since its objects can concentrate on what they do best instead of trying to be a jack of all trades. The ADO Recordset can be both a connected rowset and a disconnected rowset. It can act as a forward-only, read-only (firehose) cursor or it can allow positional moves backwards, forwards, and in the middle of the rowset. An ADO Recordset can allow data changes to be made directly to the database and it can allow data changes to be saved up and sent to the database in a batch. The point is that an ADO Recordset can wear many hats. ADO.NET splits this functionality into objects that are optimized to accomplish their specific tasks. Thus, the ADO Recordset's features have been split into the DataSet object, the DataReader object, and parts of the DataAdapter and Command objects.

Forward-only, read-only cursors require a continuous connection to a data source. ADO.NET has a DataReader object that, when open, is always connected. It was written specifically for a data provider, like SQL Server, Oracle, and the more generic OLE DB provider. Thus, the SqlDataReader object can connect to a SQL Server database and act as a firehose cursor looping through tons of records if need be. The SqlDataReader provides fast-forward-only access to query results. It retrieves a record from the query results from the database and leaves the connection open so it can get the next record in succession. The ADO.NET DataReader is extremely efficient because it does not support all of the features of the ADO Recordset. It's a lean firehose-cursor machine. The examples in Figure 2 show how to implement a forward-only cursor in classic ADO and in ADO.NET. Notice that in ADO.NET, the DataReader object's Read method automatically moves the position to the next record. This eliminates the common infinite loop problem that developers fall into with traditional ADO of omitting the ADO Recordset's MoveNext method.

Figure 2 Implementing a Forward-Only Cursor

ASP and ADO

oRs.ActiveConnection = oCn
oRs.LockType = adLockReadOnly
oRs.CursorType = adOpenForwardOnly
oRs.CursorLocation = adUseServer
oRs.Open sSQL
Do While Not oRs.EOF
    Response.Write oRs("CompanyName") & "<br>"
    oRs.MoveNext
Loop

ASP.NET and ADO.NET in Visual Basic .NET

Dim oDr As SqlDataReader = oCmd.ExecuteReader() 
While oDr.Read()
    Response.Write(oDr(0) & "<br>")
End While

ASP.NET and ADO.NET in C#

SqlDataReader oDr = oCmd.ExecuteReader();
while(oDr.Read())
{
    Response.Write(oDr[0] + "<br>");
}

Another difference in ADO and ADO.NET is how the forward-only cursor is filled. In ADO, all rowsets, forward-only or otherwise, are contained within the Recordset object which are opened via the Recordset.Open method or the Connection or Command's Execute methods. In ADO.NET there is a specific method built to retrieve forward-only data to a DataReader: the command object's ExecuteReader method. This method tells the command object to specifically set up the DataReader object for the data provider to process the results in an optimized forward-only manner, as shown in the previous example. There is also a method of the command object called ExecuteXmlReader which tells the command object to set up the query results to be processed by an XmlReader object. The XmlReader object can be used to translate and process XML query results as shown in the following ASP.NET example:

' Visual Basic .NET
oCn.Open()
Dim oCmd As New SqlCommand("SELECT * FROM Orders FOR XML AUTO",oCn) 
oCmd.CommandType = CommandType.Text 
Dim oXR As XmlReader = oCmd.ExecuteXmlReader() 
While oXR.Read() 
    Response.Write(oXR.ReadOuterXml())
End While

// C#
oCn.Open();
SqlCommand oCmd = new SqlCommand("SELECT * FROM Orders FOR XML AUTO",oCn);
oCmd.CommandType = CommandType.Text;
XmlReader oXR = oCmd.ExecuteXmlReader();
while(oXR.Read()) 
{ 
    Response.Write(oXR.ReadOuterXml()); 
}

This example shows how the XML can be sent to the calling browser. However, the XML could be gathered and streamed to the Response as a whole, as well.

Single-Value Commands

At some point, most apps need to retrieve a single value from a database query. The standard way to do this in classic ADO is to set up the SQL statement and open a recordset containing the query results. Since there is only one row and one column in the query's results, it seems like overkill to get that one value. The following example retrieves a single value from a query. This example grabs the number of rows in the Orders table:

'-- ASP and ADO                
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.ActiveConnection = oCn
oRs.Open "SELECT COUNT(*) As iRowCount FROM Orders"
iCount = oRs.Fields("iRowCount").Value

ADO.NET introduces a new way of getting a single value from a query's results when you expect only one row and one column to return. The ADO.NET command object has an ExecuteScalar method which returns the first row and column's value from its associated query. This generates very little overhead as it does not have to create a rowset, find the value, and close the rowset. The ExecuteScalar method is optimized for the specific situation in which retrieving a single value is required. The following example accomplishes the same thing as the previous example, only using ASP.NET and ADO.NET with the ExecuteScalar method:

' Visual Basic .NET
Dim sSql As String = "SELECT COUNT(*) As iRowCount FROM Orders"
Dim oCmd As New SqlCommand(sSql, oCn)
oCmd.CommandType = CommandType.Text
Dim iCount As Integer = CType(oCmd.ExecuteScalar(), Integer)

// C#
string sSql = "SELECT COUNT(*) As iRowCount FROM Orders";
SqlCommand oCmd = new SqlCommand(sSql, oCn); 
oCmd.CommandType = CommandType.Text;
int iCount = (int)oCmd.ExecuteScalar();

Another way to retrieve a single value is to use an output parameter of a stored procedure. This technique can also retrieve a number of values from a single row, for example. It's available in both ADO and ADO.NET, although ADO.NET has expanded output parameter abilities. To grab an output variable's value in ADO.NET from a command object, execute the query using the ExecuteNonQuery method. This method tells ADO.NET that the query will not return a rowset, so the overhead of a DataSet or DataReader will be avoided:

' Visual Basic .NET
oCmd.ExecuteNonQuery()
oCmd.UpdatedRowSource = UpdateRowSource.OutputParameters
Dim iOrderID As Integer = _
    CType(oCmd.Parameters("@OrderID").Value, Integer)

// C#
oCmd.ExecuteNonQuery(); 
oCmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
int iOrderID = (int)oCmd.Parameters["@OrderID"].Value;

This code then sets the UpdatedRowSource property to point to the output parameters, assuming they have been set. Then, the output values can be retrieved. This was accomplished in traditional ADO using an obscure parameter on the Connection object's Execute method, but it's very straightforward in ADO.NET, as you see. Of course, ADO.NET also has a method optimized for returning a standard rowset—the command object's Execute method.

The Recordset object in traditional ADO can figure out the UPDATE, INSERT, and DELETE statements that are required for the Recordset to be able to apply to the underlying database any changes that have been made to the Recordset. While this feature is handy, it also comes with a lot of overhead as it has to go back to the database to figure out how to do this.

The ADO.NET objects can do this as well through the CommandBuilder object; however, it is also wrought with overhead. In most development situations the exact SELECT, INSERT, UPDATE, and DELETE statements are known at design time. In classic ADO there was no easy way to associate the action queries with a Recordset so that it would take advantage of them. But in ADO.NET a DataAdapter has four separate command objects associated with it to represent each action query and the SELECT statement. This allows the DataAdapter to help fill a DataSet with a query's results and tells the DataAdapter what action queries to issue against the database ahead of time. While this requires a bit more coding at design time, the extra code is a good trade-off for the performance gains (not to mention the ease of maintenance since the code is self explanatory). My May 2004 Data Points column demonstrates how to use the DataAdapter in detail.

Until Next Time

In this month's column I discussed the evolution of some key features from ADO to ADO.NET, including firehose cursors and persisting XML. In upcoming columns I will talk more about the evolution of cursors and demonstrate how to deal with concurrency issues and batch updates in traditional ADO and translate that to work with ADO.NET.

Send your questions and comments for John to mmdata@microsoft.com.

John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive.

Additional resources