ADO Command Strategies

William R. Vaughn

This article is excerpted from Chapter 5 of Bill Vaughn's new book from Apress, ADO Examples and Best Practices, (ISBN 1-893115-16-x). I suspect most of you know Bill. I think of him as something of a "Mr. VB-DATA" thanks to his best-selling Hitchhiker's Guide to Visual Basic and SQL Server, his extremely popular and content-dense sessions at VBits through the years, and his untiring help to thousands of VB developers in venues such as the VBDAT list.

[Chapter 5, "ADO Command Strategies" is 45 pages long. In addition to providing a comprehensive discussion of the ADO Command object, it includes nine tips, five notes, and two warnings. (If you've read Hitchhiker's Guide, you know how useful—sometimes funny, sometimes almost irreverently candid—Bill's tips can be.) After thinking about David Small's September feature, "Reacquainting Yourself with the ADO Command Object," it occurred to me that it would be useful to follow up with some material from Bill's chapter on the ADO command object. What follows is just a selection of the sections I found most useful. Oh, and by the way, be sure to look for Rob Macdonald's book, also from Apress, Serious ADO: Universal Data Access with VB. Rob's another frequent VBD contributor. Look for the first of a series of articles on VB.Net beginning next month.—Ed.]

It's important for you to know that there sare many different ways to execute Recordset queries—many of which don't require use of the Command object. The one important case where the Command object is required is when you have to capture parameters returned from stored procedures… When it comes time to execute a SQL query, the best object to use is often the ADO Command. However, as you'll see, it's not always the best choice. Fortunately, due to ADO's flexibility, you have other alternatives to draw on. One thing you might not know—it's "under-documented"—is that all ADO Command objects appear as methods on their associated Connection objects. This innovative technique (well, they stole it from RDO) permits you to code the Command by name, followed by its parameters, followed by the Recordset to contain the rowset. Cool.

Tip: When you create a Command object, it should be created once. That is, create as many objects as necessary—don't create one and change its properties (other than the parameters) to suit the immediate requirement. At one time, there was evidence that ADO was making many round trips to the server to "figure out" how to execute the query. My tests show this is no longer happening in ADO 2.5—at least not always. The entire setup phase seems to be done entirely on the client. But this is still overhead that you don't have to tolerate more than once.

Setting ADO properties is really a matter of knowing what ADO expects. If you're supposed to provide a number, make sure it's in the correct range.

The Name property

If you want to use the (cool) "Command as Connection method" technique to execute your Command object, you must name it. It's not a bad idea to do so in any case. If you're executing a stored procedure, the name must match the name of the stored procedure. Otherwise, you're free to name the command after jungle plants if you're so inclined. Just remember to set your name early—before you set the ActiveConnection property. And don't forget to use a String constant or variable to name your Command.

Tip: If you pass in an unquoted value [as the Name Property] instead of a String constant or declared variable, Visual Basic assumes it's the name of a Variant variable that will have some value later at runtime, unless you have Option Explicit turned on (you should). In this case, you'll get a healthy "Variable Not Defined" warning at compile time.

The (so-called) Prepared property

In theory, the Prepared property was designed to reduce work on the server by pre-compiling ad hoc queries so subsequent executions would use a temporary stored procedure instead of repeating the compile phase each time the query is executed. However, this is not the case with ADO's implementation—keep reading.

Since ODBC was invented some years ago, SQL Server has gotten much smarter—it now knows how to leverage existing (in cache) compiled query plans. That is, once you execute a query from ADO (or by any means), SQL Server constructs a query plan, saves it in the procedure cache, and executes it. When the query is done, SQL Server marks the query plan as "discardable" but leaves it in memory as long as it can. When another identical (or close-enough) query comes in, which is very likely in systems running multiple clients, SQL Server simply re-uses the cached plan. This saves a significant amount of time and greatly improves scalability. It makes SQL Server actually run faster as more users are added, assuming they're doing about the same things with the same set of queries.

ADO and its ODBC and OLE DB data providers know about this strategy, and in most cases they execute sp_executesql to take advantage of this feature. However, this puts the Prepared property in a quandary. It insists on creating temporary stored procedures, but the data providers insist on using sp_executesql. The result? Chaos. I describe what happens a little later when executing Command objects is discussed.

My recommendation for the Prepared property: forget it—at least for SQL Server. For other providers, set up a trace that shows exactly what's going on—what the server is being asked to do.

The CommandTimeout property

The CommandTimeout property indicates how long to wait (in seconds) while executing a command before terminating the attempt and generating an error. Remember, CommandTimeout starts when the database server has accepted the command and ends when the database server returns the first record. If the server or network is busy (or not answering at all), this setting won't help you to get control again.

In RDO (and in the ODBC API), I had another option that was lost when I converted to ADO-retry on timeout. That is, if you wanted to keep waiting after a CommandTimeout, you could simply pass a flag back to the event handler and keep waiting. This is not implemented in ADO. Why? BHOM (another "technical" term I learned in the Army that means, "beats the hell out of me").

Note: ADO timeout settings are independent of network timeout. Since the low-level network driver makes a synchronous network API call, and since this call doesn't return until the network timeout expires, the ADO timeout code is blocked.

Handling parameter-based queries

Most queries you execute require one or more parameters to govern the rowset created. These parameters are usually applied to the WHERE clause of a query, but they can be used in a variety of other ways. When you construct any parameter-based query, you have to describe the query parameters one way or another and supply the runtime values, but you don't have to use the Command object—not unless you expect to deal with a stored procedure return status or with output parameters. You can use other techniques to pass input parameters, and I'll discuss those next. Basically, there are several approaches you can take when constructing parameter queries:

• Construct a Command object in code, which exposes an empty ADO Parameters collection. This approach can generate the Parameters collection using the Refresh method or by constructing the Parameters one-by-one.
• Construct an SQL statement that includes the parameters in the query string. This approach can construct an sp_executesql query instead of having ADO construct one for you.
• The Visual Database Tools, including the Data Environment Designer, can also construct parameter-based SQL statements and expose these as Data Environment Designer-based Command objects. These are discussed more completely in the Hitchhiker's Guide to Visual Basic and SQL Server. In this material, I'll stay focused on ADO coding.
• Download Sprocaddin.exe.

How ADO Command objects manage your parameters

When you use the ADO Parameters collection to manage your parameters, it's ADO's responsibility to get these parameters crammed into the query in the right places and in the right format. ADO is also responsible for dealing with "framing" quotes. That is, if the parameter has embedded single-quotes to delineate strings (most do), ADO will automatically double these up (replacing a single quote with two single quotes)… When working with Command objects, it's your responsibility to describe the parameters correctly, unless you use the Refresh method. This means constructing the Parameters collection one parameter at a time in the order the data provider expects them. No, ADO and its data providers don't support "named" parameters, so you have to specify them in the right order. Knowing how to describe the Parameter datatype, size, precision, scale, and shoe size is your responsibility. If you get it wrong, you'll get an error. If you get them out of order, who knows what will happen...

There are a number of techniques I can illuminate to make your parameter-based queries more efficient and easier to construct. Eventually, all of your production applications will evolve to depend on parameter queries to both improve performance (both system and developer performance) and to simplify the development of component-based designs.

Constructing the Parameters collection

The ADO Command object's Parameters collection manages all flavors of parameters: gazintas (input), gazouta (output), and gazinta-gazouta (input-output-bi-directional) parameters. Remember, input parameters can be applied to ad hoc queries as well as stored procedures. The trick is learning how and when to construct the Command object's Parameters collection in code. As I have said before, there are two approaches:

• Use the Command.Parameters.Refresh method to get ADO and the associated provider to construct the Parameters collection for you, based on the CommandText you've provided.
• Construct the Command.Parameters collection yourself-parameter-by-parameter, based on your understanding of how the parameters are defined.

Each technique has its advantages and disadvantages in terms of developer and application performance. IMHO, neither technique should be used from the middle tier if you can help it. Why? Well, consider that the time taken to execute the extra code to build the Command object and the appropriate Parameter objects (one at a time) is wasted. It has to be re-executed each time the ASP is referenced or the MTS component is executed. Yes, the Command objects make the process far simpler to code. If you're looking for a simple solution with somewhat limited scalability, then keep reading.

Using the Refresh method

The Command.Parameters.Refresh method technique seems to do all of the work for you—it constructs the Command object's Parameters collection for you in a single line of code. That's good and bad. It's good in the sense that you don't have to worry about how ADO creates the Parameter objects (for the most part, that is—it usually gets them right). It's bad in that ADO and the provider take a round trip to the server to figure out how to do so, and this can be costly (as we've already discussed). However, since this can be a one-time performance hit early in the life of the application, it might not make that much difference.

Remember to set the ActiveConnection property before attempting to use the Refresh method—ADO needs a valid connection to hit the database to generate the parameters.

Tip: Actually, you don't even have to use the Refresh method if you don't want to, and using it might even cause ADO to execute an extra round trip. When you try to read a property of an un-initialized Command.Parameters collection for the first time, ADO constructs the Parameters collection for you—just as if you'd executed the Refresh method.

The Parameter.Size property

Use the Size property to determine the maximum size for values written to or read from the Value property of a Parameter object. If the size of the parameter exceeds this upper limit, you'll trip a trappable error.

Constructing Parameter objects with CreateParameter

You can create individual Parameter objects and add them to the Parameters collection using the Append method:

Dim Pr as Parameter
Set Pr = New Parameter
With Pr
    .Name = "P1"
    .Type = adVarChar
    .Direction = adParmInput
    .Size = 30
    .Value = "Fred graduates in 2000"
End With
Cmd.Append Pr

If you charge by the line, this is the best approach, but it's much easier to do all of this with a single line of code:

.Parameters.Append .CreateParameter("P1", adVarChar,_
adParamInput, 30, "Fred graduates in 2000")

The CreateParameter method constructs the entire Parameter object in a single step. It doesn't include the NumericScale or Precision properties, but it does include the other essential properties—including Value. Because this method reminds you of all of the essential properties, prompting you for each one as you type in the Visual Basic IDE, its use qualifies as a "best practice."

Rolling your own Parameter queries

No, you don't have to use the Parameters collection to execute a parameter query, or queries without parameters for that matter. For instance, you can construct a query in a string and pass it to the Source property of a Recordset or the CommandText property of a Command object. You can also execute any stored procedure as a method of the Connection object. The stored procedure parameters are simply passed as method arguments.

Passing arguments with Variant arrays

Another way to pass in input parameters is to use a Variant array. This approach is kinda cool. And better yet, if you don't provide one of the elements, ADO doesn't submit the parameter—it assumes the provider will insert the "default" value. If there's no default value set, either in the called stored procedure or in the initial Parameter collection, ADO returns a trappable error: "-2147217900 Incorrect syntax near the keyword 'DEFAULT.'"

Link to http://www.betav.com

To find out more about Visual Basic Developer and Pinnacle Publishing, visit their website at http://www.pinpub.com/

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the October 2000 issue of Visual Basic Developer. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Visual Basic Developer is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.