Interoperable Web Applications

Using ADO in .NET Applications

Setting up a .NET application to use ADO objects is not really different from setting up the same application to take advantage of any other COM object. You can use COM objects from within any type of .NET application as long as you obtain an assembly that exposes the COM type library information as .NET metadata.

The .NET Framework offers a tool named tlbimp.exe that reads in a COM type library and generates an assembly that exposes to all .NET clients the functionality of the type library as a .NET class. Behind the scenes, the tool creates and compiles a class that exposes the same methods and properties as the original COM object. During this process, the names are preserved and the types involved are adapted to make them compatible with the .NET type system. The new class is also given some basic methods, such as ToString and GetType, that are not in the original definition but are standard parts of the arsenal of any .NET class.

The tlbimp.exe utility is located in the bin folder in the .NET Framework installation path. By default this folder is as follows:

C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin

The tool has the following command line. It takes the name of the file containing the type library information plus some options that rule its overall behavior.

tlbimp.exe typelibname [options]

The tlbimp.exe tool works fine either when the type library is embedded as a resource in the component code or when the type library is stored in a separate file. What matters is that the file name you provide contains the information to process. By default, tlbimp.exe generates an assembly with the same name as the type library. You can configure the assembly s file name by using the /out command line switch. The command line needed to create an assembly for the ADO library is as follows:

tlbimp msado15.dll 

You specify the name of the DLL that contains the ADO code and type library, and the tool generates an assembly named adodb.dll.

	Microsoft Visual Studio .NET automatically creates an assembly when you right-click on the Add References node in the Solution box of the project and then choose one or more COM libraries to import.

	Assemblies generated from COM libraries might not survive across different builds of the .NET Framework. Make sure you re-create them each time you install a different version of the system.

The ADODB Assembly

To use ADO objects in .NET applications, you don t have to create the assembly because it is already part of the framework. It is located in the following folder along with a few other assemblies. All assemblies represent typical COM libraries you also might want to use in .NET applications.

C:\Program Files\Microsoft.NET\Primary Interop Assemblies

Let s see how to configure an ASP.NET page so that it can use an ADO object such as Recordset. A Web page that uses ADO always ends up having a heading similar to this:

<%@ Page Language="C#" %> <%@ Assembly Name="ADODB" %> <%@ Import Namespace="ADODB" %> <%@ Import Namespace="System.Data" %>

The @ Assembly directive links an assembly to the current page. The association takes place at compile time. A page that declares an assembly imports all its classes and interfaces for use within the page. You indicate a compiled assembly by name and do not use an extension. To use the classes in the assembly, you don t need the @ Import directive. However, the following directive lets you call any class in the ADODB assembly (the Recordset class) without prefixing it with namespace information:

<%@ Import Namespace="ADODB" %>

You can create a new recordset with this code:

Recordset rs = new Recordset();

And you don t have to use this code:

ADODB.Recordset rs = new ADODB.Recordset();

Using only the @ Import directive is never sufficient to successfully access ADODB or any other assembly.

Getting a Recordset

After the original COM type library has been turned into a .NET assembly and properly linked to a page, you are ready to use the original COM objects as regular .NET classes. The following code shows how to access the Northwind database to fill a Recordset object with some rows:

Recordset adoRS = new Recordset(); adoRS.Open(strCommandText, strConn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, 1);

The original signature of the ADO Recordset object s Open method is modified as follows:

void Open(object Source, object ActiveConnection, ADODB.CursorTypeEnum, ADODB.LockTypeEnum, int)

If you work with ADO in Visual Studio .NET, you ll also have full Microsoft IntelliSense support. (See Figure 8-1.)

Figure 8-1

The IntelliSense feature of Visual Studio .NET at work with imported ADO Recordset objects.

When you hold a Recordset object, you can extract the information it contains and populate the page. Look at this example:

StringBuilder sb = new StringBuilder(""); while (!adoRS.EOF) { sb.Append("<span>"); sb.Append("<input type=checkbox name=theEmployee value="); sb.Append(adoRS.Fields["employeeid"].Value.ToString()); sb.Append(">&nbsp;&nbsp;"); sb.Append(adoRS.Fields["lastname"].Value.ToString()); sb.Append(", "); sb.Append(adoRS.Fields["firstname"].Value.ToString()); sb.Append("</span><br>"); adoRS.MoveNext(); } adoRS.Close(); Response.Write(sb.ToString());

The preceding code creates a list of check boxes based on the fields in the Recordset object. The output is shown in Figure 8-2. The full source code for the ImportAdo.aspx application is on the companion CD.

	When you use ADO to access data, you must access the database by using the data source s OLE DB provider.

Figure 8-2

A list of check boxes created from rows in the Recordset object.

ADO Server Cursors

What are the reasons you might use ADO to perform data access tasks from within .NET applications? I can list a few, some of them more compelling than others. One reason is that you can attempt to save at least a portion of your investments in your ADO code. Another is that using ADO instead of ADO.NET appears to minimize the learning curve for .NET. But really, the best reason to use ADO is that ADO.NET does not cover 100 percent of ADO functionality.

ADO.NET does not support server cursor types such as adOpenKeyset and adOpenDynamic, which are available in ADO. In the ADO.NET object model, no explicit notion of a cursor even exists, but you can see the cursor-like functionality in the data reader and the DataSet objects. The data reader object maps to an ADO Recordset object that uses the read-only, forward-only cursor. The DataSet object maps to an ADO Recordset object that uses the static disconnected cursor. When your application uses server cursors and you plan to move them to .NET, either you change the application s architecture to avoid server cursors or you stick to ADO and forget about ADO.NET classes. Avoiding a change in the architecture would definitely be a good reason to import and use ADO classes from within a .NET application.

	In a future release of the .NET Framework, the architecture of the .NET data provider will be enhanced to allow for server cursors. You should not expect it to be a general implementation of server cursors, but rather a database-specific implementation. Server cursors will be exposed to applications through a new method similar to Execute in the command class (ExecuteResultSet), and return an instance of a new data-oriented class (SqlResultSet). The following pseudo-code just gives you an idea of the future implementation: SqlCommand cmd = new SqlCommand(...); SqlResultSet rs; rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable ResultSetOptions.Updatable); while(rs.Read()) { if (rs["Column"] == valueToMatch) { rs["Column"] = newValue; rs.Update(); // persist changes back to the database } } rs.Close();

Migration Issues

If you think that importing ADO code in .NET applications will save you from rewriting large blocks of code, I m afraid you need to think again. In most cases, your ADO code is written in Visual Basic, but Visual Basic .NET isn t compatible with Visual Basic 6 code. You probably won t have to edit every line of your code, but you will need to carefully review every character. Even if your ADO code is written in C++, your situation is not significantly improved. One of the issues you have to face, at least in the most common migration scenario, is the unavailability in .NET languages of optional parameters a frequent shortcut for Visual Basic 6 programmers. The changes required to make your ADO code work in the .NET Framework are significant enough to make porting to ADO.NET worthwhile.

Another issue to consider is the lack of integration between ADO Recordset objects and .NET data bound controls. ADO Recordset objects are not native .NET objects, and this significantly affects (and sometimes prevents) the effective use of list bound controls such as the DataGrid and CheckBoxList controls. In ADO.NET, you will be able to reuse your ADO and OLE DB skills more easily than your existing ADO and OLE DB code. Importing ADO code in ADO.NET applications can save you valuable coding investments, but it does not use ADO.NET features optimally.

You can reach a good compromise between the reuse of existing code and the overall functionality of the resulting .NET applications when you obtain the ADO Recordset object directly from a COM object and import it in a DataSet object.