The Command Object - The Dialect Property
(Page 3 of 8 )
This property identifies the dialect to be used for the CommandStream or CommandText properties.
String = Command.Dialect
Command.Dialect = String
The Dialect property is a Globally Unique Identifier (GUID) and is provider-dependent, allowing the provider to support multiple dialects. The default dialect is standard SQL, identified by the following GUID:
{C8B521FB–5CF3–11CE–ADE5–00AA0044773D}
For the SQL XML format supported by SQL Server 2000, you would use the following GUID:
{5D531CB2–E6Ed–11D2–B252–00C04F681B71}
For XPATH support, you should use:
{EC2A4293–E898–11D2–B1B7–00C04F680C56}
Consult your provider documentation to find out any provider-specific dialects.
More details about XML interaction can be found in the ADO help files.
The Name Property This property indicates the name of the Command object.
String = Command.Name
Command.Name = String
In many cases you probably won't use this property, but it could be used to uniquely identify a Command object in a collection of commands. For example, imagine an application that allows users to build up a number of commands. You could store these in a collection and use the commands' Name property to identify them. Think about the query window in SQL Server; you could build quite a good emulation of this by using ADO to connect to multiple data sources and using a collection to store various command objects.
Commands as Connection Methods There is a rarely noted feature that goes like this: if you name a Command object, then associate the command with a Connection object, the Connection object inherits a method corresponding to the name of the Command. This is a dynamic method, which doesn't show up as part of the object's methods or properties within code editors such as Visual Basic.
Let's consider an example. Suppose you have two types of user, a Clerk and a Manager, and both types need to look at employee details. Managers are allowed to see the salary details, but the humble clerks are not. You have decided to use stored procedures to encapsulate all your SQL logic, so you have two possibilities. The first is to build a single SQL statement to return the employee details, and have it check whether the user is a manager or a clerk. Your user then calls a business object that fetches the data, as shown in this diagram of three-tier architecture with the logic in the server:
Note that you now have a business rule built into the SQL. In many cases this is not a problem (and indeed could be deemed good practice), but this doesn't always fit everyones needs. You may find that you'd rather not put business rules into your SQL code. For instance, in this example, you also must work out details such as how to establish which group the user belongs to. That's not a particularly complex problem, but here's an alternative situation that keeps all the business logic in the same place-the business object-as this diagram shows:
In this situation, the application first calls a business object, perhaps when it starts, and the business object establishes the role: clerk or manager. This sets the appropriate stored procedure and attaches the command as a method of the connection. Later, the application can simply call the business object that calls this new method. Because the method is one of two stored procedures, the correct stored procedure is run. Although this seems like more code, it actually simplifies some programming. It puts the business logic in a component where it is easily reused, and it also makes the stored procedures simpler; these will now run faster because there is no run-time decision to make.
When using this method, you must set the Name property before setting the ActiveConnection; otherwise, an error occurs .
One thing that's not documented very clearly-or very often-is that when using this method, the newly named procedure accepts the parameters of the stored procedure, and an extra argument: a recordset. For example, imagine the following stored procedure:
CREATE PROCEDURE usp_TestProcOne
@sAuLName varchar(40)
AS
SELECT * FROM authors
WHERE au_lname = @sAuLName
You could call this using the following code:
strConn = "Provider=SQLOLEDB; Data Source=SQL7Server; " & _
"Initial Catalog=TestData; User Id=sa; Password="
objConn.Open strConn
objCmd.CommandText = "usp_TestProcOne"
objCmd.CommandType = adCmdStoredProc
objCmd.Name = "TestProc"
Set objCmd.ActiveConnection = objConn
objConn.TestProc "Ringer", objRec
This passes Ringer into the stored procedure as the parameter, and the results are returned in the recordset objRec.
Another shortcut is simply to call the stored procedure name directly against the connection:
objConn.Open " . . ."
objConn.usp_TestProcOne "Ringer", objRec
If ADO doesn't recognize the command, it passes the command and parameters to the provider to see if it can handle it. If the provider recognizes the command, the command is executed; otherwise, an error is generated. Note that this, of course, will result in poorer performance.
One particularly interesting feature of named commands is that a variation of it allows you to retrieve output parameters of a stored procedure, even when the provider doesnt support it. This is covered in more detail in the "Retrieving Output Parameters" section at the end of the chapter.
The NamedParameters Property
This property indicates whether parameter names should be passed to the provider.
Boolean = Command.NamedParameters
Command.NamedParameters = Boolean
Setting this property to True means that ADO will pass the name of each parameter to the provider, where the name will be used to match up the parameters. If the value is False (the default), parameters are interpreted in the order created. It is more efficient to add parameters by position because, when adding by name, ADO has to match the parameters to their underlying counterparts.
The Prepared Property This property indicates whether or not to save a compiled version of a command before execution.
Boolean = Command.Prepared
Command.Prepared = Boolean
The default value of the property is False. You should set it to True when the command is to be repeated several times. Although the compilation process will be slower during the first command execution, subsequent executions will be quicker because the command text does not have to be parsed and the execution plan can be reused.
If youre using the SQL Server provider, and you set the Use Procedure for Prepare dynamic property of the Connection object to True, a temporary stored procedure will be created for prepared commands. If you find that you need to prepare a lot of statements, consider moving the SQL into a stored procedure, because this will be more efficient than preparing statements.
Use Procedure for Prepare is one of the provider-specific dynamic properties of the ADO Connection object. For more on ADO's dynamic properties and how to use them, see Appendix C.
If you are creating prepared commands against SQL Server and not disconnecting, you should ensure that the temporary database (tempdb) has enough space to accommodate the stored procedures.
Next: The State Property >>
More Database Articles
More By Apress Publishing
|
 This article is excerpted from ADO Programmer's Reference by David Sussman (Apress, 2004; ISBN: 1590593421). Check it out at your favorite bookstore. Buy this book now.
|
|
