Integrating Your ADO Code into a Visual Basic .NET Application

Believe it or not, ADO has certain capabilities that enable it to be compatible with .NET applications and the .NET Framework. It is possible to preserve your existing investment in ADO while still taking advantage of .NET controls and performing such tasks as data binding and marshaling Recordsets through XML Web services. To this end, the .NET Framework includes some features that support transforming the old ADO Recordset into something consumable by a Visual Basic .NET application. This section discusses those features.

ADO with ADO.NET: Is It the Best Architecture?

Although it is certainly possible to use ADO within your Visual Basic .NET application, your mileage will vary as far as performance is concerned. This approach may not give the best performance, but it is functional and quick to implement, and it allows you to reuse your existing business objects. Later you may choose to change to a solution based solely on ADO.NET. The compatibility features discussed here span the chasm between the two technologies and make it possible to combine ADO with ADO.NET.

Binding Your ADO Recordset to .NET Controls

.NET provides a wide range of controls for both Web applications and Microsoft Windows based form applications. Many of these controls support data binding with ADO.NET data sources. The question is how to take advantage of your existing ADO infrastructure and still use the new controls. The answer is simple: all you need to do is convert your Recordset to either a DataSet or a Data Table. Once you have one of these managed data objects, data binding is trivial.

As we mentioned previously, the OleDbDataAdapter has the ability to handle this task. Check out the following example:

'Here is the familiar ADO stuff Dim cn As New ADODB.Connection() Dim rs As New ADODB.Recordset() cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _    "Persist Security Info=False;" & _    "Initial Catalog=Northwind;Data Source=bart") rs.Open("Select * From Employees", cn) 'This is where things start getting interesting. Here we declare not  'only the DataAdapter, but also the DataTable the data is destined for. Dim olead As New Data.OleDb.OleDbDataAdapter() Dim dt As New Data.DataTable() 'We use the DataAdapter to fill a DataTable olead.Fill(dt, rs) 'Now we bind the DataTable to the DataGrid Me.DataGrid1.DataSource = dt 'This is cleanup. Always close your objects as 'soon as you are done with them. rs.Close() cn.Close()

In this simple example, an ADO Recordset is bound to a DataGrid control on a Windows form. Figure 20-3 shows the form with the DataGrid control before and after data binding. The OleDbDataAdapter.Fill method is capable of populating a DataTable object with data from an ADODB Recordset.

In this way it is possible to take any Recordset and convert it to a managed data structure (a DataTable). Once you have done the conversion, you can bind the DataTable directly to a control, or you can add it to a DataSet and work with the table just as you would any other DataTable (including creating a DataView for the data, which we will get to a bit later on).

Figure 20-3

DataGrid before and after data binding.

Using ADO with XML Web Services

XML Web services are the cornerstone of Microsoft s .NET initiative. They offer a simplified mechanism for implementing distributed, consumable services using SOAP, an XML-based protocol that transmits over HTTP. SOAP has significant advantages over technologies such as Remote Data Service (RDS) and Distributed Common Object Model (DCOM). XML Web services will go through Internet firewalls with no problems, and the applications are loosely coupled, eliminating issues related to component registration or changing GUIDs. These features can lead to a more robust system that is not as sensitive to changes as a COM-based system. For more information on using XML Web services, consult Chapter 21.

Suppose you ve decided that XML Web services are cool and you want to replace your middle tier with one. If you use the middle tier to handle data access and pass Recordsets, however, you have a problem: you cannot pass any COM object through an XML Web service. COM objects lack the self-descriptive capabilities necessary to support XML serialization. How can you get the middle tier to work with Visual Basic .NET?

You actually have two options available, as Figure 20-4 illustrates. The one you choose depends entirely on your application s architecture.

Option 1: Leave your existing architecture in place and serialize your Recordset to and from XML. This is a good idea if you have a lot of ADO manipulation on both the client and the server tiers.
Option 2: Convert your Recordset to a DataTable or DataSet (as appropriate) and pass that back through the XML Web service. (The marshaling will be handled for you.) This option is good if the client code is used mainly for presentation or data binding and can readily be replaced with managed code.

Figure 20-4
Options for implementing XML Web services with Recordsets.

Serializing Your Recordset to XML

The standard way to pass managed objects back and forth through XML Web services is by serializing the objects to and from XML (which is usually done for you). It is also possible to do this with Recordsets, although you have to handle the serialization yourself sort of. Check out the following two Visual Basic .NET functions:

Function RsToString(ByVal rs As ADODB.Recordset) As String    Dim stm As New ADODB.Stream()    rs.Save(stm, ADODB.PersistFormatEnum.adPersistXML)    Return stm.ReadText() End Function Function StringToRs(ByVal s As String) As ADODB.Recordset    Dim stm As New ADODB.Stream()    Dim rs As New ADODB.Recordset()    stm.Open()    stm.WriteText(s)    stm.Position = 0    rs.Open(stm)    Return rs End Function

By using the built-in ability to serialize an ADODB Recordset to an XML string, and by using the ADODB Stream object, you can freely pass Recordsets between tiers in your application. In addition you get the added benefits of using XML Web services, which is a versatile replacement for DCOM, especially when you need to communicate over the Internet and build loosely coupled distributed applications. Again, if you are interested in this topic, turn to Chapter 21 for details on how to implement the XML Web service itself.