Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Introduction to ADO - The Command Object

By Andy Warren, (first published: 2001/12/18)

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!

Total article views: 39460 | Views in the last 30 days: 21
 
Related Articles
FORUM

Command text was not set for the command object

Command text was not set for the command object

FORUM

DBCC command execution History

When did a DBCC command executed in a DB

FORUM

Object Execution StatisticsReport

Object Execution StatisticsReport

FORUM

BCP Command with Parameters

Stored Procedure with parameters to be executed with BCP

FORUM

SSIS error while executing in command line

SSIS error while executing in command line

Tags
ado    
programming    
stored procedures    
t-sql    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones