Migrating Code and Concepts from ADO "Classic" to ADO.NET

William Vaughn
Beta V Corporation

May 2004

Applies to:
   Microsoft® ADO.NET
   Microsoft® Visual Studio® .NET
   Microsoft® Visual Studio® 6.0

Summary: ADO.NET is becoming the new standard for data access, but what about all of that COM-based ADO code you wrote over the last five years or so? Learn more about design choices and decision points for migration. (15 printed pages)

Contents

Why Aren't Folks Switching to Visual Studio .NET?
Why Should Developers Switch to .NET?
Can You Keep Your COM-based ADO Code?
Converting Objects or Operations?
Converting ADOc to ADO.NET at the Object Level
Converting ADOc to ADO.NET Operations
Conclusion
Related Books

Why Aren't Folks Switching to Visual Studio .NET?

According to the folks I chat with at Microsoft and in the field, there are still a lot of you out there that have not converted from Microsoft® Visual Studio® 6.0 to Microsoft® Visual Studio® .NET. Based on these conversations, I think that some of the reasons for this reluctance are:

• Companies have a lot invested in functional Visual Studio 6.0 code. It's working, so why "fix" it? If an application is competitive, stable and cheap to run and maintain, it might be worth keeping around.
• The problems they have with Visual Studio 6.0 and COM have been tolerated for some time and they understand how to avoid them; it's like living with an old (paid-for) car that's burning oil.
• .NET is new and different. It would mean new investments in training, recoding, retesting and deployment. Managers and their development staff are naturally cautious of new technology and often take years to adopt something this innovative, especially in hard times.
• Some developers think that the new .NET languages and Microsoft® ADO.NET are simply harder to use. Part of this might simply be the fear of the unknown or departing from one's comfort level.
• Companies don't think they need XML Web Services to solve their problems.

This last bullet is what far too many developers and Visual Studio .NET tire-kickers report (to me) as their excuse for not switching. For some reason, they've been lead to believe that .NET means XML Web Services, and that's it. I tell them that there are many ways, other than XML Web Services, to design and configure application architectures. Sure, XML Web Services have their place, but it's not the "ultimate" .NET solution.

Some developers and their managers also tell me that they are currently supporting "many" users in client/server configurations and they don't need or want to convert to .NET. To determine what "many" really means, I conducted a poll of the number of users developers typically support with their applications—how far do these applications have to scale? What I heard was not surprising. The number of developers supporting over 500 users was relatively small. Even those with web sites did not have to support more than a few thousand hits a day to run their businesses. Many department-sized systems and small businesses fall into this category. This means that the data architectures most developers need to create can be handled easily by ADO.NET without resorting to extreme measures or complex architectures to gain "ultimate" scalability and performance. It means that simpler designs and development techniques can work, and these are also cheaper and easier to build and maintain with .NET. Yes, .NET makes sense for simple applications too.

I also hear that developers (and their managers) want to reuse existing code; they want to leverage the investment they made in Microsoft® Visual Basic® 3.0, Microsoft® Visual Basic® 4.0, Microsoft® Visual Basic® 5.0 and Microsoft® Visual Basic® 6.0 code over the years. Each time a new version of Visual Basic arrived, they were asked and encouraged to adopt the newest and coolest data access and application development paradigms. Each version was different enough that many of the existing techniques no longer worked or (worse yet), worked differently in often subtle ways. However, enough code did work that most of their investment in the previous version could be leveraged.

As Visual Basic and data access technologies evolved, they passed on the "dominant genes" of earlier versions. This helped developers feel comfortable with the new technology, but after several generations of "gene-splicing", we ended up with somewhat of a velociraptor. The new ADO was fairly fast, but with very sharp teeth and a voracious appetite for tourists and memory. It was clear to the .NET team that ADO.NET really needed to clean house and start again. I quip that the only thing that's the same in ADO.NET is the fonts. There are more similarities than that, but I think it's easier to think of ADO.NET (and Microsoft® Visual Basic® .NET) as two entirely new paradigms that bear only passing similarity to their ancestors.

Why Should Developers Switch to .NET?

On the other hand, I think there are a litany of reasons that developers and their managers have decided to switch to .NET and ADO.NET. The first and foremost reason, I believe, is COM and "DLL Hell" issues. .NET applications can virtually (or completely) eliminate COM dependencies. Applications can be deployed easier than ever before and without touching the target system's registry, and I'm talking about Windows Forms applications. No longer will developers have to worry that their fully tested application will fail the first time someone installs a new version of Microsoft Office or some other application that upgrades the version of MDAC loaded on their system. While this article focuses on ADO.NET and conversion issues, not the (copious) benefits of .NET, I'm convinced (as are millions of .NET development shops) that the increased productivity, code stability, improved deployment scenarios, enhanced data management and other reasons to numerous to mention justify the expense they bear to convert.

Can You Keep Your COM-based ADO Code?

Can developers count on reusing their old COM-based ADO (what I call "ADO Classic" or ADOc) code? Let me put it this way: Yes, you can keep most of it, but you won't want to. Using ADOc in a .NET application would be like trying to feel cool beach sand between your toes while wearing rubber boots. When you access any COM object from a Common Language Runtime (CLR) application (like Visual Basic .NET or C#), all references have to traverse a COM interop "wrapper" that translate from COM to .NET and back. Since .NET does not support all of the data types or interfaces supported in Visual Basic 6.0 (and vice-versa), some translation (more like transmogrification) has to be done. This also means that a few behaviors or "side-effects" that you used (or experienced involuntarily) when using COM components (like ADOc) are not implemented or end up somewhat jumbled when the object is accessed through the COM interop layer.

I found a number of these side-effect behaviors that don't seem to work very well in ADO.NET. For example, in my earliest books on ADO (and RDO) I encouraged use of the fast, simple "stored procedure as Connection method" technique. For example, the following code calls the stored procedure "AuthorsByYearBorn" in Visual Basic 6.0.

myConn.AuthorsByYearBorn 1940, 1947

When ADOc code using this technique is migrated to .NET, the conversion wizard warns you that this late-bound method might not work. The problem is that it appears to work (and it does work) in a .NET application, but only the first time it's invoked. After that, you'll get a "Syntax error…" exception. The development team at Microsoft has given up on this one because ADOc was "tricking" the OLE DB interface by simply passing the stored procedure and its arguments through to IDispatch for processing. It did not make sense to "fake" this behavior in Visual Basic .NET. There's a workaround (albeit cumbersome). All you have to do is convert the single line of code used to call the stored procedure into about a dozen using a Command object with a Parameters collection.

Another problem you'll face when moving from ADOc to ADO.NET is trying to go halfway through the conversion process; as when leaving an existing ADOc component in place and calling it from a .NET application. Sure, you can take this route, but many applications implement middle-tier components that send and receive ADOc Recordset objects between tiers. The problem you'll face manifests itself like a groping hand reaching out of the ooze as you try to get ADO.NET to generate an ADOc Recordset: it does not know how. While ADO.NET's OleDb data provider knows how to read and convert a Recordset object into a DataTable, it can't do the reverse, even using XML and asking nicely. Unfortunately, the versions of "standard" XML used are different. I wrote a paper to show how to make this conversion work, but it's as hard as getting a feral cat to wear a pink tutu. See my web site (Much ADO about Data: Doing the Impossible (Again) for details.

Converting Objects or Operations?

When I wrote my book "ADO.NET and ADO Examples and Best Practices for Visual Basic Programmers", I wanted to create a "Rosetta Stone"—a document that maps out the difference between ADO classic and its replacement, ADO.NET. I found that it would have been easier if I tried instead to translate Egyptian hieroglyphics to Greek and chisel them into a stone tablet. If you take this approach and look at the ADOc objects one-at-a-time in an attempt to understand how to convert your code line-by-line, I expect you'll be frustrated. Sure, there are many similarities, but you might very well miss the bigger picture, what the ADO classic code does. I think it's easier if you take each section of ADOc code and document its function, side-effects, input parameters, output parameters and return value. Once that's done, you'll find it pretty simple to identify those operations that can be easily converted to equivalent ADO.NET code and which are going to require rearchitecting. For this article, I'm going to both provide a guide to converting individual objects, method and properties, as well as a guide to overall query strategies that focuses on converting data access operations. This approach also might prove helpful for those converting from DAO or RDO as well.

As I was writing this, I realized that many of you have decided to convert your code and skills from Visual Basic to C#. The examples I'll be showing here are in Visual Basic 6.0 and Visual Basic .NET. I expect that any C# developer will be able to read and understand these examples, but if you can't, I suggest using one of the Internet conversion utilities to translate the code. There won't be any magical techniques shown, so it should be easy, even for C# developers.

Converting ADOc to ADO.NET at the Object Level

Tip: Keep in mind that ADO.NET and Visual Basic .NET are very different. One of the most significant changes for ADOc developers is how garbage is collected. In Visual Basic 6.0, when an object falls from scope without being cleaned up (closed, set to nothing), the garbage collector (GC) immediately runs the tear-down code and flushes the object. This is not the case in Visual Basic .NET, now the GC runs only when necessary. This means it might be minutes to hours before an object (like a connection) is closed and its resources released. Because of this, you'll find that it's vitally important in ADO.NET to dispose of your trash properly and promptly.

The Data Source Providers

One of the first issues you'll encounter as you start the conversion process is how ADO.NET interfaces with your data source. Consider that ADO.NET no longer forces your code to depend on OLE DB or ODBC. Instead, the .NET Framework uses built-in or add-on .NET data providers to access data sources. These include:

• SqlClient: This built-in managed provider accesses SQL Server TDS directly. It's designed to connect to Microsoft® SQL Server™ versions 7.0 or later.
• OracleClient: This built-in managed provider is designed to access Oracle databases.
• Odbc: This built-in managed provider is designed to access any data source exposed with an ODBC driver.
• OleDb: To connect to SQL Server 6.5, JET/Access databases or anything without a "native" or managed provider. It's the slowest of all providers as it uses COM interop to access the OLE DB provider selected in the ConnectionString.

As a rule of thumb, you should use a managed (.NET-based code) provider over an unmanaged (COM-based) provider that's native to your data source. If your database is not listed above, look on the Web for additional .NET data providers—there are many available—virtually all mainstream data sources now have managed .NET data providers.

Exception Handling

No paper on conversion would be worthwhile without (at least) briefly mentioning the differences in exception handling. Visual Basic 6.0 error handling is fairly crude but over the years we were able to identify most of the errors (by number and message) that were likely to be tripped in our applications. Given that the Visual Basic runtime and ADOc generated these errors and that both of these components are no longer involved in an ADO.NET application, you can imagine what will need to be done to your error (now "exception") handlers; you're going to have to pretty much start over. Visual Basic .NET and ADO.NET expose exceptions very differently—at different times, for different reasons and with different messages and (in many cases) without familiar error numbers.

The ADO.NET "Disconnected" Paradigm

Before we start looking more deeply into the ADOc to ADO.NET object differences, we need to discuss some of the fundamental (and philosophical) differences between the two data access interfaces. For example, while ADOc permits developers to choose between server-side or client-side cursor management, ADO.NET's native capabilities are limited to client-side rowset management, it does not implement any form of "cursor" as we know and use in ADOc. As I discuss in an article in Hardcore Visual Basic, it's entirely possible to create an ANSI server-side cursor with ADO.NET, but this requires the developer to manage all aspects of the cursor—code and functionality that ADOc included. ADO.NET exposes very few properties to help manage and maintain server state. This is because it's designed and tuned to work with ASP.NET applications that don't need server-side cursors or state management.

ADO.NET objects (such as the DataTable and DataSet) expect to work without a connection. Sure, a Connection is used initially to fetch the data, but after that, the connection is not used to perform any of the data manipulation methods such as sorting, filtering or searching. A connection is not required until it comes time to post the changes to the database. Because there is no need for an active Connection, ADO.NET is ideal for ASP.NET development or any architecture that does not need to manage server state. This approach is similar to the ADOc "disconnected" Recordset (which was the initial proving ground for this technology). To implement disconnected rowsets, ADO.NET uses the DataSet and DataTable (which I describe in a minute) as in-memory data stores. These can be sorted, filtered, searched or bound to.

Another big difference between ADOc and ADO.NET is in how disconnected updates are handled (the only kind ADO.NET knows how to do on its own). When changes are made to an ADOc Recordset object, they are persisted in memory until Update or UpdateBatch are executed. At this point ADOc constructs an appropriate action query (such as an UPDATE statement) based on the Update Criteria property and executes it. This means the action query can change from invocation to invocation as needed. In some cases, a TIMESTAMP can be used while at other times, field values can be used to test for concurrency collisions.

In contrast, ADO.NET takes another entirely different approach when processing updates to its disconnected rowsets (DataTable objects). One important change is the introduction of the ADO.NET DataAdapter (SqlDataAdapter) object. This single object can be used to manage the associated Connection, execute a SELECT query, construct one or more DataTables and populate a DataSet with the results, all with a single method (Fill). It also exposes the Update method used to post changes made to the in-memory DataTables. Instead of taking the time to construct what it thinks is the right action query, ADO.NET requires that the developer build three Command objects at design time (an UpdateCommand, an InsertCommand and a DeleteCommand), or use the CommandBuilder to construct them on the fly. These commands are executed each and every time the Update method is called, once (a round trip to the server) for each row that's changed. Unfortunately, there is no Update Criteria property to tune the CommandBuilder's or Update method's behavior. This means that developers must construct their own action queries to map to their own collision detection routines. This should not be a significant problem if concurrency is managed through stored procedures.

The ADOc Connection Object

The ADO.NET Connection object is still responsible for providing a gateway (a connection) to the selected data source. Each .NET data provider exposes a Connection object. For example, the SqlClient provider exposes the SqlConnection object. While these objects don't all share identical functionality, properties or methods all implement the same core objects. Each of these still uses a ConnectionString property as a way to set its properties. In ADO.NET, there are very few Connection object properties that can be set except by using the ConnectionString. For example, you must specify security credentials in the ConnectionString as there are no arguments on the Open method to change them at the last second. For the most part, the ConnectionString settings are similar to the string used in ADOc with a few notable exceptions:

• In non-OleDb .NET data providers, you cannot specify the "Provider" name or version, it's not needed.
• You must specify user credentials, or SSPI security. There are no other ways to specify credentials at runtime, except through the connection string.
• You can only use ODBC DNS or OLE DB UDL shortcuts to manage connection strings in Odbc and OleDb respectively, not in SqlClient or OracleClient.
• Errors in the ConnectionString property settings can trip a trappable exception at runtime—ADO.NET does not simply create an "Extended Properties" section of the ConnectionString.

If you're using a DataAdapter (SqlDataAdapter), the ADO.NET Connection object can be managed for you automatically. This means you don't have to use the Open or Close methods. However, if you do open the connection in code, you must close it while it's still in context or the connection pool will overflow. See my article on managing the connection pool or the next section on converting operations for more details. Shown below is typical ADOc code written in Visual Basic 6.0 used to create a Connection object and open the connection followed by the ADO.NET equivalent. Note the subtle differences in the connection string—the "provider=" clause is missing. In ADO.NET, the SqlConnection constructor captures the connection string, instead of passing it to the Open method. Remember that the ADO.NET Open method accepts no arguments so you can't pass in credentials as you open.

ADO Classic "Open"

Set cn = New ADODB.Connection
cn.Open "provider=sqloledb;data source=betav9;  
initial catalog=biblio", "admin", "pw"

ADO.NET "Open"

cn = New SqlClient.SqlConnection("data source=betav9;  
     initial catalog=biblio;uid=admin;pwd=pw")
cn.Open()

The ADOc Command Object

Again, the ADO.NET Command object seems very similar to the ADOc Command object, it's spelled the same, but the differences are in the details. For example, you can't execute a Command object by making it a "method" of the Connection object as you can in ADOc. The SqlClient namespace exposes the Command object as "SqlCommand". The familiar CommandText, CommandType, CommandTimeout properties are still present and so is the Parameters collection. The property settings for these properties are roughly the same, but limited (or enhanced) by the capabilities of the .NET data provider. One notable exception is how input parameters are marked in the CommandText. Each provider uses its own technique. For example, the SqlClient provider uses (just) position insensitive named parameters prefixed with the "@" character. The OracleClient uses named (but position-sensitive) parameters prefixed with ":". The ADO.NET Connection property is the equivalent of the ADOc ActiveConnection property in most respects, and the ADO.NET Prepare method approximates the ADOc PreparedProperties property, but there is no equivalent for the State property. As we'll see in a minute, the methods used to execute an ADO.NET Command object are different as well.

Note:   In .NET applications, we depend heavily on the object's "New" constructor that can dramatically reduce the number of line of code you have to write (and support). These constructors simply accept arguments that are used to set the object's initial property values. Be careful though. These constructors are executable code and can trip exceptions if the values supplied do not conform to the type definitions or other criteria specified by the class.

Shown below is a typical (albeit simple) ADOc Command and the equivalent code in ADO.NET. Note that they are not all that different.

ADOc Create a Command Object

Set cmd = New ADODB.Command
With cmd
    .CommandText = "SELECT Author, Year_Born  
         FROM Authors WHERE Author Like ?"
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("@AW",  
       adVarChar, adParamInput, 20)    
    Set .ActiveConnection = cn
End With

ADO.NET Create a Command Object

cmd = New SqlClient.SqlCommand
With cmd
     .CommandText = "SELECT Author, Year_Born  
       FROM Authors WHERE Author Like @AW"
     .CommandType = CommandType.Text
     .Parameters.Add("@AW", SqlDbType.VarChar, 20)
     .Connection = cn
End With

The ADOc Parameters Collection

As with ADOc, the ADO.NET Parameters collection is used to manage the input, output and return value parameters for the Command object. The code used to construct the Parameters collection in ADO.NET is very different as you can leverage the generous Parameter object constructors to add new members to the Parameters collection. In many cases, several lines of code can be reduced to one for each Parameter. As I said earlier, the CommandText Parameter markers (which are different in ADO.NET) still need to map 1:1 to the members of the Parameters collection by either name or position. Developers will also discover that the Parameters collection is more intimately connected to the binding and rowset concurrency mechanisms. There are now Parameter properties that can address the current, previous and proposed versions of data items to be passed to remote procedures or queries. I'm also working on an article that describes how to populate "sparse" parameter lists that might appear here sometime in the future.

Executing ADOc Commands

While you can execute an ADO.NET Command object and (optionally) return a rowset, as you can in ADOc, the traditional Recordset is gone in ADO.NET. There is no 1:1 equivalent as ADO.NET handles rowsets very differently. For the first time since DB-Library, ADO.NET uses the Command object (SqlCommand) to expose a low-level data stream in the form of a DataReader (SqlDataReader). This stream is similar in some respects to the read-only, forward-only (default) Recordset in ADOc. When using a DataReader, developers must manually manage the connection (as in ADOc) and retrieve the rows and columns one-at-a-time. There are no "bulk" methods such as GetString or GetRows exposed by the ADO.NET Command object. While the DataReader approach is fast, it's virtually featureless and requires a lot of code. In an ASP.NET application, developers can bind to a DataReader, but this can't be done in any other type of application. I don't encourage universal use of the DataReader (especially in architectures where it can't be bound) as it's too easy to mismanage and the improvement in performance is not worth the copious amounts of code needed to manage it properly. I've seen far too many problems with the DataReader to give them a general endorsement. However, other developers swear by them (or at them) and use the DataReader quite heavily and I'll admit that there are suitable uses for DataReaders but you really have to pay attention and be sure the connection is freed in time.

Executing Action and Other Specialized Queries

Not all of the queries developers need to execute are SELECT statements. There are many instances where action (UPDATE, INSERT, DELETE) queries need to be executed. There are also other cases where it's more efficient to execute a query and not return a rowset, return only a single value or perhaps even return an XML string. To support these query types, ADO.NET Command object not only exposes an ExecuteReader method to return a DataReader, it also exposes an ExecuteNonQuery, ExecuteScalar and ExecuteXMLReader methods as well. Each of these methods are discussed in an article published in the October 1 2000 issue of Hardcore Visual Basic.

Shown below are two examples of executing a simple action query in ADOc and ADO.NET. Note that the ability to pass a SQL statement as a method argument is not supported in ADO.NET.

ADOc Execute an Action Query

cn.Execute "UPDATE Authors SET Year_Born = 1950 WHERE AU_ID =25",
 intRA, adCmdText

ADO.NET Execute an Action Query

cmd2 = New SqlClient.SqlCommand("UPDATE Authors 
    SET Year_Born = 1950 WHERE AU_ID =25", cn)
intRa = cmd.ExecuteNonQuery

Executing Stored Procedures

Very few of the production applications I write or review use hard-coded queries as these simplified examples illustrate; virtually all of them execute stored procedures. ADOc and ADO.NET both support stored procedures by permitting you to name the stored procedure in the CommandText and specifying that you're calling a stored procedure (adCmdStoredProc or CommandType.StoredProcedure). Capturing the RETURN value and OUTPUT parameters is also supported in both ADOc and ADO.NET in very similar ways.

The following code snippets compare how ADOc and ADO.NET can be used to call a stored procedure. Notice that the ADO.NET code is a bit more verbose. Consider that Microsoft® IntelliSense® fills in much of this code so as you enter the method arguments or the constructors. You simply pick from lists of valid property or argument settings.

ADOc: Execute a Stored Procedure

Set cmd = New ADODB.Command
With cmd
 ' Stored procedure expects a single Parameter
    .CommandText = "MyStoredProcedure"      
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@ReturnValue",  
       adInteger, adParamReturnValue)
    .Parameters.Append .CreateParameter("@AW", adVarChar,  
       adParamInput, 20)
    .Parameters.Append .CreateParameter("@ParmOutput", adVarChar,  
       adParamOutput, 20)
    Set .ActiveConnection = cn
    .Parameters("@AW").Value = 25
    cmd.Execute (intRa)   ' Fetch Rows Affected and discard rowset
    txtShowReturn.Text = .Parameters("@ReturnValue").Value
    txtShowOutput.Text = .Parameters("@ParmOutput").Value
End With

ADO.NET: Execute a Stored Procedure

cmd = New SqlClient.SqlCommand("MyStoredProcedure", cn)
With cmd
     .CommandType = CommandType.StoredProcedure
     .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = 
        ParameterDirection.ReturnValue
     .Parameters.Add("@AW", SqlDbType.VarChar, 20).Value = 25
     .Parameters.Add("@ParmOutput", SqlDbType.VarChar,  
        20).Direction = ParameterDirection.Output
     intRa = cmd.ExecuteNonQuery ' Return rows affected, discard rowset
     txtShowReturn.Text = .Parameters("@ReturnValue").Value.ToString
     txtShowOutput.Text = .Parameters("@ParmOutput").Value.ToString
End With

The ADOc Recordset Object

As you know, the ADOc Recordset object is used to capture and manipulate rowsets returned by the executed queries. If the query or stored procedure returns more than one rowset, you have to reuse the same ADOc Recordset to capture each in turn. ADO.NET manages rowsets with an entirely new set of classes. As I said, there is no direct equivalent of the Recordset object in ADO.NET. There are, however, several ways to handle query results. We've already talked about the DataReader, but ADO.NET also implements a DataTable class that separates the schema and data into two collections: Columns and Rows. This permits more efficient data storage and makes it easier to pass data without unnecessary schema overhead. While it might appear to be so, ADO.NET data is not stored in XML behind the scenes. However, DataTable objects can be converted to or loaded from XML using a variety of methods. But be advised that the XML extruded by ADOc to persist a Recordset cannot be read by ADO.NET to create a DataTable. You can use the ADO.NET OleDb data provider to convert an ADOc Recordset into a DataTable, but there is no (easy) mechanism to go back.

The real advantage of storing rowsets in DataTable objects is ADO.NET's ability to manage multiple DataTable objects in a new ADO.NET object called a "DataSet". DataSets are used to provide rowset management, binding and relational links for two or more tables, whether or not they are related in the host database. This means there's no need for a separate SHAPE provider and its unique syntax, or to manually extract multiple rowsets returned from stored procedures. To implement inter-table relationships, developers manually define these relationships in code. They cannot be derived from the data source. The ability to tie two or more DataTables together means that it's possible to "relate" rowsets extracted from several different data sources and create custom parent-child relationships in memory, something that simply wasn't possible in ADOc without use of the SHAPE provider, and then it was a PIA.

Updating Recordset Data

When it comes time to update the ADOc Recordset, you could choose a variety of techniques. You could choose to update either an updatable server-side cursor or client-side "disconnected" Recordset tied to a base table. However, most larger shops don't permit direct table access so the ADOc Update method was of limited use. That's because there are no built-in mechanisms to tie appropriate stored procedures to the ADOc Update method.

In contrast, ADO.NET exposes the DataAdapter, which can link existing stored procedures to the Update method so different stored procedures are called when update, delete and insert operations are called for. This is similar to the code generated by the Visual Basic 6.0 Data Object Wizard, but with far fewer issues. What ADO.NET can't do is batch up a series of operations and execute them with a single round-trip. This feature won't appear in ADO.NET until Visual Studio 2005.

One of the most difficult problems ADOc developers face when confronted with the DataTable is how to navigate through it. In ADOc, developers used "Move" methods to position a pointer to a specific row in the Recordset. This "current" row was used to manage binding and expose a single row at-a-time. ADO.NET's DataTable object exposes a rowset in the Rows collection so there are no "move" methods to move a cursor from one row to the next. Instead, developers simply use an integer pointer to address specific rows in the Rows collection or iterate through the rows using traditional collection techniques such as For Each. For example, the entire set of rows returned by a query can be examined through use of a loop such as the one shown below. This code creates a DataAdapter (SqlDataAdapter) Fill method to open the connection, run the query, build and populate a DataTable (for each rowset returned) and close the connection.

da = New SqlClient.SqlDataAdapter("MyStoredProcedure", cn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
With da.SelectCommand.Parameters
    .Add("@ReturnValue", SqlDbType.Int).Direction =  
       ParameterDirection.ReturnValue
    .Add("@AW", SqlDbType.VarChar, 20).Value = "V%"
    .Add("@ParmOutput", SqlDbType.VarChar, 20).Direction =  
       ParameterDirection.Output
End With
ds = New DataSet
da.Fill(ds, "MyTable")    ' Connect, run query, fill DataTable, close
Dim dRow As DataRow
'Step through the rowset one row at-a-time
For Each dRow In ds.Tables("MyTable").Rows
     ListBox1.Items.Add(dRow.Item("Author").ToString & "-" & _
 dRow.Item(1).ToString)
Next

Converting ADOc to ADO.NET Operations

I think a more rational approach to converting an ADOc application focuses on the operations used in the application, not on the individual ADOc objects, their properties or method calls. For example, if you have an ADOc routine that calls a parameterized query, don't worry about how the syntax is differs between ADOc and ADO.NET, simply setup an ADO.NET Command object that uses the same Parameter settings as the ADOc code. Once this is done once or twice, developers can easily repeat this process for each invocation. However, in many cases data access applications weave the data structures returned from the data provider into other code instead of isolating this code in an easily replaceable object (that might reside logically or physically in the middle tier). This complicates the conversion process quite a bit. Capturing rowsets, binding their values, processing updates are all handled differently in ADO.NET. For example, ADOc built action queries on the fly at execution time—ADO.NET expects the developer to provide the UPDATE, DELETE and INSERT SQL commands in code. ADOc also handles multiple resultsets and rowsets differently than ADO.NET. Since ADO.NET can define and manage DataTable relationships, it's possible to eliminate considerable code from an ADOc application and simply let ADO.NET manage these relationships for you. These new techniques can be somewhat challenging to learn, but once developers understand the technology their productivity increases dramatically. They let ADO.NET do the work instead of having to manage, sort, filter and find data in the rowsets themselves.

Conclusion

The process of converting applications begins with converting the developers who are experienced with working on ADOc applications. Once the team is properly trained, the code conversion is made far more easily. Other issues could have been discussed (but were not, due to the scope of this paper) including data binding, concurrency management, server-side cursors, pessimistic locks and many more. My books "ADO.NET and ADO Examples and Best Practices for Visual Basic Programmers" and "ADO.NET Examples and Best Practices for C# Programmers" (APress) are written with the ADOc developer in mind. They're designed to assist developers convert their skills and code to efficient ADO.NET coders and code. These books, coupled with a few days of training can bring most teams up-to-speed quickly on the new technology.

Related Books

ADO.NET and ADO Examples and Best Practices for VB Programmers

ADO.NET Examples and Best Practices for C# Programmers

About the Author

William (Bill) Vaughn is an industry-recognized author, mentor and subject-matter expert. He's been in the computer industry for over thirty years working with mainframe, minicomputer and personal computer systems as a developer, manager, trainer, marketer, support person and writer. After 14 years at Microsoft, Bill stepped away to work on his new books, mentoring and training seminars. His area of specialization is focused on data access and especially Visual Basic and SQL Server. He's written six editions of the "Hitchhiker's Guide to Visual Basic and SQL Server" and two editions of "ADO.NET and ADO Examples and Best Practices for Visual Basic Programmers". There's even a C# version "ADO.NET Examples and Best Practices for C# Programmers". Bill is a top-rated speaker and frequents conferences all over the world including VSLive, Developer Connections and PASS (Professional Association for SQL Server). He's also written a wealth of articles for magazines such as SQL Server Magazine, Visual Basic Programmer's Journal, .NET Magazine and many others. Bill is currently working on a new book "Hitchhiker's Guide to SQL Server 2000 Reporting Services". He's available for consulting, mentoring or custom training. See www.betav.com.