In the previous installment of The Active Server Page, I showed you a framework that makes it easier to assemble your ASP pages and centralize common code. That framework included a database object that was used to access the database and return a recordset. In this article, I’ll show how to create an object and explain how the database object was put together.
Use VBScript to prototype objects
The technique I’m going to use for developing objects might be new to you: I’m going to show you how to put together objects using VBScript. The main reason I use this approach is so that I can quickly prototype and develop my code without having to worry about compiling and registering the objects.
An additional benefit to using VBScript objects is that if you develop code that is hosted with a provider, you can use VBScript objects without additional hosting costs. Most providers will not even allow you to register your own COM objects on their systems, so this may be your only alternative. The good news is that a VBScript object can quickly be changed into a Visual Basic class and compiled. With minor modification to the code, the compiled object can then be used in place of the VBScript object. For most objects, this method is adequate, but not always. Sometimes, you will require the more advanced capabilities of Visual Basic or C++ for your COM objects, and prototyping them in VBScript will not be an option.
So why create a database object? The key reasons are simplicity, security (in the compiled version), and versatility. The database access simplifies the ASP pages by placing as much logic as possible outside of the requesting ASP page. It aids security since when converted to a compiled object, you can place the database information directly in the object where it is not easily read. And finally, the database object is versatile in that you can use the same object to connect to a variety of data sources by manipulating the connection string.
Database object requirements and limitations
Before we look at the code listing for the database object, let’s consider the requirements for this object. First, it should take care of all the code necessary to establish a connection to the database. Also, in order to be flexible, it should allow us to change which data source is used. In addition, it should allow the running of ad hoc SQL statements and stored procedures.
For efficiency, we will divide the SQL and stored procedure methods into those that return a recordset and those that only perform updates or inserts and therefore do not return a recordset. Often, it is handy to utilize a return value from a stored procedure, so we will need a separate method to handle this case if we need to take advantage of such a return value.
For the purpose of this article, we won’t meet all our criteria, but you will have enough code to easily complete the implementation and even add functionality if you want. Take a look at the code in Listing A for the database object that’s in the file called dbAccess.asp. I store this file in a directory called classes within the includes directory or just off the root of the Web in the classes directory.
Putting together the object
The entire file is encapsulated inside a <script> tag. You could use the ASP delimiters (<% %>) if you prefer. The next statement is the class statement that tells VBScript you are declaring an object. The class statement is followed by the name you choose for your class. In this case, the name of the class is DBConnection. The end class statement encloses all the code for the object and should appear at the bottom of the file just before the </script> tag.
Within the class statement, you can dimension variables for use within the class and declare the methods that make of the class. A class has two special methods that are run whenever the object is created and whenever it is destroyed. These methods are named Class_Initialize() and Class_Terminate() (see Listing B). They are often referred to as the class constructor and destructor. You can place code here that initializes the properties of the object and, if necessary, cleans up once the object is finished and the last reference is removed.
In this database object, the constructor checks the Application object for a value called connectionString. If this value exists, it sets the private variable mConnectionString to the value. You should modify your global.asa file (see Listing C) to place the connection string into an application variable named connectionString.
The difference in the code to dimension variables within a class is that you declare the variables as either private or public instead of using the dim statement. Declaring a variable public is not recommended, as this defeats the encapsulation of logic and data hiding, which are some of the benefits of using an object. A private variable can be accessed only within the class, but you can expose properties to the user that manipulate the value of a private variable. The great thing about properties is that you can determine whether the property is read only, write only, or both. Look at the code in Listing D for the ConnectionString property.
For each property you would like to expose, you define a property procedure. There are two types of property procedures: get and let. When you define a get procedure with the name of the property, that property is exposed and accessible as a readable property. Defining a let procedure defines that property as changeable. Notice that for the let procedure, you must define a parameter. This parameter will contain the value from the right side of the equal sign when the user performs an operation to set the value such as objectname.ConnectionString = value.
There is a third type of property procedure called set. The set property procedure is used when the property you are defining is actually an object. For instance, you could define a container object that holds references to another group of objects. An example would be an order object that accepts the assignment of multiple item objects forming a collection of those objects that make up the order. This is a more advanced use of objects, but as our examples progress, we will most likely see cases where the property of one object is itself an object that will require the set property procedure.
The rest of the database object is really just a set of specific procedures for various types of database operations. I won’t go through every method here, but I will describe the two basic types and the one special private function used to support parameters.
The rsFromSQL method is the one used for the musicList.asp file (a sample file I introduced in “Add power by using objects within ASP pages”). This method returns a disconnected recordset from the data source. It simply refers to the database object variable and the method, along with the SQL test as an argument to the call. The line of code below accomplishes the task of calling the method and storing the returned recordset in the local variable called dbRecords:
set dbRecords = dbConnect.rsFromSQL(sqlText)
The code within this method is identical to the code originally contained in the musicList.asp file. The only difference is that now the code is not explicitly creating a Connection object. Instead, the connection string is applied to the Command object that is used as the source for the Recordset object. The ActiveConnection property of the Recordset object is set to nothing before returning to the calling code to ensure that the recordset is no longer holding a reference to the connection. A disconnected recordset is most efficient since object references are freed as soon as they are no longer needed.
The runSQL procedure is a simplified version of the rsFromSQL procedure. Since this method does not need to return a recordset, the call can be optimized. In this method, a Recordset object is not created and only the Command object is used to fulfill the request.
Passing parameters to stored procedures
The methods for supporting stored procedures or parameter queries require a little more code. This additional code handles the parameters you usually pass to a stored procedure call. Each parameter in a stored procedure call requires a number of arguments. Since you can have multiple parameters for each call, you need a way to pass multiple parameters, each consisting of a number of arguments. To accomplish this, the second parameter in the call is an array of arrays. The two methods rsFromProcedure and runProcedure each accept the name of the procedure to run and the parameters. An example call is shown in Listing E.
In this example, the stored procedure named myProc is called with three parameters. The second argument in the call is an array with each element containing an array of four items. The first item in the parameter is the name of the parameter; the second is the data type expressed using ADO constants. The third item is the length, which varies depending on the data type. The final item for each parameter is the actual value, which is usually a string or numeric. If you look up the parameter code in the MSDN documentation, you will see that these arguments match the documentation for adding a parameter to a Command object.
The key to adding the parameters to the Command object is parsing the array argument into the individual arrays and applying those to a Parameter object. The Parameter object is then appended to the Command object’s collection of parameters. The private method parseParams in Listing F performs all of this functionality.
The parseParams function uses a single for loop to access each argument and create the Parameter object using the CreateParameter method of the Command object. Before attempting the loop, I use the isarray function to verify that the argument is, in fact, an array instead of something else. The one case where something besides an array is valid is when you don’t have any arguments. In this case, you can pass null for the second argument in your call and the routine simply will not add any parameters to the Command object.
What is left
This database object represents the basic, necessary functionality to allow your ASP code to access a data source. It is not complete. I have removed error-checking code for two reasons.
First, it complicates the explanations of the code. Second, this object is intended for compilation in Visual Basic (if you are not using it in a hosting environment where you cannot have compiled components). The error handling code in Visual Basic will be slightly different since Visual Basic supports the use of an on error goto statement. The on error goto statement will allow you to place the error handler in one place instead of VBScript’s error-handling, which requires checking for errors after each statement. The database object could also use a method that returns only the return code from a stored procedure. I often use the return code of a stored procedure for simple status updates from the database instead of returning an entire recordset.
One other item missing is a method that allows you to return a recordset as well as perform an update. Because the calls to the database are optimized, the recordset returning calls are read-only. But I have had some procedures that perform both updates and recordset returns. You have to choose to balance the benefit of such an operation over simply making two separate database calls from the code.
I hope you find this database object useful and that if you are just starting with objects, it will help you learn about prototyping objects using VBScript. Next time, I’ll cover the compiled version of the database object, and then we’ll move on to some form validation techniques.