It is important to be able to create custom pieces of code to perform tasks, such as database access, from your Active Server Page (ASP) applications. The obvious problem with a scripted solution, however, is performance. In this article, I’ll show you how to convert the script version of a database object into a dynamic link library (dll), or as it’s commonly called, a COM component. To create the compiled version, I’ll use Visual Basic 6 service pack 4. In “Creating objects for your Active Server Pages,” I showed you how to create a VBScript object to simplify access to a data source. Because this object has the potential to be heavily used, it is an ideal piece of code to compile.

Visual Basic is a great environment for creating COM components, and with version 6, the compiler is very efficient. In most cases, it can create code that is as fast as C++. This is not to say you shouldn’t use C++. Often, project requirements will dictate an efficient component that only C++ can create. But the majority of development I have been involved with favors Visual Basic for the speed of creation and ease of use. Of course, much of your language choice is simply personal preference, so I will leave the language selection in your capable hands.

The starting script
Just to refresh your memory, Listing A shows the VBScript version of the database object from the previous article.

Supporting files for this technique

This article uses an example database that we discussed in “Add power by using objects within ASP pages.”

Getting to Visual Basic
Converting this script into a Visual Basic component isn’t hard. The first step, of course, is to start Visual Basic. The starting screen (unless you have asked Visual Basic not to show it) is the new project dialog box shown in Figure A. Notice the ActiveX dll option. Simply select that option and click Open to generate a new Visual Basic project with all the proper settings for creating a COM dll. It should start a new class named Class1.

Figure A
Project dialog

Click on the Project menu and select Project1 Properties to open the dialog box shown in Figure B. Here, you can change the name of the project and set the properties that affect how the component is built. Name the project DBUtil and select the Unattended Execution check box. This option tells Visual Basic that the components don’t have any user interaction. Basically, this means that any error information or output will go to the event log. Leave all the remaining settings to their defaults and click OK.

Figure B
You can name the project and set properties here.

The next step is to copy all the code from the VBScript object and paste it into the class window that the new project wizard displays. Copy only the code between class DBConnection and end class. After pasting the code, change the name of the class to DBConnection in the Properties window, as we’ve done in Figure C. Also change MTSTransactionMode to 3 (uses transactions). We won’t be adding any transaction-specific code to this object, but it’s usually a good idea to specify that your object will support transactions. Now, pick a place on your hard drive and save the project and class. The default filenames will be the same as the names of the project and class. I find that there is less confusion by using these names, but if you don’t share my preference, you can rename the files.

Figure C
We’ve renamed the class DBConnection.

Since this object uses ADODB, the next step is to add a reference to that library in the project. Select the Project menu and choose References. The dialog box that appears will have a listing of all the available libraries on your system, as shown in Figure D. Scroll through the list and select the Microsoft ActiveX Data Objects 2.5 Library check box. My system has SQL 2000, but if you don’t have version 2.5, choose the closest version you have on your system. This component doesn’t use any just-released features, so most any version of Active Data Objects should function properly.

Figure D
Select Microsoft ActiveX Data Objects 2.5 Library.

Code modifications
So far, we have successfully moved the DBConnection class code into Visual Basic. For the most part, this code is complete. Except for the reference to the ASP Application object in the class constructor (Class_Initialize()) and the Server.CreateObject lines, this code would compile. In order to ensure a well-compiled object, a few code modifications are necessary.

First, add Option Explicit to the top of the Code window so that the system generates an error if any variables are used before they are declared. Also, move the dimension statement for mConnectionString just below the Option Explicit statement. Next, change the Class_Initialize() routine to match Listing B. This code simply moves the connection string code directly into the object. This adds a minor level of security, since a compiled production object would be able to connect to the database without exposing the actual connection string to developers or unauthorized users.

Listing B
Private Sub Class_Initialize()
   mConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” _
      & “Data Source=c:\temp\Music Collection.mdb;”
End Sub

To ensure that the component does not expose the connection string, remove the Public Property Get ConnectionString() code from the class. This makes the ConnectionString property write-only so that the user can utilize this class to access other data sources, but it prevents the property from being read, so the user can’t see the connection string.

You can also remove the Class_Terminate() procedure, since it doesn’t contain any code. Now, perform a search-and-replace using Server.CreateObject as the search text and CreateObject as the replacement. This makes all the create object references Visual Basic-compatible. At this point, you should save the project and, just for testing purposes, compile the dll by choosing Make DBUtil.dll from the File menu. The object should compile properly, although it won’t run properly without a few more changes to the code.

The remaining code changes are numerous but simple. Since Visual Basic is more strongly typed than VBScript, you should add type references to all the functions and variables in the class. It doesn’t take long and helps create more efficient code. Listing C shows the completed class.

Putting your class to work
Now that you have a working COM component, it is time to put it to work. There a few steps necessary to make this object available for use in ASP. First, modify the pageDeclare.asp file of the framework (for information on the framework see “A framework for your ASP application”) to reference the newly compiled object using an object tag (Listing D).

Listing D
<%@ LANGUAGE=”VBScript” %>
<object runat=”server” progid=”DBUtil.DBConnection” id=”dbConnect”></object>
   option explicit
   ‘<!–#include virtual=”/includes/classes/dbAccess.asp”–>
   dim gExpirePage
   dim gPageTitle
   dim gHeaderInclude
   dim gBodyTagInclude
   ‘dim dbConnect
   ‘set dbConnect = new DBConnection
   ‘Set defaults
   gExpirePage = true
   gPageTitle = “”
   gHeaderInclude = “”
   gBodyTagInclude = “”

The object tag is great because, unlike the VBScript object, the dll will not be instantiated until it is used. That way, if your page uses the framework but doesn’t need the dbConnect object, it won’t incur any extra overhead. Notice that the object tag specifies that the object should run on the server. This is necessary so that the tag doesn’t go to the browser and attempt to create the object on the client.

The final thing needed to make this object work is to register it in your computer’s registry. The best way to do this is with Microsoft Transaction Server (MTS), which is just COM+ on a Windows 2000 machine. Go to the Control Panel and select Administrative Tools. In Administrative Tools, double-click on Component Services. Use the left pane to navigate into Component Services | Computers | My Computer | COM+ Applications. Click on COM+ Applications and then right-click on COM+ Applications. Select New | Application from the popup window. Follow the prompts and create an empty application. Then, name the application whatever you like and make sure to choose Server Application. If our component needed to access the ASP objects, like Application or Session, it would need to be a Library Application. But for database access, Server Application is a better choice.

For the Application Identity, you can choose Interactive User, as long as you are on a development machine where you are logged in while using Internet Information Services. If this is a server machine where no one is logged in, you need to specify a user account. Create an account that the application and objects will use for security authentication. This can be a complicated setup with a server farm and one or more domains, so it is best if you can use the Interactive User for this test.

Now, you should be able to navigate to your new application in the left pane and click on the Components folder of your application. To register your new dll, simply pull up an Explorer window and drag the dll into the right pane of the Component Services window. An icon will appear named DBUtil.DBConnection, as shown in Figure E.

Figure E
Register the new dll.

The musiclist.asp file is ready to go if you just remove the line set dbConnect = nothing. This call will no longer work, since the object is now managed by MTS. Once you have deleted that line, the musiclist.asp file should appear just like before.

I hope this has helped to demonstrate how easy it can be to convert a VBScript object into a Visual Basic compiled dll and use it in your ASP pages. If we left the set dbConnect = nothing statement out of our pages, the transfer from VBScript object to dll would have required only changing the pageDeclare.asp file.

The compiled object is still missing proper error handling. Since it is now a Visual Basic object, you can use the on error goto style to capture any errors that occur in the functions. Once an error is caught, you can either raise the error or log it to the event log. In future columns, we’ll explore how to write a session object and using regular expressions to validate form data.