Data Object Technologies | Designing Component-Based Applications

As we saw in Chapter 3, persistent data can be stored in many ways. Each data store typically has a native API for accessing the store. There are also generic APIs such as ODBC for accessing relational databases. There are ODBC drivers available for every major relational database. You can use these APIs to access data within your data objects, but doing so gives rise to one fundamental problem: if you have multiple data stores, especially nonrelational stores, you have to learn multiple APIs to access your data.

The Microsoft technologies that help you deal with any type of persistent data are OLE DB and Microsoft ActiveX Data Objects (ADO). An OLE DB provider implements a specific set of interfaces to provide access to some data store. Clients can use the OLE DB interfaces directly to get data out of the store, but most clients tend to use the more friendly ADO. ADO is the preferred method for accessing persistent data from your data objects.

MTS provides a resource dispenser to handle pools of ODBC database connections. The resource dispenser also automatically enlists connections in transactions, as appropriate. The Microsoft Data Access Components (MDAC) includes an OLE DB provider for ODBC, so ADO can be used to connect to any ODBC data source and we can get the benefits of connection pooling and automatic transactions from MTS (assuming that the data store supports transactions and the ODBC driver meets some minimal requirements).

NOTE
At the time this book was written, the only databases with ODBC drivers that fully support both connection pooling and transactions were Microsoft SQL Server, Oracle, and IBM DB2. Microsoft is working with other database vendors to ensure that ODBC drivers are available for those databases as well. For more information about the databases supported by MTS, refer to the MTS Database and Transactions FAQ. A version of this file is located in the $/FAQ directory on the companion CD. The latest version of this file can be obtained from the Microsoft Web site: http://www.microsoft.com/com/mtsfaq/faq.htm.

Although the resource dispenser/resource manager model supported by MTS and the data access model supported by OLE DB are completely generic, the most common data source is an ODBC database. For the remainder of this chapter, we'll focus on data stored in ODBC databases—in particular, data stored in SQL Server databases. In Chapter 15, we'll look at some other types of data stores when we discuss extending the application beyond COM and MTS.

Programming with ADO

The ADO object model contains the seven objects listed in Table 8-1.

Table 8-1. The objects contained in the ADO object model.

*Object*	*Description*
Connection	Manages a connection to a data source.
Command	Defines a specific command to execute against a data source.
Recordset	Represents a set of records from a data source or the results of an executed command.
Field	Represents a column of data with a common data type. A Recordset object has a Fields collection, with one Field object per column in the Recordset.
Parameter	Represents a parameter associated with a Command object based on a parameterized query or stored procedure. A Command object has a Parameters collection, with one Parameter object per command parameter.
Property	Represents a dynamic characteristic of an ADO object defined by the OLE DB provider. Connection, Command, Recordset, and Field objects have Properties collections, with one Property object per dynamically defined characteristic.
Error	Contains details about data access errors for a single operation. A Connection object has an Errors collection, with one Error object per OLE DB provider error.

Connections

You use a Connection object to set up a connection with a data source. When the object is used with an ODBC data source, you establish a connection by passing either a data source name (DSN), user ID, and password or a DSN filename to the Connection object's Open method.

Data objects should generally access data sources using a fixed identity, rather than using the client's identity. This technique greatly simplifies administration and makes it possible to efficiently pool database connections across multiple client requests. If you need to restrict access to a database, you can restrict access to the business objects your clients interact with or you can restrict access to the data objects themselves.

The most straightforward and flexible way to specify parameters for the Connection object's Open method is to include a DSN filename in your source code and then specify the data source, user ID, and password in the DSN file itself. This technique lets a system administrator modify the data source or account access information without requiring source code changes to your components.

The Connection object is used to specify the type of database access you want. You use the Mode property to indicate whether you want a read-only, write-only, or read/write connection and what type of sharing you'll permit if other attempts are made to connect to the database. You must set this property before opening the connection.

Typically, you open the connection immediately before accessing the database and close the connection as soon as possible, instead of holding onto a connection for the lifetime of an object. This approach is acceptable, even though creating database connections is an extremely expensive operation, because of the connection pooling services of the ODBC 3.0 driver manager. For each connection request, the driver manager first looks in the pool for an acceptable unused connection. If it finds one, that connection is returned. Otherwise, a new connection is created. Connections are disconnected from the database and removed from the pool if they remain idle for a specified period of time (by default, 60 seconds). Currently, the ODBC connection pool size is limited only by free memory and the number of database connections available. The only way to control the pool size is to set the ODBC pooling time-out value based on your estimated connection rate.

You cannot reuse a connection established using a different user identity. For this reason, you should connect to databases using a fixed identity within your data objects. If you use the client's identity, every unique client will require a unique database connection, eliminating a key scalability benefit of the three-tier architecture and MTS. You also cannot reuse connections across process boundaries. As we'll see in Chapter 9, components that access the same data sources should run within the same process so that connections can be reused.

If you examine the methods of the Connection object in the ADO documentation, you'll see the following methods related to transaction processing: BeginTrans, CommitTrans, and RollbackTrans. Components that will be run in the MTS environment should never use these methods. Instead, you should let MTS manage transactions through the ObjectContext and use the ObjectContext SetComplete and SetAbort methods to vote on the transaction outcome.

Accessing data

There are three ways to access data using ADO: the Connection Execute method, Command objects, and Recordset objects. You can use the Connection Execute method to execute a specified command against a data source. When used with an ODBC data source, commands can be SQL statements or nonparameterized stored procedures. Any results are returned as a Recordset object with a read-only, forward-only cursor. We'll discuss cursors in more detail in the section "Recordset Objects" below.

NOTE
Stored procedures can provide a great performance boost, especially for complex data access operations. However, you should use stored procedures only for data access. Business logic should be implemented in business objects, as we'll see in Chapter 9.

You can use Command objects to execute parameterized stored procedures and commands or to save a compiled version of a command that will be executed multiple times. You establish a connection to a data source by setting the Command ActiveConnection property. You specify the command using the CommandText property, and you execute the command using the Execute method. Any results are returned as a Recordset with a read-only, forward-only cursor. If you have a parameterized command, you specify the parameters in the Command object's Parameters collection. To compile a command for speedy reuse, you set the Prepared property.

Finally, you can manipulate data directly using Recordset objects. Creating a Recordset object and using its methods directly is the most flexible way to manipulate data.

Recordset objects

When you use ADO, you'll almost always manipulate data exclusively through Recordset objects. You get either a Recordset as the return value from a Connection or Command Execute call, or you create your own Recordset.

A Recordset object consists of a set of rows and columns. At any given time, a Recordset refers to the set of columns associated with a specific row, called the current row. The individual columns of the Recordset can be accessed through Fields collection. You move through the rows of a Recordset by using the object's associated cursor.

ADO supports the following cursor types:

Dynamic cursor: Enables you to view additions, changes, and deletions made by other users. All types of movement through the Recordset that don't rely on bookmarks are permitted. Bookmarks are supported if the OLE DB provider supports them.
Keyset cursor: Similar to a dynamic cursor, except that you can't see records added by other users and you can't access records deleted by other users. Keyset cursors always support bookmarks.
Static cursor: Provides a static copy of a set of records. You cannot see additions, changes, or deletions made by other users. Static cursors always support bookmarks and thus permit any type of movement through the Recordset.
Forward-only cursor: Similar to a static cursor, but allows you only to scroll forward through the Recordset.

The Recordset features available to you depend on the cursor type specified when the Recordset is opened.

NOTE
Not every OLE DB provider supports every cursor type. When you're using the OLE DB provider for ODBC, the cursor types available to you depend on the types supported by the underlying ODBC driver for your database. The SQL Server ODBC driver supports all four cursor types.

Recordset objects also support a variety of lock types. Whenever records in a Recordset are being updated, a lock must be put on those records. The following LockType property values specify what types of locks are placed on records during editing:

adLockOptimistic: Optimistic locking applies locks record by record only when the Update method is called.
adLockPessimistic: Pessimistic locking applies locks record by record at record-read time.
adLockBatchOptimistic: Batch optimistic locking applies locks to the entire Recordset when the UpdateBatch method is called.
adLockReadOnly: Read-only locking is the default. When this lock type is specified, you cannot update the data in the Recordset.

NOTE
Not all lock types are supported by all OLE DB providers.

The Recordset object provides a set of methods for moving through its rows. The MoveNext and MovePrevious methods move forward and backward through the Recordset, one record at a time. You can use the BOF and EOF properties to detect when you've reached the beginning or end of a recordset. For a Recordset that supports dynamic positioning, MoveFirst and MoveLast are available. (You can guess what those do…) For a Recordset that supports bookmarks, you can use the Bookmark property to return a unique identifier for the current record in the Recordset. At a later time, you can set the Bookmark property to return to that record. Recordset objects also provide methods to move to specific records by ordinal number.

The most common way to populate a Recordset that you create is to attach the Recordset to a Connection using the ActiveConnection property and then call the Recordset Open method. You can also populate a Recordset programmatically, if the data does not come from an OLE DB data source.

Recordset objects are tremendously useful for three-tier applications. Remember that we don't want to share state on the server across method calls. We want to connect to the database, get some data, disconnect, and return all the data to the caller. The way to accomplish this in ADO is through disconnected Recordset objects. Disconnected Recordset objects were originally introduced under the name Advanced Data Connector, but they are now built into ADO 1.5.

Disconnected Recordset objects use optimistic locking and are manipulated on the client using a client-side cursor library. (Here, "client" can mean either the presentation layer or the business layer.) Disconnected batch updates are supported through the UpdateBatch method. Be careful with this, because during the time records are being modified on the client, another client might have updated some of the records. When the batch update is applied, you will get errors for the conflicting updates. In this case, you need to define as part of your component interface how partial updates will be handled—will they generate transaction failures, or will the client need to handle the error?

To create a disconnected Recordset, you set the CursorLocation property on either the Connection or the Recordset to adUseClient before opening the connection, you get the data, and then you release the ActiveConnection. If you want to allow the client to modify the data, you should create the Recordset using batch optimistic locking (adLockBatchOptimistic) and either a static (adOpenStatic) or a keyset (adOpenKeyset) cursor.

Fields

The columns of the current row in a Recordset are accessed using the Fields collection. You can access a field by its name or by a numeric index. When you are using an ODBC data source, the field name corresponds to its name in a SQL SELECT statement and the numeric index is determined by the field's position in the SQL SELECT statement. Once you have a Field object, you can get or set information about it using its properties. The most commonly used property is the Value property, which can be used to retrieve or set the field's data value.

Two special methods are available on the Field object for dealing with long binary or long character data. You use the GetChunk method to retrieve a portion of the data. You use the AppendChunk method to write a portion of the data. You can determine whether you need to use these methods by examining the Attributes property of the Field.

Handling errors

Any ADO operation can generate errors, so it's important to handle those errors within your method calls. This error handling consists of two parts: ADO returns error codes for each method call, and it supports the standard COM error reporting mechanism, IErrorInfo. Specific OLE DB provider errors, such as native database error codes or ODBC error codes, are stored in the Errors collection associated with your Connection object. One ADO call can generate multiple errors in the Errors collection. You can walk through the Errors collection to retrieve rich error information about database failures.

Note that ADO clears the ErrorInfo object before it makes a call that could potentially generate errors. However, the Errors collection is cleared and repopulated only when the OLE DB provider generates a new error or when the Clear method is called. Some methods and properties can generate warning messages in the Errors collection without halting program execution. Before calling these methods or properties, you should clear the Errors collection so that you can read the Count property to determine whether any warnings were generated. Methods that can generate warnings include Recordset Resync, UpdateBatch, and CancelBatch. The Recordset Filter property can also generate warnings. A common situation that generates warnings is when conflicting updates are applied to the database.