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!
