Introduction to ADO - The Command Object

,

In two previous articles I've done a very basic introductions to the ADO

Connection and ADO Recordset objects. This week I'd like to talk about the third

main object, the Command object. Reading the previous articles isn't a

requirement for making sense of this article, but probably worthwhile!

Let's jump right in. Here is a short code sample that shows how to execute an

update (or insert or delete) query using a command object. For the duration of

the article we'll assume you've already got a connection object open.

Dim cmd As ADODB.Command

Dim cn As ADODB.Connection

'open connection here, as discussed in other articles

Set cmd = New ADODB.Command

Set cmd.ActiveConnection = cn

cmd.Execute "update customers set country='USA' where customerID=301"

As you can see, it works exactly the same way that using the connection

execute method does. You can also use it to execute a stored procedure, again

using the same syntax I illustrated earlier with the connection object:

cmd.execute "usp_whatever"

If the connection object can do it all, why use the command object. Probably

the first reason is that you can give ADO "hints" to help it execute

the statements more efficiently by setting the command type property. Taking a

look at the options you see in the VB environment, you would select acCmdText

for the first code example that uses dynamic SQL, or use acCmdStoredProc for the

second example that executes a stored procedure.

Nice, but not really a compelling argument? Maybe..but so far

we're just sending parameters, not leveraging the ability to declare parameters

as output so that we can get a value returned to us. Let's take a look at

something where the command object really excels, executing stored procedures

with parameters. Using either the connection object or the command object, you

can do it like this:

Dim dteStart As Date

Dim dteFinish As Date

cmd.CommandType = adCmdStoredProc

cmd.Execute "North.dbo.[Employee Sales by Country] '" & dteStart & "','" & dteFinish & "'"

We're just passing the

parameters in the order that they are expected. In this case both parameters are

dates, so I'm surrounding each with single quotes (Access developers remember to

use the single quote and not the pound sign for date delimiters!).

Here is a full example showing

how to use the parameters collection of a command object:

Dim cn as ADODB.Connection

Dim cmd as ADODB.Command

Dim params as ADODB.Parameters

Dim param as ADODB.Parameter

' Create connection and command objects

Set cn = New ADODB.Connection

Set cmd = New ADODB.Command

' Set connection properties and open

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security

Info=False;Initial Catalog=Northwind;Data Source=TESTBOX"

cn.Open

' Set command properties

With cmd

    Set .ActiveConnection = cn

    .CommandText = "usp_BuildCat2List"

    .CommandType = adCmdStoredProc

    Set params = .Parameters

End With

' Refresh parameters from database

params.Refresh

' Specify input parameter values

params("@ParentID") = MyVariable

params("@Description") = MyVariable

' Execute the command

cmd.Execute , , adExecuteNoRecords

There are a couple key points in this example. The first is the

"params.refresh" call. Using refresh does the work for you of

populating the parameters collection - for each parameter, you have to provide

the name, data type, whether it's input or output. Refresh just queries the

server for the parameter info and sets it up for you. This is good for you

because it's easy, but generally considered a bad practice because it generates

an extra "trip" to the server. We'll talk about alternatives in a

minute. Then the next couple lines are where we start to see how having a

parameters collection makes the code more readable:

' Specify input parameter values

params("@ParentID") = ParentID

params("@Description") = Description

ParentID and Description could be parameters of a sub routine,

or even properties in a class. The other thing you may have noticed is this

line:

' Execute the command

cmd.Execute , , adExecuteNoRecords

Passing the adExecuteNoRecords tells ADO that you're only

expecting parameters back so there is no need to generate a recordset - saves

some processing time and some bandwidth. Definitely a good idea to use it.

Now let's take another look at the "right" way to use

the parameters collection. This involves building all the parameters up in code.

Instead of params.refresh, we'll insert this code:

' Define stored procedure params and append to command.

params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)

params.Append cmd.CreateParameter("@ParentID", adInteger, adParamInput, 0)

params.Append cmd.CreateParameter("@Description", adVarChar, adParamInput, 500)

It's more work up front, but it's the best way to get the

maximum performance. After all, isn't that why you wrote the stored procedure in

the first place? Finally, as with all objects, be sure to close them when you're

done by setting your command object = Nothing.

Coming up in the next week or two I'll have an article that

combines all three of the main ADO objects into one simple application so you'll

have a chance to really see them in action. Thanks for reading the article. Got

a comment or question about it? Click the tab below!

Rate

5 (1)

Share

Share

Rate

5 (1)