Mastering ADO

Microsoft introduced ADO and Active Server Pages (ASP) in 1997 as the primary means of building data-driven Web applications. Since then, ADO has become the standard API developers use to work with databases for applications built with Microsoft tools and technologies. Micro-soft also touts ADO as the standard API for the future. These tips and examples will help you understand and use ADO.

How ADO Works

Because ADO is a set of COM objects, you must use either CreateObject or the Object tag to instantiate the objects before you can use them. (I discuss using CreateObject later in this article.) Then, if you're programming in Visual Basic (VB), you'll need to create a project reference to the ADO library, as Screen 1, page 32, shows. Next, in your VB program, you need to create an object variable of the appropriate type for the object you want to use. VB will use early binding to the ADO object.

After you've set up the project reference, VB will read the ADO type library and use the constants defined in the library. For example, when you use the adOpenForwardOnly constant in your VB program, VB finds this constant's definition in the ADO type library. If you're using ADO from ASP instead of VB, you need to take a different approach. ASP doesn't allow explicit variable definitions, so it can't use early binding. However, you can create a reference to the ADO type library in the global.asa file. For example, using Visual InterDev you can create a project reference to the ADO type library. Visual InterDev will place the following entry in the global.asa file:

Creating such a reference in the global.asa file lets you use ADO type library constants in your ASP code without an include file. This capability makes your code more readable, and it might execute faster. When you use the ADO type library constants, your program doesn't have to load the constants as an include file. This approach can make a big performance difference when you load ASP pages from disk.

Creating Explicit or Implicit Connections

ADO needs an explicit or implicit connection to connect with SQL Server. To create an explicit connection, you use the ADO Connection object to create the connection, then you use that connection with a Recordset or Command object. To create an implicit connection, instead of creating a Connection object and passing it to the command object, you simply specify the connection string when you create the Command or Recordset object.

Listing 1 shows how you can use a Connection object explicitly. When the application starts, CreateObject creates the ADO objects. Then the code sets the ConnectionString property of the Connection object to the connection string for the Pubs database. Next, the connection opens, and the program passes the Connection object to the Command object when it sets the Command object's ActiveConnection property.

One benefit of explicitly creating the Connection object is flexibility. You can easily set properties for the connection, such as its timeout parameter or the cursor or location at the Connection object. You can also pass the Connection object to one or more commands or recordsets.

Many developers use a shortcut way of creating connections. I call this approach an implicit connection, and Listing 2 illustrates it. Instead of creating a Connection object and passing it to the Command object, you can simply specify the Connection string when you create the Command or Recordset object, as the statement at Callout A in Listing 2 shows.

The only difference between the explicit and implicit ways of setting the connection is that when you set it implicitly, you don't have a separate Connection object to work with. You can also create a connection implicitly when you open a recordset. Instead of using the Recordset object in the way Listing 1 and Callout B in Listing 2 show, you can use

rs.Open "select * from titles", sDSN ,   adOpenForwardOnly, adLockReadOnly

With this one line of code, you can specify the SQL and connection string cleanly without extraneous commands. However, you have less flexibility when you use this single line of code than when you treat the connection as a separate object and pass it from one command or recordset to another. If you create a Connection object, you can use it for many connections. This method lets you save code and localize the database connection information to one connection.

Another consideration with this approach is that the last Open statement doesn't specify the Options parameter. The Options statement lets you specify the command type. Setting the command type optimizes ADO's performance because ADO doesn't have to do as much work to execute the command. When you don't specify the command type, ADO must query the provider each time the command executes to determine which type of command it is. You can also specify the command type of the Command object by using the CommandType property.

Connection and command objects are flexible. If your application uses COM objects, you can hide connection information in a COM object and pass the connection to other COM objects and ASP script. This capability gives you security and flexibility. The Connection object works well because of this flexibility, but you can choose how you want to implement connections in your code.

ADO and Stored Procedures

More developers are starting to use stored procedures with ADO to optimize the performance of database interactions. Stored procedures are easy to create and use. You can implement stored procedures in your code in many ways. First, you can use a stored procedure with ADO as you would use a stored procedure with a SQL statement. The Listing called "Two Ways to Use a Stored Procedure," which you can download at the link to this article at http://www.sqlmag.com, shows two ways to use stored procedures with ADO. The cmdGetTitles_click event procedure sets the CommandText property of the Command object to the stored procedure's name, as Callout A in the online listing shows:

cmd.CommandText = "GetTitles"

Next, the code sets the CommandType property to the adCmdStoredProc constant:

cmd.CommandType = adCmdStoredProc

The remaining code is similar to the examples in Listing 1 and Listing 2.

The GetTitles stored procedure doesn't require any parameters and works without them. However, if you want to use a stored procedure that requires parameters, you'll need to make a minor change. For example, you can change the code in the cmdGetTitles_Click procedure to call the GetTitleByID stored procedure (in Listing 3) with this code:

cmd.CommandText = "GetTitlebyID '" &   Trim$(txtTitleID) & "_"cmd.CommandType = adCmdStoredProc

When you execute this code, you'll see an error message because the CommandType is telling ADO that you're calling a stored procedure. However, the command you're really trying to send to SQL Server is

Exec GetTitlebyID 'BU1032'

You can see this situation by starting a new trace with the SQL Server Profiler on the database server. Then watch what happens when you execute the above Exec command with the CommandType set to adCmdText, as the online listing shows. The original command doesn't even reach SQL Server because ADO catches the command and evaluates the call to the stored procedure. Because the command is both the stored procedure name and the parameter, the call fails and generates an error. To avoid this error, You'll need to change the CommandType to use the adCmdText constant, as the following code shows:

cmd.CommandType = adCmdText

Now, when you watch this command execute in SQL Server Profiler, you'll see the Exec command:

Exec GetTitlebyID 'BU1032'

You can also use the Parameters collection of the Command object to supply the stored procedure's parameters. The syntax for the methods and properties of the Parameters collection is straightforward. However, it can be awkward to create many para- meter statements every time you want to use the Parameters collection. Callout B in the online listing shows how to use the Parameters collection in the cmdGet1TitleParam_Click procedure:

cmd.Parameters.Appendcmd.CreateParameter("TitleID", adChar,adParamInput, 6, _   Trim(txtTitleID))

This line of code first calls the CreateParameter method of the Command object to create a new parameter named TitleID. This new input parameter has a data type of adChar and is six characters long. You set the value of the parameter from the txtTitleID textbox. Next, this code calls the Command object's Append method to append the new parameter to the command. This line of code might seem long, but it keeps the listing simple and accomplishes the task efficiently.

Alternatively, you can create named parameters and use them when you need to. You use the same syntax with the Create-Parameter method as with the above cmd.Parameters.Append command. Instead of passing the parameter into the Command object's Append method, you create the parameter and set a reference to it, as the cmdGet1TitleParam_Click event procedure in Callout B in the online listing illustrates. The command procedure creates the parameter in the error-handling section of the code:

Set mParam = mcmd.CreateParameter("TitleID",adChar, adParamInput, 6, Trim(txtTitleID))

The mParam variable points to the Parameter object. You can use the pointer to append the Parameter object to the Command object:

mcmd.Parameters.Append mParam

You might wonder why you would create a pointer to the Parameter object instead of simply using the original method, which directly appends the Parameter object and doesn't bother to create an object reference. The answer is that the parameter is reusable. The cmdSetParam_Click procedure shows how you can reuse the parameter in the If statement early in the procedure:

If mParam.Value > "" ThenmParam.Value = txtTitleIDEnd If

This code simply sets the parameter's Value property to the new title ID. After the Value property changes, you can execute a new command with the command object or reopen the recordset to retrieve the new record. When you create and reuse the parameter object, you can easily set and reset parameters in your code. You can use the Refresh method of the Parameters collection to pull the attributes for the parameters. The Refresh method frees you of the need to specify them, but it generates an extra call to the server, which slows the application.

When you use a stored procedure that doesn't return a recordset, you can use the Execute method of either the Command or the Connection object to execute that stored procedure. If you don't want ADO to return a recordset, you can execute the command with the adExecuteNoRecords option:

cmd.Execute , , ADODB.adExecuteNoRecords

The adExecuteNoRecords option can speed up your application because ADO doesn't generate a recordset. Use this option only when the stored procedure or SQL statement performs an action that doesn't return a recordset.

ADO's Future

Microsoft touts ADO as a universal data-access technology. My tests of the code in this article demonstrate the portability of ADO across programming languages and confirm the universal data-access claims. I did most of the tests in VB 6.0, ported most of the code to ASP, then reran the tests. As I expected, all test results from VB and ASP were the same.

Windows 2000 (Win2K) includes ADO 2.5. This latest version of ADO continues to provide new features for developers, such as the Stream object and the new Record object, that you can use with recordsets. You can open a Record object directly with ADO's Open method, or you can use the Open method to open a record from a recordset or URL. You can use the new Stream object to dynamically store data in memory in a way similar to how you save a recordset to disk in ADO 2.1.

ADO 2.5 also lets you work more easily with non-structured data, such as file systems or message folders, which helps when you're working with non-relational data in your applications. You can work with various types of data, query, add, and change data, and search through collections of non-relational data.

For more information about ADO, see VB Toolkit, "Saving and Restoring Recordsets" (November 1999) and "Using ADO Parameters," Web Exclusive Article ID 7849 on the SQL Server Magazine Web site at http://www.sqlmag.com.

Mastering ADO with VB and ASP can be easy if you try these examples and learn how they work. You'll benefit from increased performance when you use ADO with implicit and explicit connections, command types, stored procedures, the Parameters collection, and the new ADO 2.5 features.