The COM Interop Services

Adapting Recordset Objects to DataSet Objects

Just as any other data adapter class, OleDbDataAdapter provides a Fill method that callers can use to add tables to a DataSet object. The OleDbDataAdapter class is defined in the System.Data.OleDb namespace, and you typically use it as follows:

OleDbDataAdapter da = new OleDbDataAdapter();  DataSet ds = new DataSet(); da.Fill(ds, "MyTable");

The Fill method of the OleDbDataAdapter class has an interesting peculiarity. It can load into the target DataSet object not only the result sets created by the given command but also the contents of an existing ADO Recordset object. One of the overloads of the Fill method has the structure shown here:

int Fill(DataSet ds, Object adodb, String srcTable);

The data adapter copies each result set found in the ADO Recordset object into a table in the DataSet. As usual, the first table is named after the specified srcTable argument, and all the other result sets add a progressive index to the table name: MyTable1, MyTable2, and so on. The link between ADO and ADO.NET is a one-way binding that you can use only to copy data from ADO to the DataSet object. Any updates are performed by ADO.NET.

	The OleDbDataAdapter class s Fill method features several overloads that work with ADO data objects. For example, you can import a single Record object into a DataSet object s DataTable object as well as into a stand-alone DataTable object.

Loading a Recordset Object into a DataSet Object

The Fill method loads the Recordset object into a given table in the specified DataSet object, regardless of how you actually obtained the Recordset object. The following code shows how to retrieve the Recordset object by using plain ADO code, load it into a DataSet object, and then bind it to a CheckBoxList control. The output is shown in Figure 8-3.

Recordset adoRS = new Recordset(); adoRS.Open(strCommand, strConn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, 1); // Transforms a Recordset into a DataSet using the OleDbDataAdapter OleDbDataAdapter oda = new OleDbDataAdapter(); DataSet ds = new DataSet(); oda.Fill(ds, adoRS, "MyTable"); // Bind to the CheckBoxList control chkOutput.DataSource = ds.Tables["MyTable"]; chkOutput.DataTextField = "lastname"; chkOutput.DataValueField = "employeeid"; chkOutput.DataBind();

Figure 8-3

The contents of an ADO Recordset object bound to a ASP.NET CheckBoxList control.

The signature of the Fill method mandates that you specify the name of the table. When a table with the name you specified already exists in the target DataSet object, the contents of both tables are merged. If primary key information is present, duplicate rows are refreshed and appear only once. When creating a table, the Fill operation creates primary keys and constraints if the MissingSchemaAction property for the data adapter is set to AddWithKey. Filling a DataTable object with the contents of a Recordset object is just a special case of a more general filling mechanism.

Status of the Recordset Object

Typically, you call Fill on an open Recordset object. If the Recordset object is closed prior to the beginning of the operation, no exception is thrown. The data adapter assumes that the current result set in the Recordset object is closed and attempts to move to the next one. If another result set is found, the execution continues; otherwise the method terminates.

When the Fill method terminates, all the result sets have been successfully loaded in the target DataSet object, and the original Recordset object is closed. The Fill method returns the number of rows successfully added to or refreshed in the DataSet object.

Loading a Recordset Object into a DataTable Object

The Fill method can load the contents of the Recordset object directly into a DataTable object. The DataTable object might or might not be a member of a DataSet object. The signature of this overload is slightly different.

int Fill(DataTable dt, Object adodb);

When this implementation of the Fill method terminates, the input Recordset object is left open. In addition, when handling batch SQL statements that return multiple results, this implementation of the Fill method retrieves schema information only for the first result.

Loading the contents of a Recordset object into a DataTable object provides you with a bit more flexibility because you can handle the various result sets separately. You can decide whether to load them all or discard a few. You are responsible for closing the Recordset object when finished.

A Common Migration Scenario

Being able to pump data out of an ADO Recordset object into an ADO.NET object is a big step forward on the road to integration between ADO and ADO.NET systems. Let s review a common migration scenario and see how you can elegantly connect new ASP.NET pages to the existing middle tier and data back-end. You begin the renovation of the Web application with the presentation layer, then handle the more problematic changes to the middle tier. Let s assume that, as shown in Figure 8-4, you have a typical Windows DNA 2000 system made of ASP pages populated by a middle tier that uses ADO for all data access needs.

Figure 8-4

Only two moves are required to port a Windows DNA system to .NET.

Well written ASP pages do not include plain ADO code but rather call into COM objects, usually Visual Basic COM objects. Such middle tier and data tier components can have methods that return only ADO Recordset objects, which the page uses to fill out the HTML user interface.

Linking to a Visual Basic COM Object

From the .NET perspective, calling into the ADO library is not really different from calling into any other COM object. CIS is involved and you jump out of the CLR. You can make a Visual Basic COM object callable from within an ASP.NET page by following the instructions discussed earlier in this chapter.

Let s suppose that you have a COM component whose progID is MyComp.NWData, which you use for data access. This object has one method with the following signature and code:

Public Function GetEmployees() As ADODB.Recordset Dim strCnn As String Dim strCmd As String strCnn = "PROVIDER=sqloledb;UID=sa; DATABASE=northwind;" strCmd = "SELECT firstname, lastname, title FROM Employees" Dim rs As New ADODB.Recordset rs.Open strCmd, strCnn, adOpenForwardOnly, adLockReadOnly, 1 Set GetEmployees = rs End Function

How do you use this code to retrieve a Recordset object from within an ASP.NET page? You get a .NET wrapper for the library and link the resulting assembly to the page. Visual Studio .NET can do this for you; otherwise, you use the tlbimp.exe utility.

	Unless you adopted a special naming convention, chances are good that the Visual Basic DLL filename conflicts with the default name chosen by the tlbimp.exe utility. Visual Studio employs a naming schema that prevents this risk, but if you run the utility, you are responsible for renaming the assembly file.

Let s assume that you successfully created a .NET assembly called MyComp.dll from the original Visual Basic COM DLL. You link this assembly to the ASP.NET page by using the following directives.

<%@ Assembly Name="MyComp" %> <%@ Assembly Name="ADODB" %>

Both the assemblies must be visible to the ASP.NET run time and placed, for example, in the bin directory of the application. Since the method of the MyComp component returns an ADO Recordset object, you must also link with ADODB if you want your code to remain strongly typed. Figure 8-5 shows how the Intermediate Language Disassembler (ILDASM) reads the .NET version of the MyComp component.

Figure 8-5

The MyComp component analyzed by the ILDASM utility.

Invoking COM Methods

What happens from now on in the migration scenario is not really different from what we discussed earlier for plain ADO code. You create a new instance of the .NET class that represents the VB COM object. Next you invoke the needed method and get an ADO Recordset object.

MyComp.NWData mc = new MyComp.NWData(); ADODB._Recordset rs = mc.GetEmployees();

The OleDbDataAdapter class lets you convert this recordset into a Data Table object, making it bindable to any data bound ASP.NET control for example, the DataGrid control.

OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); da.Fill(ds, rs, "MyTable"); grid.DataSource = ds.Tables["MyTable"]; grid.DataBind();

Figure 8-6 shows this technique in action in the AdoAdapterFromCOM.aspx sample application.

Figure 8-6

The contents of the DataGrid controls come through the .NET wrapper for a VB COM component.

Caution with Visual Basic 6 Components

Most COM components work if invoked from within ASP.NET pages, even when you call them by using the late-bound Server.CreateObject method the typical way of creating object instances in ASP. By creating a .NET wrapper, you also improve performance because the component is early bound to the managed code of the page.

Most of the COM components developed using Visual Basic 6 work in the single-threaded apartment (STA). If you plan to use these components from within an ASP.NET page, you should mark the ASP.NET page with the aspcompat attribute:

<%@ Page aspcompat="true" %>

The aspcompat attribute forces the page to be executed on an STA thread. In this way, the page can call STA components, such as those developed with Visual Basic 6. Calling into STA components is not as fast as calling into native .NET components or imported free-threaded components.

If you omit setting the aspcompat attribute and call STA objects, the run time is expected to detect the situation and throw an exception. However, an exception will not be thrown when you use the STA component through the services of a .NET assembly. The ASP.NET run time has no way to detect that it is going to make STA calls because the calls are not direct and are processed by an intermediate proxy. As a result, your application is walking a tightrope suspended between poor average performance and lethal deadlocks.

COM+ Components

The range of the COM components you can call from within ASP.NET pages includes Visual Basic COM components registered with COM+ applications. Setting the aspcompat attribute to true also enables you to access the ObjectContext object so that you can interact with the COM+ run time.