When migrating a VB6 legacy application, obtaining a working piece of VB.NET code is only part of the story. To complete the transition to the .NET world you also want to convert your database-access code to ADO.NET.

The Upgrade Wizard that comes with Visual Studio doesn't offer any option to upgrade ADODB code, with a good reason: "traditional" code translators can't automatically convert from ADODB to ADO.NET, no matter how sophisticated the translator is. (More on this below)

Fortunately, VB Migration Partner isn't a "traditional" code conversion. We have already proved that it correctly translates many VB6 features and keywords that many "migration experts" swore that were impossible to translate automatically. VB Migration Partner 1.20 supports *all* the major VB6 features, inclduing Gosubs, Variants, graphic statements, drag-and-drop, DDE, and a lot more.

After we dealt with the complete set of VB6 features, we decided to focus our attention on the challenges of porting ADODB-based code to ADO.NET.

The result of our efforts is Code Architects' ADOLibrary, a revolutionary tool that makes ADODB-to-ADO.NET migration one or two orders of magnitudes simpler, faster, and less expensive.

In a nutshell, ADOLibrary is a set of native .NET classes that behave exactly like their ADODB counterparts. For example, the ADOConnection object supports all the methods, properties, and events of the ADODB.Connection object, with exactly the same syntax and the same behavior. The same holds true for the ADORecordset class, the ADOCommand class, and ancillary classes such as ADOParameter, ADOField, and ADOProperty.

ADOLibrary is currently in internal beta stage, but it already supports forwardonly-readonly cursors and client-side cursors with optimistic batch updates. We even and transparently support advanced ADODB programming techniques, such as asynchronous events and alternate update strategies (the Update Criteria property), for the benefit of demanding ADODB gurus out there.

With its vast support for most ADODB features, ADOLibrary enables you to migrate thousands of database-related statements in a matter of hours. This is perhaps 100-200x faster than a manual migration and 10-20x more productive than any other code translator on the market. In practical terms, it can save you weeks or months in a real-world migration project and significantly reduce migration costs.

The first ADOLibrary example

Let me give you an idea of what the ADOLibrary works. Say that you have the following piece of VB6/ADODB code:

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    ' connect to Northwind
    cn.Open "Provider=SQLOLEDB.1;Initial Catalog=Northwind;..."
    cn.CursorLocation = ADODB.adUseClient
    rs.Open "Select * from Products ", cn, ADODB.adOpenStatic, _
        ADODB.adLockBatchOptimistic
    
    ' Load a listbox with all product names
    Do Until rs.EOF
        ListBox1.AddItem rs.Fields("ProductName").Value
        rs.MoveNext
    Loop

Here's the VB.NET code that uses the ADOLibrary, as generated by VB Migration Partner:

    ' Assumes: Imports CodeArchitects.ADOLibrary

    Dim cn As New ADOConnection
    Dim rs As New ADORecordset
    ' connect to Northwind
    cn.Open("Provider=SQLOLEDB.1;Initial Catalog=Northwind;...")
    cn.CursorLocation = ADOCursorLocation.adUseClient
    rs.Open("Select * from Products ", cn, ADOCursorType.adOpenStatic, _
        ADOLockType.adLockBatchOptimistic)
    
    ' Load a listbox with all product names    
    Do Until rs.EOF
        ListBox1.AddItem(rs.Fields("ProductName").Value)
        rs.MoveNext()
    Loop

As you see, the VB.NET code is basically the same as the original VB6 code, except for the different class and enum names. Even more important, the code is perfectly equivalent to the original VB6 code

If you are an ADODB developer you will find yourself comfortable with the generated code, which is as maintainable as the original VB6 code, except it leverages ADO.NET's superior performance and scalability. You are immediately productive and don't have to study ADO.NET, because our ADOLibrary hides the many differences between the two object models.

Don't let the ADODB-like syntax fool you, though: ADOLibrary relies on ADO.NET objects exclusively and has no dependency on COM or ADODB. It's like having ADO.NET with an ADODB dress.

But the ADOLibrary is more than just an ADODB clone, because it exposes all the ADO.NET structures that it uses internally in addition to all the properties and methods "inherited" from ADODB.

For example, the ADORecordset object exposes the inner DataTable object that stores the individual records. You can therefore bind the "recordset" to .NET controls and data grids. Or you can optimize the converted code as follows, and improve it to keep track of both the product name and the product ID:

    ' Load a listbox with all product names
    ListBox1.ValueMember = "ProductID"
    ListBox1.DisplayMember = "ProductName"
    ListBox1.DataSource = rs.DataTable     ' use native .NET binding!

Thanks to the DataTable member, you can rely on .NET databinding to implement master-detail relationships, advanced data formatting, input validation, and so forth. Just keep in mind is that all these additions and fixes are optional, because the converted .NET code will work immediately as-is.


Why you can't obtain ADODB-to-ADO.NET functional equivalence with a traditional code translator

At least another VB6 conversion tool vendor claims that their product can handle ADODB to ADO.NET conversions by means of code transformation techniques. However, they don't go into many details about which features are converted and which ones require some (or a lot of) manual fixes.

The problem in automatic conversion of ADODB code to ADO.NET is two-fold. First and foremost, it is impossible to achieve full functional equivalence. Second, filling the gap between ADODB and ADO.NET by means of code transformations is that you end up generating verbose (and often illogical) code that is very hard to maintain and evolve in the future.

Consider the following trivial code snippet:

    Set cnNorthwind As New ADODB.Connection
    Set rsOrders As New ADODB.Recordset
    cnNorthwind.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI"
    rsOrders.Open "SELECT * FROM Orders", db, ADODB.adOpenKeyset, _
        ADODB.adLockOptimistic

Notice that this example uses server-side keyset cursors, that aren't supported by ADO.NET. Keysets are typically used when you need to modify data in the database, therefore the closest ADO.NET structure that can perform the same task is the combination of a DataSet plus a DataAdapter. This is the best code that a conversion tool that relies solely on code transformation can generate

    Dim cnNorthwind As New SqlConnection
    Dim rsOrders As New DataSet
    cnNorthwind.Open("Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI")
    Dim cmd As New SqlCommand
    cmd.Connection = cnNorthwind
    cmd.CommandText = "SELECT * FROM Orders"
    Dim da As New SqlDataAdapter(com.CommandText, com.Connection)
    adap.Fill(rs)

Up to here, the converted code behaves more or less like the original VB6 code. However, the actual problems arise now. For example, consider that

  • the original ADODB code typicall uses a Do Until rsOrders.EOF loop to visit and possibly modify each record in the database. The loop can be transformed into a For Each loop that visits all the rows in the DataTable, but the database is updated only when the DataAdapter's Update method is invoked.
  • when using a keyset on a locked record, you get a runtime error when you modify one or more fields and then move away from the current record. Conversely, the optimistic lock used by the DataAdapter.Update throws a single error when any record in the DataTable fails to update. The two models greatly differ, so expect a lot of manual fixes in this area.
  • The example shows a simple SELECT query, however many VB6 apps retrieve (and update) by means of queries or stored procedures with parameters. ADODB and ADO.NET mechanisms for retrieving and using stored proc parameters are very different and this gap can't be filled by writing some code. Besides, the .NET CommandBuilder.DeriveParameters method has several defects and can't be used in production code, thus in practice you are forced to define all the query parameters manually in code.

It isn't at all surprising that our competitors don't provide any code sample that shows how their tool converts these (and many other) ADODB basic features, such as Recordset and Connection events, transactions, and bookmarks. Not to mention more advanced ones such as the Update Criteria property and the Resync method.

In practice, we estimate that a traditional code translator can automate no more than 10-15% of the statements that read, write, and process data stored in a database. Is 15% enough to qualify for ADODB-to-ADO.NET automatic conversion?

Our competitor also claims that their tool can convert code in a database-agnostic fashion. In .NET parlance, it means that they generate code that uses objects in the System.Data.Common namespace exclusively, e.g. DbConnection and DbCommand. You might believe that it's a good approach at writing database-agnostic code, until you see how ADOLibrary solves the same problem.

Internally, ADOLibrary uses the .NET objects in the System.Data.Common namespace, therefore it is inherently database-agnostic. When it's time to create a concrete class, by default ADOLibrary instantiates the objects in the System.Data.OleDb namespace (e.g. OleDbConnection, OleDbCommand, and so forth), and is therefore as database-agnostic as the original ADODB code. However, this is just the default behavior and you can override it by simply setting a global property.

For example, if your application is designed to work with Microsoft SQL Server exclusively, you can have ADOLibrary instantiate concrete classes in the System.Data.SqlClient namespace, which delivers faster and more scalable code. To enable this optimization you just need to add the following single statement:

    ADOConfig.LibraryKind = ADOLibraryKind.SqlClient

That's it! No need to be confused with base classes or other similar annoyances! Wink

For even more flexibility, you can read the value of the LibraryKind property from the configuration file, to let your end users customize the application for the database they actually use, without you having to recompile your code. Talk about flexibility!