Interoperating with ADO

for RuBoard

Interoperation with existing code is always important when you move to a new development platform or language, and moving to ADO.NET is no exception. In fact, many organizations will have an extensive set of COM components already developed and that may be deployed using Microsoft Transaction Server (MTS) or Component Services (COM+). In these cases, it certainly makes sense to reuse that existing code base, at least initially, rather than rewriting it all in ADO.NET. In that way, you can concentrate on implementing only the most cost-effective parts of your managed code.

To that end, ADO.NET supports integration with ADO Recordset objects through the OleDbDataAdapter object.

Reading ADO Recordset Data

As we discussed on Day 1, the DataSet object can in many respects be thought of as analogous to the disconnected Recordset object of ADO 2.x. As a result, it makes sense that you should be able to read an ADO Recordset into a DataSet object using a data adapter.

Although the SqlDataAdapter doesn't support it, the OleDbDataAdapter includes two overloaded signatures in its Fill method that accept ADO Recordset objects as the second parameter. Using this method, you can pass a DataSet to be populated into the Fill method along with a Recordset . The rules for filling the DataSet are the same as those discussed on Day 12, "Using Data Adapters."

However, the mechanism that makes accessing Recordset objects almost trivial is the COM Interop functionality of the .NET Framework. Simply put, COM Interop allows COM components to be called from managed code, and vice versa, by creating wrappers through which the calls pass. These wrappers, referred to as the runtime callable wrapper ( RCW ) for .NET to COM calls and the COM callable wrapper ( CCW ) for COM to .NET calls, abstract the differences between COM and .NET including reference counting versus garbage collection, COM types versus the Common Type System, type libraries versus metadata, and so forth.

Note

For a list of how the ADO.NET types map to the ADO types, see the "ADO Type Mapping to a .NET Framework Type" topic in the online documentation.

RCW Details

At runtime, the common language runtime creates one RCW for each COM object that caches all references to the object from managed code. In this way, the RCW can manage the lifetime of the COM object by dereferencing it at the appropriate time. As a result, all calls to the COM object pass through the RCW, which is responsible for marshalling data between the two and making sure that the appropriate interfaces are called on the COM object. The RCW consumes the COM interfaces and therefore hides them from a managed client. However, it retains all other custom interfaces implemented by the component and adds them to the metadata. When it does so, it exposes all members of all implemented interfaces as a part of the managed class. In this way, a client doesn't have to, but certainly may, cast to the appropriate interfaces before making a call to one of its methods .

The RCW also is responsible for managing when the COM object's reference count is decremented. The default behavior is to simply wait until the RCW is garbage collected and to call the COM object's Release method at that time. Of course, when the reference count reaches 0, the COM object deallocates itself.

For more information, see Chapter 9 of my book Building Distributed Applications with Visual Basic .NET , published by Sams.

Before the component can be called by the runtime using the RCW, you must first import it as a managed type using a metadata assembly. The easiest way to do this is to use the Add References dialog in VS .NET. For example, assume that ComputeBooks has an existing COM-based DLL written in VB 6.0 that exposes a GetTitles method that returns a disconnected Recordset object. From inside a VS .NET project, you can right-click on Add References and invoke the Add Reference dialog shown in Figure 20.1.

Figure 20.1. Adding a reference. This dialog enables you to add a reference to an existing COM component.

Notice that the COM tab is activated and shows the COM components registered on the local machine. When the ComputeBooksData component is selected, a managed type is created in an interop assembly (or metadata assembly ) generated in the obj directory of the project called Interop. component, where component in this case is ComputeBooksData.dll.

Note

You can alternatively import the COM component as a managed type using the Type Library Importer (tlbimp.exe) command-line utility or programmatically through the System.Runtime.InteropServices.TypeLibConverter class.

In addition to adding a reference to the ComputeBooksData component, the Add References dialog also automatically adds a reference to the primary interop assembly (PIA) for ADO 2.7 called ADODB. The PIA is a strongly named metadata assembly that is placed in the GAC and through which all clients will gain access to the COM component. You can think of PIAs as the "authorized" way to gain access to a COM component. Microsoft ships several PIAs (including the one for ADO) that are installed with the .NET Framework.

Tip

Behind the scenes, the CLSID key in the registry for the COM component can be updated with an assembly value that points to the PIA. In this way, when you select a registered COM component from the Add References dialog, VS .NET will attempt to load the PIA if it exists and, if not, prompt you to create the metadata assembly. You can create your own PIAs for COM components in your organizations using this technique as well.

At this point, the COM component can be called as if it were implemented in managed code. For example, the method in Listing 20.2 could be used to instantiate the COM component. It calls its GetTitles method to bind the resulting data to a grid control.

Listing 20.2 Reading Recordset data. This method calls a COM component to retrieve an ADO Recordset and bind its results to a grid control using a DataSet object.

 private void BindTitles(string publisher) {      DataSet ds = new DataSet("Titles");      ComputeBooksData.QueryClass o = new ComputeBooksData.QueryClass();      OleDbDataAdapter da = new OleDbDataAdapter();      ADODB.Recordset rs = o.GetTitles(publisher);      da.Fill(ds,rs,"Table1");      dgTitles.DataSource = ds;      rs.Close(); } 

In Listing 20.2, you'll notice that the interop assembly is referenced as ComputeBooksData.QueryClass , where ComputeBooks is the name of the namespace (translated from the name of the DLL) and QueryClass is the name of the class. After instantiating the OleDbDataAdapter , the Recordset is retrieved into rs by calling the GetTitles method and passing it the publisher name passed into the method.

After the Recordset has been retrieved, it's copied into the DataSet using the overloaded Fill method, with the third argument specifying the name of the DataTable to populate. The DataSet is then bound to the DataGrid using the DataSource property.

Note

This operation is a one-way operation. In other words, when the Recordset is retrieved, it is read-only, so you would then need to use the OleDbDataAdapter or explicit commands to update the data that was retrieved.

for RuBoard