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.

To return a recordset from the Connection object you use the Execute method. The syntax of this method is:

Connection.Execute CommandText, [RecordsAffected], [Options]

The arguments are:

The Execute method optionally returns a recordset, in which case you simply assign the Recordset variable as the return value. For example:

		Set conPubs = Server.CreateObject("ADODB.Connection")
		conPubs.Open strConn
		set rsAuthors = conPubs.Execute ("Authors")

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:

		Dim strSQL As String
		Dim lngRecs As Long
		strSQL = "UPDATE Titles SET Price = Price * 1.10" & _
			" WHERE Type = 'Business'"
		conPubs.Execute strSQL, lngRecs, adCmdText
		Response.Write lngRecs & " records were updated."

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:

conPubs.Execute strSQL, lngRecs, adCmdText + adExecuteNoRecords

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.

The Command Object
The Command object is designed specifically to deal with commands of any sort, but especially those that require parameters. Like the Connection object, the Command object can run both commands that return recordsets as well as those that don't. In fact, if your command doesn't have parameters, then it really doesn't matter whether you use a Connection, a Command, or a Recordset.

Returning Recordsets
For a recordset-returning command you would use the Execute method. However, unlike the Connection object, you do not specify the command text in the Execute method itself - you have to use the CommandText property:

		Set cmdAuthors = Server.CreateObject("ADODB.Command")
		cmdAuthors.CommandText = "Authors"
		Set rsAuthors = cmdAuthors.Execute

This is the simplest way to tell the Command object to run a simple command that returns a read-only recordset.

The Execute method also has some optional arguments:

		Set conPubs = Server.CreateObject("ADODB.Connection")
		conPubs.Open strConn
		set rsAuthors = conPubs.Execute ("Authors")

		Dim strSQL As String
		Dim lngRecs As Long
		strSQL = "UPDATE Titles SET Price = Price * 1.10" & _
			" WHERE Type = 'Business'"
		conPubs.Execute strSQL, lngRecs, adCmdText
		Response.Write lngRecs & " records were updated."

conPubs.Execute strSQL, lngRecs, adCmdText + adExecuteNoRecords

		Set cmdAuthors = Server.CreateObject("ADODB.Command")
		cmdAuthors.CommandText = "Authors"
		Set rsAuthors = cmdAuthors.Execute