SQL Server Magazine

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.

   Prev. page   [1] 2     next page