How to Use ActiveX Data Object

Using ADO to access SQL Server databases

In Microsoft's new strategies for application developers, the words universal, scalable, and enterprise pop up everywhere. These key words correspond to three concepts: Universal refers to the idea that all users should be able to access information, regardless of its form. Scalable refers to an application's ability to provide consistent response times to all its users as the number of users grows. Enterprise means an application's users can connect to the application across a LAN, intranet, extranet, or the Internet.

The universal access strategy, Universal Data Access (UDA), is the most important to SQL Server developers because it provides a set of mechanisms for accessing data in all forms. UDA has broad coverage and includes mechanisms to access flat-file, relational, and object-oriented databases, and hierarchical data storage systems such as Exchange Server and Windows 2000's (Win2K's—formerly Windows NT 5.0's) Active Directory (AD). ActiveX Data Object (ADO) implements the client side of the UDA specification. This article shows you how to use ADO to access SQL Server databases (for more information about ADO, see the sidebar "ADO Resources," page 48).

OLE DB and ADO

Microsoft's Object Linking and Embedding Database (OLE DB) is the specification used to implement UDA. OLE DB acts as a layer between the data provider, which stores and retrieves data, and the data consumer, which processes the data and presents it to the client. (For more information on data providers and consumers, see Ken Miller, "The Ins and Outs of Data Access," page 49.) Because many kinds of data providers have different data storage mechanisms, OLE DB abstracts the details of accessing data into an all-encompassing set of generic methods and properties describing every data source currently known. Now, you can use a single interface to access data from a diverse set of data-storage mechanisms.

However, this level of coverage makes using native OLE DB calls a fairly daunting task for programmers, especially those who use OLE DB only occasionally. To simplify the process, ADO encapsulates OLE DB API calls in objects corresponding with OLE DB functions. Figure 1 shows how ADO relates to OLE DB. Client applications create ADOs, which in turn use native OLE DB commands to communicate with the data providers. The ADO methods and properties you use to access your data remain the same, no matter which data provider you use.

Microsoft has gathered related functions into each ADO. Thus, when you use an ADO, you use only the OLE DB calls you need. For example, in Figure 2 the Connection object is at the top of the ADO hierarchy, because all the other objects use it to send commands to and receive data from the providers. Also, the Errors collection is an array of Error objects corresponding to errors generated by ADO, Open Database Connectivity (ODBC), OLE DB, or SQL Server while ADO is working. For SQL Server, the Command object provides a generic way to execute parameterized queries, stored procedures, and those commands that don't return data. Parameter objects represent the parameters a stored procedure or query defines. Recordset objects manage application access to the data each data source returns. Finally, field objects represent the individual columns in each row.

The Connection Object

The Connection object has two roles within ADO. First, it provides a communication channel between the other objects and the data provider. For SQL Server-based data providers, the Connection object manages the process of finding the server, logging in, opening the database for use, passing Transact SQL (T-SQL) statements to the server, and returning rowsets to the client. Second, the Connection object lets you establish server-specific, connection-based settings. For example, in SQL Server the STATISTICS IO setting is set on a connection basis only. If you need to change a setting affecting only the current session, you set it through a Connection object.

Typically, you use two sets of the Connection object's methods on a regular basis: Open/Close and BeginTrans/CommitTrans/RollbackTrans. In this article, I focus on the Open method. Listing 1 on page 44 shows one way to call the Open method (this code sample shows you how to use only this particular function). If you are familiar with ODBC programming, you will recognize the Connection String syntax.

Listing 1 shows a Connection String without a Data Source Name (DSN) in which I have identified the SQL Server user ID, password, database, server name, and ODBC driver I want to use. The ODBC documentation explains how to create ODBC data sources in detail, but this Connection String will work for SQL Server. You must substitute a valid user ID and password and set the SQL Server entry equal to the correct server name.

Because the Microsoft ODBC Provider for OLE DB (i.e., MSDASQL) is the default provider for ADO, you can use the ODBC Connection String syntax. If you want to use a different provider, you can add the Provider= parameter to the Connection String. ADO automatically adds Provider= MSDASQL for you; therefore, you can port any ODBC Connection String to ADO without change.

SQL Server 7.0 has a native data provider for both SQL Server 7.0 and SQL Server 6.5, which you can use if you want to bypass ODBC. The SQL Server OLE DB Provider (i.e., SQLOLEDB) directly maps the OLE DB API to the SQL Server interface.

The Error Object

When the data provider sends an error message to the client, the Connection object creates an Error object and appends it to the Errors collection (one of the Connection object's properties). Each Error object contains text, a data provider-specific description of the error, the native error number the server sent, the error the data provider sent, and the name of the object causing the error. Errors from SQL Server also have the SQLState number, which either SQL Server or ODBC sets, depending on where the error occurs.

Error objects represent errors the data provider, not ADO, returns. ADO uses the standard error-handling system of the programming environment in use. For example, for Visual Basic (VB), you need to trap errors using the On Error Goto statement pointing to an error-handling routine.

After executing each SQL statement, check the Count property of the Errors collection to see whether any errors occurred. If the Count is greater than 0, ADO intercepted an error message. Remember, SQL Server returns informational messages (severity less than 10) as error messages. VB won't call the error handler for informational messages; therefore, you must check the Error object's properties to differentiate between true errors and informational messages.

The Recordset Object

The Recordset object provides a place to store a local copy of the data. Organized into rows and columns (fields), Recordset objects can hold the current row, a pointer to the current row, or a full copy of the rowset, depending on what kind of cursor you choose. Let's use the default settings to create a forward-only, read-only, Recordset object that uses a server-side cursor. You use a temporary table in the tempdb database to create this type of Recordset. Your program can traverse this Recordset moving from the first record to the last record, but you can make no changes to the data in the Recordset.

Listing 2 shows the syntax for the Recordset object's Open method and sample code from the example program. The source parameter is the command for creating the cursor. For SQL Server, the command can be a SELECT statement, the name of a table, or the name of a stored procedure returning a rowset. I chose a SELECT statement returning the Pub_id rowset and the name of each publisher in the Pubs database.

In the CursorType parameter in Listing 2, CursorType tells ADO what kind of cursor to use for this Recordset. If you need to move randomly through your Recordset, and if you need to see the changes others make to the underlying table, choose a dynamic cursor. Otherwise, choose the default Forward-only cursor. If you are filling a combo box or list box, as I do in the example program, the Forward-only cursor is a good choice because it provides the best performance of all the Recordset types.

The LockType parameter determines what kind of locks ADO puts on the data, when to apply those locks, and how long they stay in place. Programming with ADO can be tricky with locks. Unless you maintain an exclusive lock on the data while the cursor is open, you run the risk of trying to update a row at the same time someone else is updating it. The adLockOptimistic value lets users update this Recordset. If this Recordset were read-only (as in the prior description), you would use the constant adLockReadOnly. Because adLockOptimistic is the most common choice, let's use it for now.

With the ActiveConnection parameter, you can supply a reference to a Connection object or a Connection String such as the one used to create the Connection object in Listing 1. The difference is that if you supply a Connection String, ADO automatically opens a new connection to the data provider. So, if you use this technique to open multiple Recordsets, your application will use multiple connections at any given time. Multiple connections aren't the most efficient way to use SQL Server's limited pool of connections. A better technique is to share a Connection object among all Recordset objects or use the SQLOLEDB provider, which doesn't allow multiple connections.

After you open the Recordset, you can use the MoveFirst, MoveLast, MovePrevious, and MoveNext methods on the object to move through the rows. Of course, if you chose a Forward-only cursor, only the MoveNext method will work. Because the Fields collection is the default property in the Recordset object, Recordset("ColumnName") = value sets the specified column to the value, and variablename = Recordset("ColumnName") sets the variable equal to the value in the named column of the current row.

However, to save changes to the values in the current row's columns, you must execute the Update method. Otherwise, any changes you made will be discarded when you move to the next row. Similarly, if you want to add a row to the Recordset, you must call the AddNew method, which inserts a new row into the Recordset and makes it the current row. When you finish making changes, call the Update method to save them.

Two other Recordset methods also apply: NextRecordset and GetRows. Most people who work with SQL Server recognize that stored procedures and command batches can return multiple rowsets to the client. ADO supports SQL Server returning multiple rowsets to the Recordset object via the NextRecordset method. Calling this method closes the current cursor and tells SQL Server to send the results of the next query. If you have a stored procedure to execute multiple queries, you will find this method particularly useful.

GetRows returns the Recordset's current contents in a dynamic array. You can use the array as an in-memory copy of your data to speed data processing and eliminate locking and storage problems on the server. In general, looping through an array is faster than looping through the rows in a Recordset if you have to visit the rows multiple times.

The Command Object

The Command object differs from the Recordset object in two ways: It doesn't store rows internally, and it can both accept parameterized queries and call stored procedures. You can think of the Command object as a special way of executing SQL statements that are more complex than the Recordset object can handle.

Instead of the Source property, the Command object has a CommandText property. It accepts a standard SQL statement, calls to queries with parameters, and calls to stored procedures. Listing 3 shows how to use a Command object to call the stored procedure FindTitles. The CommandText property contains the name of the stored procedure that already exists on the server, and the adCmdStoredProc value stored in the CommandType property tells ADO the text in CommandText refers to a stored procedure.

After setting the CommandText and CommandType properties, call the Refresh method on the Parameters collection. The Refresh method tells ADO to find the stored procedure on the server, get the list of parameters the stored procedure has defined, create a separate Parameter object for each one, and append the object to the Parameters collection. The subroutine in Listing 3 configures the object but does not call the stored procedure.

Calling the Execute method tells ADO to call the stored procedure on the server. Listing 4 shows the first parameter set to the Pub_id of the currently selected publisher and calls the Execute method. The statements in Listing 3 and Listing 4 are equivalent to the T-SQL statement Execute @RetVal = FindTitles('736'), if we assume 736 is the current publisher ID.

The rsTmp Recordset object holds the rows from the Titles table that have the Pub_id specified in the parameter. In this case, you must use a Command object to retrieve the records because Recordset objects cannot accept queries with parameters or calls to stored procedures. The only real difference between Listing 2 and Listing 4 is that Listing 4 uses the Command object's Execute method on the Command object, and Listing 2 uses the Open method on the Recordset object.

This ability to handle parameters lets you take advantage of the improved performance you gain by calling a stored procedure rather than sending dynamic T-SQL commands to SQL Server. If an application sends a SELECT statement to SQL Server directly, the server has to parse the query, find all the referenced objects, look up the available indexes, work through the optimization algorithm, and create an execution plan for the engine to run. Because a stored procedure is compiled at creation, SQL Server can skip all the early steps and start running the execution plan. For queries running many times and varying only in the WHERE clause values, compiling into stored procedures can greatly improve performance. Prepared SQL offers most of the advantages stored procedures offer.

For example, Listing 5 shows the parameterized SQL Select statement Select pr_info From pub_info where Pub_id = ?. The question mark tells ADO you will change the value of Pub_id before you execute the query, so the Command object will create a Parameter object to hold a place for the value. This approach lets you control which rows SQL Server returns according to a value the application sets.

Exactly what happens under the hood depends on which version of SQL Server you use and on the setting of the Prepare property. If Prepare is False, ADO sends SQL Server the text in the CommandText property after it replaces the question mark with the value in the Parameter object. If Prepare is True, you have different scenarios for SQL Server 4.21a, SQL Server 6.5, and SQL Server 7.0.

For SQL Server 6.5, MSADSQL and SQLOLEDB use the text from the CommandText property to create a temporary stored procedure in the tempDB database. When you call the Execute method, the driver executes the temporary stored procedure with the values from the Parameter objects, if any. Although you can control how long the temporary stored procedure remains in tempDB, the default is to delete it when the connection drops.

Because SQL Server 4.21a doesn't support creating temporary stored procedures, the MSDASQL provider creates permanent stored procedures in the user database instead of tempDB. Otherwise, the process runs the same as SQL Server 6.5's process runs. However, SQL Server 4.21a won't delete the stored procedures when the connection drops because they are permanent. Thus, a buildup of orphaned stored procedures can occur if your application unexpectedly loses connection.

SQL Server 7.0 bypasses creating stored procedures. MSDASQL and SQLOLEDB tell SQL Server 7.0 to compile the text in the CommandText property and store the execution plan in the procedure cache. ADO then jumps directly into the execution plan after it substitutes the parameters. From that point on, SQL Server must recompile the command only if the execution plan is flushed from the procedure cache.

A Simple Beginning

If you have simple data-access needs, this article will bring you up to speed quickly. The sample code contains additional comments to help you understand how to put the pieces together. ADO's capabilities extend far beyond this introduction.