Standardizing Data Access with ADO and ASP Scripts

Data-access code is usually the foundation to building any Web application. Developers typically write ADO code that uses either the Command or Recordset object and sets various properties. Then, the code either executes a SQL statement or calls a stored procedure. Creating this code in each script is time-consuming and error-prone, so I was delighted when Microsoft recently sent me a new include file that provides a standard set of data-access functions that you can use from any Active Server Pages (ASP) application.

Microsoft has stress-tested this include file as part of its benchmarking efforts, and the code is fast and reliable. You can use these functions any time you need to execute a SQL statement, run a stored procedure, or create an ADO Recordset object from a database. The functions in the include file will streamline your script code and make writing data-access applications much easier.

In the Include File

Let's look at the database functions in the support file, ADOHelper, and see how it uses them. This file contains the functions that provide the ADO database support. The file, adohelper.asp, as Listing 1 shows, implements a VBScript class using the Class statement, which creates a class named ADOHelper. Your application can invoke ADOHelper as if it were a COM object.

Class ADOHelper

At the end of the file, an End class statement terminates the class.

End class

Other than these two statements, adohelper.asp is traditional VBScript. In fact, you could remove the Class statements and use ADOHelper as a normal include file. Using the code as a class makes the code more intuitive because in your script you can see that you're using a method from the class. However, if you don't want to use the code as a class and just want to call the functions, you can remove the Class statements. The choice is yours.

ADOHelper contains several functions—methods in object speak—that implement the class's database features. The first function is GetConnectionString, which returns the connection string used to access your application. You must change this function's connection string information to match the names of your server and database and the required login information.

Four other key functions in the include file begin with Run, each executing either a SQL statement or a stored procedure. Table 1 describes the Run functions. The two RunSP functions accept the same three parameters. The first parameter is the name of the stored procedure you want to execute. The second is the name of an array containing the stored procedure's input parameters and their criteria. The third is the name of a variable that will contain any output parameters that the stored procedure generates.

All four Run functions have similar code; for example, the following is the code for RunSP.

Function RunSP(ByVal strSP, params, byRef OutArray)   Dim cmd, OutPutParmsSet cmd = Server.CreateObject("adodb.Command")

   cmd.ActiveConnection = GetConnectionString()cmd.CommandText = strSPcmd.CommandType = adCmdStoredProccollectParams cmd, params, OutPutParms

   cmd.Execute , , adExecuteNoRecordsif OutPutParms then OutArray = collectOutputParms(cmd, params)    Set cmd.ActiveConnection = Nothingset cmd = NothingRunSP = 0

End Function

The RunSP function contains two noteworthy pieces of code: the collectParams function and the adExecuteNoRecords parameter. Various scripts use the collectParams function to build the ADO parameters collection. Setting up the parameters for a stored procedure call is perhaps the most trying part of using stored procedures. The collectParams function simplifies the process of creating and using parameters with a stored procedure and makes setting them up a non-issue.

RunSP also uses the adExecuteNoRecords parameter on the cmd.Execute line. This parameter tells ADO that the requested action doesn't need a recordset. Using this parameter usually gives your application a performance boost because ADO and the database will have less work to do.

Using the File

Using the adohelper.asp file from your ASP scripts requires a couple of steps.

This line links the ADO type library into your application. You can create this link automatically in Microsoft Visual InterDev by going to the Project menu, Project References, and selecting the proper reference, as Figure 1 shows.

As a relative link (without a path), you must adjust the link based upon the configuration of your application directories.

After you have taken these two steps, you can use the include file's features. Listing 2 shows my code for ListAuthor.asp, which calls the retrieveauthors stored procedure to obtain a recordset of all authors from the Pubs database. (Note: This code isn't a standard part of the Pubs database.)

So far, we've used the stored procedures by setting a couple of variables and executing one function. Not bad! That's the beauty of this include file.

In a slightly more complex case, Listing 3 shows my code for ShowAuthor.asp. This code also executes the RunSPReturnRS but to execute the RetrieveAuthor stored procedure. (This code isn't a standard part of the Pubs database either.) RetrieveAuthor requires one parameter: the author id (au_id). The code at Callout A in Listing 3 sets the data for this parameter and stores it in the sParams variable.

The line that sets the sParams variable looks strange because it calls the Array function twice. First, the function sets up the array of parameter arrays; then, each parameter calls the function once. The result is an array of parameters, with each parameter having four entries. A more complex stored procedure call from another application shows how the array of parameter arrays works.

Set rs = db.RunSPReturnRS("Add_CartItem", Array( _Array("@SessionID", adInteger, AdParamInput,4, CLng(group)), __  Array("@BookID", adInteger, AdParamInput, 4, bookID), _     Array("@Author", adVarChar, AdParamInput, 50, author), __     Array("@Title", adVarChar,  AdParamInput, 50, title),  _     Array("@Price", adCurrency, AdParamInput, 8, price), _  Array("@Qty", adInteger,  AdParamInput, 4, Qty)),       OutArray)

Six Array statements are nested in the first array, and each of the subarrays sets one parameter. To get the parameters' values, I use the Data View window in Visual InterDev as I build the application. Data View lets me inspect the tables and stored procedures. Then I can look at each parameter's properties and use them to set or pass the parameter definitions.

Faster and More Reliable

Standardized code is one of the best ways to make your application run faster and more reliably. Using functions such as those in adohelper.asp also speeds up the development process because you don't need to recreate or repeat ADO code each time you touch the database.