| Downloads |
|---|
| 98718.zip |
|
Executive Summary: Learn about the primary Microsoft ActiveX Data Objects (ADO) objects—Connection, Command, Parameter, Recordset, and Field—and follow example scripts that demonstrate how to use ADO within VBScript.
|
Microsoft ActiveX Data Objects (ADO) provides a data access model that lets you use a similar approach for working with data from various data sources. For example, you can use ADO within VBScript to access a database in Microsoft SQL Server, Microsoft Access, or another relational database management system (RDBMS). You can even access data in a Microsoft Excel spreadsheet.
ADO, which is based on COM, acts as an interface between the program or scripting language and an OLE DB provider. The ADO objects let you connect to a data source, issue a command against that source, and retrieve data into a recordset, which you can view or modify without calling the data source repeatedly. In this article, I introduce you to the primary ADO objects and provide examples that demonstrate how to use ADO within VBScript. (For definitions of several ADO terms, see the sidebar “ADO Acronyms.”)
The ADO Object Model
When you use ADO to access data, you must create the objects necessary to connect to the data source and work with the data. Figure 1 shows the basic objects necessary to get started working with ADO. These objects include:
- Connection—Defines the connection to the specific data source.
- Command—Defines the command executed against the data source.
- Parameter—Defines a parameter that is used with a stored procedure or parameterized query.
- Recordset—Contains the result set returned by the query.
- Field—Contains a field (column) included in the Recordset result set.
Although Figure 1 shows the objects hierarchically, it’s a loose hierarchy. For example, you can create a Recordset object without creating a Connection object. I discuss all of the objects in more detail in the following sections of the article.
In addition to the objects shown in Figure 1, ADO supports several other objects, such as Record and Stream. The Record object provides access to hierarchical data such as a file system, and the Stream object provides access to data within a document. Although both objects are important components of ADO, they’re beyond the scope of this article. For more information about ADO objects, as well as their properties and methods, see MSDN’s “ADO API Reference.”
The Connection Object
The Connection object defines the connection to the data source. At the heart of this object is the connection string, which contains the information necessary to connect to a specific data source. When adding the Connection object to your script, you should create the object, define the connection string, and open the connection. Let’s take a look at an example to demonstrate how this works.
Listing 1 includes the ADO code necessary to connect to the SQL Server 2005 AdventureWorks test database. After I declare my variables, I use the CreateObject method to create the Connection object (ADODB.Connection). I then assign the object to the conSqlServer variable.
Next, I create the connection string and assign it to the csSqlServer variable. In the connection string, I specify the following:
- The OLE DB provider, which for SQL Server is sqloledb (Provider='sqloledb')
- The SQL Server instance I want to use as my data source (Data Source='ws04')
- Integrated security and the Security Support Provider Interface (SSPI), a common API for obtaining integrated security services (Integrated Security='SSPI')
- The database I want to connect to (Initial Catalog='AdventureWorks')
As you can see, the connection string defines not only the type of data source, but the data source itself. As a result, the information you provide in the connection string varies from source to source. For example, Listing 2 shows how you would connect to an Access database. In this case, the connection string specifies the OLE DB provider as Microsoft.Jet.OLEDB.4.0. In addition, the data source is simply the path and name of the .mdb file.
The connection string to an Excel file is similar to that for an Access file. As Listing 3 shows, the connection string once again specifies Microsoft.Jet.OLEDB.4.0 as the OLE DB provider and the path and name of the .xls file as the data source. However, the Excel connection string also includes the Extended Properties parameter, which is set to Excel (Extended Properties=Excel 8.0).
Now let’s take a look at another connection string. Listing 4 shows a connection to a MySQL database. The provider in this case is msdasql, which is the basic OLE DB driver for ODBC connections. To use this provider, I installed the ODBC driver that MySQL provides. I then created a system Data Source Name (DSN) that uses the MySQL ODBC driver. The DSN, which I named MySQL_AW, defines the ODBC connection to the MySQL database. Finally, I specify the DSN in the connection string in my script (DSN=MySQL_AW).
Notice that, for each listing, I first create the Connection object and assign it to a variable. Then I define the connection string. Finally, I open the connection. To open the connection, I use the Connection object’s Open method. For example, in Listing 1, to open the connection, I specify the variable that contains the connection, followed by a period and the Open method, as in conSqlServer.Open. Notice that the Open method takes as an argument the variable that contains the connection string (csSqlServer).
That’s all there is to setting up a connection. After you open the connection, you’re ready to move on to the Command object.
The Command Object
The Command object provides the structure for passing a command to a data source. The first step is to create the Command object itself. If you refer back to Listing 1, you’ll see that I once again use the CreateObject method to create the object (ADODB.Command). I then assign the object to the cmdContacts variable.
The next step is to associate the Connection object with the Command object, which you do by setting the Command object’s ActiveConnection property (cmdContacts.ActiveConnection = conSqlServer).
After you create the Command object and set the connection, you define the connection text. Because the connection shown in Listing 1 is accessing a SQL Server database, the connection text is a T-SQL statement. The statement retrieves the first 10 rows from the Person.Contact table in the AdventureWorks database. Listing 2 uses a similar SQL statement to access data from the Contacts table in the Access database. Listing 3, however, uses a different convention for the command text because I’m accessing an Excel spreadsheet. Notice that my command text specifies the name of the spreadsheet (Contacts) and the range of cells to retrieve (A1:B11). The command text in Listing 4, which is accessing data from MySQL, shows command text similar to Listing 1 and Listing 2, except that the SQL statement is specific to MySQL conventions.
In each listing, I assign the command text to a variable and then use that variable to set the Command object’s CommandText property. For example, in Listing 1, I assign the ctContacts variable to the CommandText property of the cmdContacts object variable (cmdContacts.CommandText = ctContacts). With the ActiveConnection and CommandText properties set, you’re ready to create your Recordset object.
The Recordset Object
The Recordset object is the workhorse in ADO. It holds the data returned by the command text, which you can then view and manipulate within VBScript.
As with the Connection and Command objects, your first step is to use the CreateObject method to create the Recordset object (ADODB.Recordset). In Listing 1, for example, I create the Recordset object and assign it to the rsContacts variable. I then use that variable to call the object’s Open method (rsContacts.Open). When I call this method, I pass the Command object (saved to cmdContacts) as an argument. This process executes the command text and populates the Recordset object with the result set. You can then use the properties and methods of the Recordset object to access that data.
In Listing 1, for example, I use the Recordset object’s Save method to save the result set to an .xml file. The Save method takes two arguments. The first argument specifies the path and name of the target file (C:\Info\SqlServerContacts.xml), and the second argument specifies the format of the data. In this case, I specify the adPersistXML constant (which equals 1). When you specify the adPersistXML constant, the data is saved in an XML format. (Refer to MSDN for details about an object’s properties and the methods and arguments they take.)
After I save the data to the .xml file, I use the Recordset object’s State property to determine whether the object is still open (by checking the adStateOpen constant). If it is, I use the object’s Close method to close the recordset. Notice that I use the same approach to close the Connection object.
The examples that I’ve used up to this point have all followed the same format. I create a Connection object, then a Command object, and finally a Recordset object. However, ADO often supports different ways to achieve the same results, allowing you to simplify your code when appropriate. For example, you don’t have to create a Command object. If you refer to Listing 5, you’ll see that, as with the other examples, I first create the Connection object, define the connection string, and open the Connection object. However, I then define the command text and assign it to the ctContacts variable, but I do not create a Command object. If your commands don’t require Parameter objects (which I describe later in the article) or special property settings, you often don’t need to create a Command object. For instance, if you simply want to retrieve a list of contacts from the database and you don’t need to pass any parameter values (such as a contact ID) into your SQL statement, you can simply define the command text and not bother creating a Command object.
After I define the command text, I create the Recordset object. When I open the object, I pass two arguments. The first argument is the command text (the ctContacts variable), and the second argument is the Connection object (the conSqlServer variable). After you open the Recordset object, you can access its properties and methods just as you saw in the previous examples.
You can also create a Recordset object without creating Connection or Command objects. Listing 6 shows an example of how this works. In this script, I first define the connection string and assign it to the csSqlServer variable. Next, I define the command text and assign it to the ctContacts variable. After this, I create the Recordset object and use the object’s Open method, passing the two variables as arguments to the method—first the connection text and then the connection string. As you can see, this approach is even simpler than the previous approach, and it works well if you plan to include only a single recordset. For example, if you want to retrieve a list of contacts only once and you need to connect to the database only once, then you probably don’t need to create a Connection object or Command object. However, if you have multiple recordsets maintaining multiple connections, you should first create the Connection and Command objects.
The Field Object
The examples I’ve shown you so far have all used the Recordset object’s Save method to save the returned data to an .xml file. However, in all likelihood, you’ll want to dig a lot deeper into the data than I’ve done. That’s where the Field object comes in.
As you’ll recall from Figure 1, the Recordset object includes the Fields collection. This collection is made up of one or more Field objects that each represent a field returned by the command text. For example, if your command text returns data from the FirstName, MiddleName, and LastName columns of the Person.Contact table in the AdventureWorks database, your Recordset object will include three Field objects, one for each of those columns.
Listing 7 provides an example of how you can use the Fields collection to access the Field objects. After I create the Recordset object, I create a FileSystemObject object and a text file to hold my data. I then use a Do Until statement to create a loop. I plan to loop through the result set and insert the data into the text file. The Do Until statement first uses the Recordset object’s EOF property to specify when to end the loop. When the loop reaches the end of the result set, it will stop.
Within the loop, I concatenate two Field objects: FirstName and LastName. To retrieve a value for a field, I call the Fields collection (rsContacts.Fields) and specify the name of the first field (FirstName) in parentheses. I follow this with the Value property so I can retrieve the specific value. I do the same thing for the LastName field and concatenate the two values.
After the concatenation, I use the Recordset object’s MoveNext method to retrieve the next row in the result set. As a result, for each row, the first and last name will be concatenated and written to the text file.
This, of course, is a simple example of how to access the Field object, but it does demonstrate the basic concepts behind using Field objects and gives you a better sense of the crucial role that the Recordset object plays in ADO. Now let’s take a look at another important ADO object—Parameter.
The Parameter Object
The Parameter object is similar to the Field object in that it’s part of a collection included in another object. In this case, the Command object contains the Parameters collection, which is made up of one or more Parameter objects. Each Parameter object contains a parameter that you can use for stored procedures and parameterized queries.
Parameters are particularly important when you want to modify data in your data source. Listing 8 shows an example of passing parameter values to a stored procedure that updates a SQL Server table. For the purpose of this example, I created the HumanResources.Vacation table in the AdventureWorks database. Along with the table, I created the UpdateVacationHours stored procedure to update an employee’s vacation hours. I used the following T-SQL statements to create the table and the stored procedure:
SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName, e.VacationHours
INTO HumanResources.Vacation
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
GO
CREATE PROCEDURE UpdateVacationHours (@EmpID INT, @HoursUsed INT) AS
UPDATE HumanResources.Vacation
SET VacationHours = VacationHours - @HoursUsed
WHERE EmployeeID = @EmpID
Returning to Listing 8, you can see that I first create and open a Connection object, and then I create a Command object. After I create the Command object (which I assign to the cmdVacation variable), I set several properties and create the necessary parameters. First, as you saw in earlier examples, I assign the Connection object to the ActiveConnection property. I then define the CommandText property, which in this case is the name of the stored procedure. Next I set the CommandType property to the adCmdStoredProc constant (which equals 4). As the name indicates, the command type is a stored procedure.
The next step is to add Parameter objects to the Parameters collection. To do this, I use the collection’s Append method. The argument that I pass to this method is the Command object’s CreateParameter method. The CreateParameter method takes two arguments: the name of the parameter and the adInteger constant (which equals 3). The constant specifies that the parameter should be an integer. For this example, the first parameter is @EmpID and the second is @HoursUsed.
That’s all you need to do to create the parameters. However, notice that I also assign initial values to them. To do this, I call the Parameters collection, specify the name of the parameter, and assign it a value of 1. This provides a default value for the parameters, and it allows me to easily test the code.
After I define the parameters, I can execute the command. However, because I’m updating records and not returning a result set, I don’t create a Recordset object as I did in earlier examples. Instead, I use the Command object’s Execute method (cmdVacation.Execute) to run the stored procedure. As arguments to the method, I first specify the RowsUpdated variable. This will hold the number of records updated when the stored procedure runs. The second argument, which is blank, can hold a Variant array of parameter values, an alternative method for passing parameters to a command. The third argument is the adExecuteNoRecords constant. Including this argument is important because, by default, the Execute method returns a Recordset object when it runs. However, because you don’t need this object, you should specify this constant to avoid the additional overhead.
When you run the script, it will call the stored procedure and return the number of rows affected by the update. Not surprisingly, numerous approaches are available for modifying data. For example, you can define command text that uses an EXECUTE statement to call the stored procedure, then pass in the parameter values through variables. Or you can define command text that uses an UPDATE statement rather than calling a stored procedure. ADO also lets you use methods in the Recordset object to update field data, which can then be persisted to the database.
A Place to Start
Although the examples I provided barely skim the surface of ADO, they should provide you with a good foundation to begin working with ADO. Once you understand how to implement the basic components of ADO in your script, you can start digging deeper into each object and try out the various ways to access a data source and work with the data. Again, refer to the MSDN Web site as necessary for details about the objects’ properties and methods. And when you’re ready, start playing with other ADO objects and see what you can come up with.