Accessing an ADO Recordset from ADO.NET
| for RuBoard |
Even if you aren't ready to convert your existing codebase to work within the managed Microsoft .NET Framework, you still have a few options for reusing your old components . It's possible to use COM interoperability to work directly with your old methods and objects. A complete discussion of COM interoperability is out of the scope of this book. However, in this section and the next few sections following, you'll see how to access a Visual Basic 6.0 COM object method that returns a recordset object.
The function in Listing 16.1 is written in Visual Basic 6.0. It's fairly simple; it connects to a data source and retrieves some employee information from the Northwind database, places the results in a recordset object, and then returns that object. If you have Visual Basic 6.0, and would like to follow along, create a new ActiveX DLL project in Visual Basic and place the code in Listing 16.1 into a class called DataAccess . Name your project "Northwind." You'll also need to add a reference to the ADO 2.6 type library (by clicking on the Project menu and selecting References). After you have performed these steps, click on the File menu and select Make Northwind.dll. Your screen should look like Figure 16.1. Click OK to compile the project. After you compile the project, a new class named Northwind.DataAccess will be registered on your system.
Figure 16.1. Compiling a Visual Basic 6.0 component.
Listing 16.1 A Legacy Visual Basic 6.0 Northwind Data Access Component
Public Function GetAllEmployees() As ADODB.Recordset Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Set conn = New ADODB.Connection Set cmd = New ADODB.Command Set rs = New ADODB.Recordset sSQL = "SELECT FirstName, " + _ "LastName, " + _ "Address, " + _ "City, " + _ "PostalCode, " + _ "HomePhone " + _ "FROM Employees" conn.Open "DSN=NorthwindSQL;Initial Catalog=Northwind;" & _ "Server=(local);UID=sa;PWD=;" rs.ActiveConnection = conn rs.Open sSQL Set GetAllEmployees = rs End Function
 | The examples in this chapter require the use of a DSN. Make sure you have a system DSN named Northwind that points to your Northwind database. |
In ASP 3.0, this newly created ActiveX object would typically be used to pass some employee information to another Visual Basic application or to an ASP page, as is the case in Listing 16.2. As you can see from the code at the top of the page, the Northwind.DataAccess object is created, and then the GetAllEmployees() method is called to fill a recordset. The recordset object is then looped through on the page to display information about each employee in the database. Figure 16.2 shows how the code in Listing 16.2 appears when run in a browser.
Figure 16.2. The output of Listing 16.2.
Listing 16.2 Legacy Usage of the Northwind.DataAccess ActiveX DLL
<%@ Language=VBScript %> <% dim rsEmployees dim oNorthwind set oNorthwind = Server.CreateObject("Northwind.DataAccess") set rsEmployees = oNorthwind.GetAllEmployees set oNorthwind = nothing %> <HTML> <HEAD> </HEAD> <BODY> <h1>Northwind Employees</h1> <table> <tr> <th> First Name </th> <th> Last Name </th> <th> Address </th> <th> City </th> <th> Postal Code </th> <th> Home Phone </th> </tr> <% do while not rsEmployees.EOF 'Add a single table row for each record %> <tr> <td> <%=rsEmployees("FirstName")%> </td> <td> <%=rsEmployees("LastName")%> </td> <td> <%=rsEmployees("Address")%> </td> <td> <%=rsEmployees("City")%> </td> <td> <%=rsEmployees("PostalCode")%> </td> <td> <%=rsEmployees("HomePhone")%> </td> </tr> <% 'Advance to the next record rsEmployees.MoveNext loop %> </BODY> </HTML> In ASP 3.0, the code in Listing 16.2 is one standard way of displaying database data. Lines 3 “13 create an instance of the COM object created from Listing 16.1. The GetAllEmployees() method is then invoked, returning an ADO recordset of data. The remainder of the listing is display logic; the inline ASP code iterates through the rows in the recordset, manually building a table. Note that in ADO.NET, this has been replaced by the DataGrid control.
The next few sections demonstrate how to use this legacy codebase to build a similar screen using ADO.NET and an ASP.NET Web form.
COM Interoperability
COM interoperability is the bit of magic that enables you to use legacy COM objects inside the Microsoft .NET Framework. There are two different ways to access legacy objects: by using early binding or late binding. Binding, in this instance, refers to when the object's type information is available.
When you use early binding, the correct type is compiled into your application, improving performance and readability. You need not worry about the correct type not being present at runtime.
However, the Server.CreateObject() method is still available for use. If you pass it a valid Programmatic Identifier (ProgID), it will attempt to create the object and return it as type Object . You will get a runtime error in your application if the ProgID cannot be located.
Both of these methods for COM interoperability are covered in detail in the next few sections.
Importing Type Libraries
As mentioned earlier, one method of accessing your legacy COM objects is to import their type libraries into your existing applications and then work with them as you would any other .NET object. The application used to import the type library is called tblimp.exe. For the current example, you're going to need to import two libraries. Because you will need to create an instance of the recordset object, you must import its type library. If ADO is installed in the default path , you can run the following line of code from a command prompt:
tlbimp "c:\program files\common files\system\ado\msado15.dll" /out:adodb.dll
This generates a new DLL containing the type information for the recordset object. Place this DLL in the /bin directory of your ASP.NET application. Likewise, you'll need to compile the type library for the Northwind.DataAccess object you created at the beginning of this hour :
tlbimp Northwind.dll /out:NorthwindDOTNET.dll
Place this DLL in your /bin directory as well.
Server.CreateObject
The other method you can use to access legacy ActiveX objects is to create them at runtime using the following line of code:
Server.CreateObject("Northwind.DataAccess") This returns Northwind.DataAccess as type Object . After the object is created, you can use its public methods and properties.
Filling a DataSet with ADO Recordset Data
To use early binding, as you saw in the section "Importing Type Libraries," you would need to import the two DLLs you created and placed into your bin directory using the following code:
<%@ Import Namespace="NorthwindDOTNET" %> <%@ Import Namespace="adodb" %>
However, this is not necessary for the following example, because it uses the second method, or late binding, in order to access the object.
The Fill() method of the OleDbAdapter object is overloaded to accept a recordset object directly. It converts the recordset object into a DataTable inside a DataSet . You can see this in the example in Listing 16.3. In the LoadDataGrid() method, an instance of the legacy Northwind.DataAccess COM object is created. Then a legacy recordset object is created. Then the GetAllEmployees() method from Listing 16.1 is called, which, as you'll recall, returns a recordset of employee information.
Then, the recordset object is passed to the Fill() method of the OleDbDataAdapter , along with a DataSet and a table name. The Fill() method imports the recordset into the DataSet . The DataSet can then be manipulated any way you like. In this instance, it was bound to a DataGrid object to display it on a Web form. You can see the results in Figure 16.3.
Figure 16.3. A recordset object fills a DataSet and displayed in a DataGrid .
Listing 16.3 A Legacy Visual Basic 6.0 Northwind Data Access Component
<% @Page Debug="true" Language="VB" aspcompat="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <HTML> <HEAD> <LINK rel="stylesheet" type="text/css" href="ADO24HRS.css"> <!-- End Style Sheet --> <script language="VB" runat="server" > Sub Page_Load(Source as Object, E as EventArgs) LoadDataGrid(orders) End Sub Private Sub LoadDataGrid( _ myDataGrid as System.Web.UI.WebControls.DataGrid) 'Create Instance of Legacy Object Dim adoComponent as Object = _ Server.CreateObject("Northwind.DataAccess") 'Create Instance of Legacy Recordset Dim adoRS As ADODB.Recordset 'Access Legacy Method that returns recordset adoRS = adoComponent.GetAllEmployees() 'Create ADO.NET objects Dim adapter As OleDbDataAdapter = New OleDbDataAdapter Dim dsEmployees As DataSet = New DataSet 'Use overloaded Fill() method to place recordset contents into 'a DataTable in the dsEmployees Dataset adapter.Fill(dsEmployees, adoRS, "Employees") 'Bind! orders.DataSource = dsEmployees orders.DataBind() End Sub </script> </HEAD> <BODY> <h1 class="MainHeader">Northwind Employees</h1> <hr> <form runat="server" id=form1 name=form1> <asp:DataGrid id="orders" runat="server"></asp:DataGrid> </form> <hr> </BODY> </HTML> The example in Listing 16.3 is like many that you have seen before, with a few differences. First, notice that the System.Data.OleDb namespace is imported instead of the System.Data.SqlClient namespace. OLE DB provides access to the legacy ADO objects. Also, the Page attribute aspcompat is set to true. This makes the Web form run in a mode compatible with legacy single-threaded COM objects.
The LoadDataGrid() method encapsulates the data access for the Web form. First, it creates an instance of the COM object compiled from Listing 16.1 using Server.CreateObject() in lines 21 “22. On line 28, the GetAllEmployees() method is called, just as in Listing 16.2, to retrieve an ADO recordset of data from the database. Then, on line 36, the recordset is passed as the second argument to the OleDbDataAdapter 's Fill() method. This imports the ADO recordset directly into a DataSet .
| for RuBoard |
EAN: 2147483647
Pages: 237