Connections, Commands And Procedures
In the previous chapter, we looked at the basics of ADO, concerning ourselves mainly with the Recordset object and the manipulation of data. In most of the examples, we obtained data by simply specifying the name of a database table, but as you've seen from the object model, ADO has other objects that allow data to be accessed.
In this chapter we are going to look at some of these in more detail. In particular, we shall examine:
The Connection object, and how it can be used to retrieve data and run commands
The Command object, why it is necessary, and what facilities it has
How to run stored procedures, especially those that require parameters
Some simple optimization techniques for improving ADO performance
What Data Shaping is, and how it can be used
Like the Recordset object, we're not going to cover all of the methods and properties of the objects involved. Instead, we're going to cover the most important topics, and those methods and properties that are most applicable for an ASP developer.
The Connection Object
We mentioned in the previous chapter that the Connection object is what gives us a connection to the data store, but that's not all the Connection object does. As well as storing details of the connection (such as the type of data store and the features it supports), we can use the connection to run commands.
These commands can be action queries, such as updates, inserts or deletes, as well as commands that return a recordset. You might wonder what use this is, since we have the Recordset object, but it's all part of the flexibility of ADO, that allows you to use whichever object is the most convenient, and most suited to the task in hand.
The commands run from the Connection object are generally action queries, but it's useful to know that you can get recordsets returned too.
Returning a Recordset
To return a recordset from the Connection object you use the Execute method. The syntax of this method is:
The Execute method optionally returns a recordset, in which case you simply assign the Recordset variable as the return value. For example:
You might wonder what the difference is between using the Execute method of the Connection object and the Open method of the Recordset object. It may not seem that there's much difference, but remember that with the Open method of the Recordset you have the ability to change the cursor type and lock type of the resulting recordset. These options are not available for the Execute method of the connection, so you will always get a forward-only, read-only recordset.
Action Commands
If you are running action commands, such as a SQL UPDATE statement, then you can use the RecordsAffected argument to find out how many records were affected by the command. For example:
This updates the price for all business books by 10%. Once the Execute command has completed, the number of titles affected by the update will be available in lngRecs - this is the RecordsAffected argument.
Notice that we've specified adCmdText for the options of this command - this tells ADO that the command text is a text command. Whilst this isn't strictly necessary, it does allow ADO to know ahead of time what sort of command is being executed, and therefore improves performance. Remember that this value can be one or more of the values from the CommandTypeEnum values.
No Recordset Returned
If no recordset is being returned, as in the example above, then it's also best to add another option to the Execute statement:
Using adExecuteNoRecords tells ADO that the command being executed does not return any records. ADO therefore doesn't bother building a recordset. If you omit this option then ADO builds an empty recordset, which is clearly a waste of time, so adding the option will speed up the statement.
