Exchange Data Between VB6 and VB.NET Apps
by Andy Leonard

Technology Toolbox: VB.NET, SQL Server 2000, XML, VB6

You can use XML to persist data in both VB6 and VB.NET applications. You can persist ADO recordsets as XML in VB6, and you can persist ADO.NET data sets as XML in VB.NET. The question is: Can you use these mechanisms to achieve an exchange of data between VB6 and VB.NET applications? The answer is: "Yes, however … ."

Like me, you would probably surmise that ADO.NET allows you to read an XML-persisted ADO recordset from within your VB.NET application. Like me, you would be mistaken. Is it possible to read an XML-persisted recordset? Sure. Is it simply a matter of passing the adUseADORecordsetXMLSchema argument as a parameter when filling a data set? Far from it. (adUseADORecordsetXMLSchema doesn't exist, but I think it should.) Attempting to open an ADO recordset persisted to XML with ADO.NET generates an error (see Figure 1).

You've probably divided your .NET migration project into two phases, believing you could rewrite the Data layer in .NET to read all that XML that the business layer generates in VB6. You're not stuck—your strategy is just out of order. You can rewrite the business layer in .NET to persist data sets in XML in the ADO recordset format, with the help of a snappy DLL from Microsoft support.

The ConvertDStoRS.dll is available, at the time of this writing, on the Microsoft Product Support Services Web site, article 316337 (see Additional Resources). The source code for the DLL is provided with the download. Take a look at two applications—one for VB6 and one for VB.NET—to demonstrate what you can and cannot achieve with this DLL. You'll need access to an instance of SQL Server with the Pubs database installed for these to function properly.

Create a directory called "XML" on your C:\ drive (or elsewhere—just remember to edit the code appropriately), and place the "ConvertDStoRS.xsl" file in this directory. (Note: ConvertDStoRS.xsl is listed in the Microsoft Product Support Services article as "test.xsl"; see Additional Resources.) Decompress and open VB6_XMLOps in the VB6 IDE. Modify the SQL Server connection strings to match your instance of SQL Server, and press F5 to run the application in Debug mode (see Figure 2).

When you click on the "Write ADO RS" CommandButton, the application executes a query against the Pubs database, returning the contents of the Authors table in an ADO recordset. Next, the contents of this recordset are persisted as an XML file to the indicated path. Click on "Write ADO RS" to generate the XML file.

Decompress and open DotNet_XMLOps in the VB.NET IDE. Modify the connection strings to match your instance of SQL Server, and press F5 to run the application in Debug mode (see Figure 3). When you click on "Write .NET DS," the application executes the same query as before against the Pubs database, this time returning the contents of the Authors table to a SQLDataAdapter, which then fills an ADO.NET data set. Next, the contents of the data set are persisted as an XML file to the indicated path. Click on "Write .NET DS" to generate this XML file.

Click on "Write .NET RS" to create the last of your three XML files. "Write .NET RS" creates a data set just like "Write .NET DS" (see Listing 1). It then calls the GetADORS function in the ConvertDStoRS.ConvertToRS object. As arguments, this function takes the data set (ds), the database name (Pubs), an XSLT file (ConvertDStoRS.xsl), and the desired path\name of the output XML file. ConvertDStoRS.ConvertToRS uses the XSLT file "ConvertDStoRS.xsl" to convert the data set from ADO.NET's native data set format into ADO's native recordset format. This allows ADO to open ADO.NET data sets persisted as XML. Now you may pass an ADO.NET data set from VB.NET into an ADO recordset in VB6.

Return to your applications to test this functionality. Start DotNet_XMLOps and click on "Read ADO RS" to attempt to read the C:\XML\myVB6Schema.xml file. You should receive the error shown in Figure 1. ADO.NET cannot read an XML-persisted ADO recordset without translation. Notice you receive the same error when you click on "Read .NET RS," which attempts to read the XML file translated to ADO recordset format. Same error, same reason—both files are in the native XML format for persisted ADO recordsets. The only read operation that succeeds is "Read .NET DS," which reads C:\XML\myDataSetDotNetSchema.xml—the XML file used to persist an ADO.NET data set.

Start VB6_XMLOps and click on "Read ADO RS" to read the C:\XML\myVB6Schema.xml file. The application uses ADO to open the XML file and load it into an ADO recordset (see Listing 2). The data grid displays the contents of the recordset. Click on "Read .NET DS" to attempt to read C:\XML\myDataSetDotNetSchema.xml—the XML file persisted in ADO.NET native data set format. You receive an error indicating ADO cannot (natively) read an ADO.NET persisted data set (see Figure 4).

Click on "Read .NET RS" to attempt to read C:\XML\myRecordSetDotNetSchema.xml—the XML file created from an ADO.NET data set and converted to an ADO recordset. The results speak for themselves (see Figure 5).

One important note regarding ConvertDStoRS: This library won't handle non-ASCII characters because of the encoding attribute (or lack thereof, more accurately) in the output XML. There is a note about this in the Microsoft Product Support Services article (see Additional Resources). You have the source for the DLL and can tweak it to allow you to control encoding, but this is beyond the scope of this article.

About the Author
Andy Leonard is an MCSD, consultant, and engineer who currently lives and works in Jacksonville, Fla. In the mid 1990's, Andy developed Plant-Wide Webs, one of the first Web-based Manufacturing Execution Systems (MES). He specializes in developing business intelligence solutions for the manufacturing enterprise. Reach him at .