Linking to the ADO Library

When we want to access external components in Visual Basic, it's good practice to include a reference to those components. Here we'll be linking to the ADO library (as we see shortly). By including references we experience a huge performance increase versus not including references.

As an example of low performance code, consider the following, where an ADO Connection object is created in ASP:

<%
Dim Connection
Set Connection = CreateObject("ADODB.Connection")
%>

Once we have a Connection object around we can run queries on the database using the Execute method, like this:

<%
Dim Query
Set Query = Connection.Execute("select * from customers")
%>

In this case Active Scripting (the Microsoft technology that interprets and runs the VBScript code embedded in your ASP scripts) goes away and asks the Connection object if it supports the Execute method. After it's determined it does, it then needs to ask the Connection object what parameters the Execute method supports.

That operation is very expensive (since it requires a lot of processor cycles) and happens whenever you call a method or property on an ActiveX object created inside ASP, irrespective of the language you're using. Similarly it happens in Visual Basic whenever you call methods on objects that Visual Basic doesn't understand. Paradoxically, although this technology costs a lot of processor cycles (the unit of work that the processor is capable of doing) when it runs, it's the very essence of ActiveX.

Reference Benefits

Including references in our Visual Basic project, tells VB where to go to find the methods and properties the object supports, ahead of time. As we mentioned before, when an ActiveX DLL is compiled a type library is added to the file. When requested, the DLL can give that type library to whatever wants to contain that ActiveX control - and in this case Visual Basic is the thing that wants to contain your control.

Visual Basic is much smarter at executing code than the VBScript/Active Scripting combination. Once VB has loaded and understood the type information in the library it can make a much faster, lower-level, call into the ActiveX object than the route VBScript/Active Scripting takes. Without getting into too much detail, what VB is able to do is to make the call into the ActiveX control in one hop, as it is a compiled language. Essentially, this takes a few processor cycles. VBScript/Active Scripting cripples itself because it cannot make calls in this fashion, as it is an interpreted language. Instead, it has to move through a higher-level architecture that takes between 10 and 20 times as many cycles.

We'll be learning a little more about this later, but as we know we are using ADO in our project we'll be including a reference to the ADO DLL in our project. This will mean that whenever we call Connection.Execute from our objects we'll be calling it between 10 and 20 times faster than if we made the same call from ASP without the library reference.

Providing Data Services

Our next task in the construction of our object model is to create another class module (Database) that will provide data services to the remainder of the objects in the model (thus Database is an infrastructure object). The term data services encompasses any database activity, such as selecting rows, inserts or deletes, schema manipulation, etc. Although ADO provides a rich set of database functionality, we are going to build our own object, so that we can build a smaller set of very useful functions that leverage the ADO functionality. This makes our writing objects in our model a little easier (the motives for doing this will become clear in later chapters).

We'll use the Database object to connect to the database and, after we have created it, the other objects in our model can then use this object to get a connection to the database without having to worry about the details of creating the connection.

Try It Out - Creating the Database Class Module

1. Our first task is to include a reference to the ADO DLL. Select Project | References from the menu and when the list of objects appears, locate and check on the Microsoft ActiveX Data Objects 2.1 Library as shown here:

If you do not have ActiveX Data Objects 2.1 installed on your computer, it can be downloaded from http://www.microsoft.com/data/.

2. Using the Project Explorer window, right-click Class Modules and select Add | Class Module. Select Class Module from the New tab and click Open:

3. After the class has been created, use the Properties window to name the class Database:

Continued...