Mapping ADO Objects to ADO.NET

Mapping ADO Objects to ADO.NET

Building on the previous discussion, let s now see how to adapt your existing code to use the new managed data objects in place of ADO. On the whole, it is a fairly simple process. We ll start by going over generic technology mappings to give you an idea of where to begin, and then we ll move on to some implementation details.

In general, the most direct mapping for ADO is to the OleDb namespace under System.Data, although if you expect to be using Microsoft SQL Server, the SqlClient providers will offer the best performance. An early beta version of the .NET Framework had an ADO provider namespace. It was decided, however, that this name would be too confusing, since the only real commonality was the use of the underlying OLEDB database providers. Therefore, the namespace was renamed to OleDb. This change was meant to emphasize to developers that the OleDb provider is to be used to access the various OLE DB database providers (as was ADO). Remember, however, that ADO is a COM-based interface to OLE DB data providers. The OleDb namespace contains managed interfaces to OLE DB data providers and actually lacks certain features found in ADO (hierarchical Recordsets come to mind).

For the purposes of this discussion, we will limit comparisons to those between ADO and OleDb. Keep in mind, however, that every example using classes from the OleDb namespace has a direct equivalent in the SqlClient namespace (which is specific to SQL Server). So if you are using a SQL Server database, moving between the two managed providers is often as easy as changing the class name s prefix from OleDb to Sql (that is, OleDbCommand becomes SqlCommand). Easy, right?

Connection and Command Objects

The Connection and Command objects still exist in ADO.NET. There are, however, significant differences between the ADO and ADO.NET classes. In ADO the Connection object not only represents your connection to a database but also can be used to execute database commands. In addition, other ADO objects (Command and Recordset) had ActiveConnection properties that accepted either an existing Connection object or a provider string, which would cause the object to create a new Connection object. This arrangement led to confusion because it was possible to execute database commands using all three of these objects, leading to wildly different coding practices from method to method (let alone from project to project).

ADO.NET is much more straightforward. Connection objects are provider specific (such as OleDbConnection) and are used only to represent a connection to a database. There is no way to use them to execute a statement against a database. That is the job of the OleDbCommand class. This class is comparable to the ADO Command class in that it can be used to execute run-time queries and stored procedures. Setting properties such as CommandText and CommandType should be instantly familiar to the seasoned ADO developer in this context. There is an important difference, however, in that it is possible to be explicit about the kind of query being performed. The simple Execute method of the ADO Command object has been replaced with four separate methods that indicate expectations regarding any given query. The methods specify what data type you want back, if any:

• ExecuteReader Execute a query and return a DataReader object.

• ExecuteScalar Execute a query and return a single value.

• ExecuteXmlReader Execute an XML query and return an XmlReader object.

• ExecuteNonQuery Execute a query with no results.

What this change means is that if you are using the Execute method on your ADO Connection objects to perform queries you will need to create an OleDbCommand object to do the actual work. This separation of functionality will hopefully lead to a more consistent coding style across applications. Although it can be painful to make the necessary adjustments in your code, we consider this an improvement over classic ADO. The following code illustrates what changes are needed to upgrade a simple ADO example:

' Visual Basic 6 Method Using ADO Sub ExecuteSql( connStr As String, statement As String )    Dim conn as New ADODB.Connection    Dim rs as ADODB.RecordSet    conn.Open connStr    Set rs = conn.Execute( sqlStatement )  End Sub

' Visual Basic .NET Equivalent Method Using ADO.NET Sub ExecuteSql( connStr As String, statement As String )    Dim conn as New OleDbConnection( connStr )    Dim cmd as New OleDbCommand( statement, conn )    conn.Open()    Dim dr As OleDbDataReader = cmd.ExecuteReader() End Sub

Recordsets

Not all Recordsets are created equal. Depending on the settings you use, they can take several different forms, leading to various consequences for application performance. As we discussed in the previous section, ADO.NET seeks to separate the different types of functionality into specialized classes, both so that there is never any question as to the kind of data you are working with (disconnected, forward-only firehose, and so on) and so that the classes can be optimized for a particular kind of data, rather than having to be general-purpose.

Forward-Only Recordsets

The forward-only Recordset is by far the most common type. It is the kind of Recordset that ADO generates by default (which is probably why it is the most common, performance advantages aside). Often this type of Recordset is referred to as the firehose Recordset because the data is read in a continuous stream, and once it has been read, it s gone.

ADO.NET has a direct equivalent to the firehose: the DataReader object. The DataReader is always forward only, and unlike a Recordset, a DataReader can never be disconnected. Also, it doesn t support random access under any circumstances. To simplify reading data from streams and to standardize on a common way to read data, the DataReader shares many interfaces and implementation details with other stream reader objects in the .NET Framework.

There are other implementation differences as well that can cause some initial confusion for developers. When a DataReader object is first returned, the record pointer starts before the first record in the data stream (instead of starting at the first record in the stream). A call to the Read method must be made before the first record is loaded, and successive calls to Read are necessary to continue moving through the data stream until the end is reached (signaled when Read returns False). Here is a quick comparison of the use of ADO firehose Recordsets and the ADO.NET DataReader (note the differences in the loop structures):

' Visual Basic 6 Forward-Only Recordset Access Sub VB6RecordSetExample(connStr As String, statement As String)    Dim conn As New ADODB.Connection    Dim rs As ADODB.Recordset    conn.Open (connStr)         Set rs = conn.Execute(statement)    Do Until rs.EOF       Dim str As String       Dim i As Integer       For i = 0 To rs.Fields.Count - 1          str = str & rs.Fields(i).Value       Next       Debug.Print str        rs.MoveNext    Loop End Sub ' Visual Basic .NET DataReader Example Sub VBNETDataReaderExample(ByVal connStr As String, _    ByVal statement As String)    Dim conn As New OleDbConnection(connStr)    Dim cmd As New OleDbCommand(statement, connStr)    conn.Open()    Dim reader As OleDbDataReader = cmd.ExecuteReader()    While reader.Read()       Dim str As String = "", i As Integer       For i = 0 To reader.FieldCount - 1          str &= reader(i)       Next       Debug.WriteLine(str)    End While End Sub

Disconnected Recordsets and Dynamic Cursors

A disconnected Recordset is retrieved in much the same way as a forward-only Recordset. So is a Recordset that uses a dynamic cursor. This is not the case with a DataSet, which requires, at least in the following example, a DataAdapter to handle the data population. Thus, while ADO had common methods for creating different kinds of Recordsets, ADO.NET has specific methods for creating different forms of data repositories.

' This is a Visual Basic 6 Dynamic Cursor Recordset Example Function VB6DynamicRSExample(connStr As String, _    statement As String) As Recordset    Dim rs As New ADODB.Recordset    rs.Open statement, connStr, adOpenDynamic         Set rs = conn.Execute(statement)    Set VB6DynamicRSExample = rs    Set rs = Nothing End Function

' This is a Visual Basic .NET DataSet Example Function VBNETDataSetExample(ByVal connStr As String, _    ByVal statement As String) As DataSet    Dim adapter As New OleDbDataAdapter(statement, connStr)    Dim ds As New DataSet()    adapter.Fill(ds)    Return ds End Function

The other major difference between the disconnected Recordsets and DataSets lies in how iteration is handled. The disconnected or dynamic Recordsets can use any of the Move functions (Move, MoveFirst, MoveLast, MoveNext, and MovePrevious) or can access rows using an ordinal reference or column name. A DataTable (contained within a DataSet) can be iterated using either an ordinal reference or For Each, as in the following example.

Dim row As DataRow For Each row In ds.Tables(0).Rows    ' Do stuff Next Dim i As Integer For i = 0 To ds.Tables(0).Rows.Count - 1    row = ds.Tables(0).Rows(i)    ' Do other stuff Next