Out of the Order Came Chaos

[Previous] [Next]

With DAO and RDO, it's fairly simple to explain to a programmer new to the object model how to connect to and query the desired database. In each case, you use the object model to create an object that represents a connection to your database. Then you use that object to retrieve the results of your query into another object.

As I explained earlier, you can follow the same pattern with the ADO object model. However, you can also open an ADO Recordset to retrieve the results of your query in a single call without using either a Connection object or a Command object. This flexibility makes the process of determining the "right" way to get your data more complicated.

There are times when you won't want to use all of the objects in the ADO hierarchy. For example, perhaps you have no intention of executing a query more than once, so you don't have any need to use the Command object. Or maybe your application will require only one Recordset object, and you don't plan to use the Connection object for any reason other than to retrieve the results of that one query. The following sections discuss these and other examples in detail.

Obtaining a Recordset Without a Command Object

If you are not going to execute your query more than once, or if you won't need Parameter objects, you probably don't need to use a Command object. As was shown in Figure 2-1, you can use a Recordset object with a Connection object without having to use a Command object. Here's an example:

 strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _ "Initial Catalog=Northwind;" & _ "User ID=MyUserID;Password=MyPassword;" Set cnNorthwind = New ADODB.Connection cnNorthwind.Open strConn strSQL = "SELECT * FROM Customers" Set rsCustomers = New ADODB.Recordset rsCustomers.Open strSQL, cnNorthwind 

Connection Object: To Use or Not to Use

You might find that sometimes the only reason you use your Connection object is to open Recordset objects. Who would want to write the code shown in the previous section when you could use code that looks like this instead:

 strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _ "Initial Catalog=Northwind;" & _ "User ID=MyUserID;Password=MyPassword;" strSQL = "SELECT * FROM Customers" Set rsCustomers = New ADODB.Recordset rsCustomers.Open strSQL, strConn 

In this code snippet, we are opening the Recordset object without explicitly using a Connection object. Instead, we're passing a connection string, strConn, into the ActiveConnection parameter (the second parameter) on the Open method. This parameter (and the ActiveConnection property on the Recordset object) accepts a connection string or a Connection object. If a connection string is supplied instead of a Connection object, ADO will establish a connection to the database based on that connection string.

There is absolutely nothing wrong with the syntax of either example; each has its place. But proceed with caution! Before you decide that you'll always use the latter syntax, here's something to consider: do you want the ActiveConnection property on each of your Recordset objects to refer to the same Connection object?

If you choose to use the process demonstrated in the second snippet, you could wind up with code similar to the following:

 Set rsCustomers = New ADODB.Recordset rsCustomers.Open "SELECT * FROM Customers", strConn Set rsOrders = New ADODB.Recordset rsOrders.Open "SELECT * FROM Orders", strConn 

Although each Recordset object was opened using the same connection string to communicate with the same database, you've created a separate Connection object for each Recordset. Each of the resulting Connection objects maintains its own physical connection to the database. For most database systems, physical connections are a precious resource and should not be consumed frivolously.

You can avoid generating additional Connection objects in two ways (or derivatives thereof). Your first alternative is to explicitly create a Connection object and use it with both Recordset objects:

 Set cnNorthwind = New ADODB.Connection cnNorthwind.Open strConn Set rsCustomers = New ADODB.Recordset rsCustomers.Open "SELECT * FROM Customers", cnNorthwind Set rsOrders = New ADODB.Recordset rsOrders.Open "SELECT * FROM Orders", cnNorthwind 

The other method to ensure that you use only one Connection object, but without having to explicitly create one, is to utilize the ActiveConnection property on the initial Recordset object as shown in the following code:

 Set rsCustomers = New ADODB.Recordset rsCustomers.Open "SELECT * FROM Customers", strConn Set rsOrders = New ADODB.Recordset rsOrders.Open "SELECT * FROM Orders", _ rsCustomers.ActiveConnection 

Here we simply passed the ActiveConnection property from the initial Recordset, rsCustomers, into the ActiveConnection parameter on the Open method for the second Recordset, rsOrders.

Using the same Connection object for each of your Recordsets (even if you're not explicitly declaring a Connection object) will improve the performance of your queries and help control the number of actual connections you're making to the database. We'll discuss the reasons why one Connection object can generate multiple connections to your database in greater depth in Chapter 3.

Obtaining a Recordset Without a Database

Toward the end of the Bronze Age, Microsoft developed the ODBC cursor library. (OK, maybe the ODBC cursor library isn't that old, but given how quickly new technology is being released it seems like it might require carbon dating to determine when it was created.) Applications could use this library to store the results of a query while having to store only a small amount of the data within the application itself. Some saw this event as the beginning of database programming with rapid application development (RAD) tools and components. Programmers who loathe the idea of relying on anyone else's code believed it marked the beginning of the fall of civilization as programmers know it.

RDO 2.0 added its own cursor library. Microsoft Visual Basic 5 users could retrieve the results of a query into this library and actually disconnect from the database while still working with the query results. In fact, those users could modify the data and later reconnect to their databases to update them with that batch of changes.

ADO carries that concept of a disconnected recordset further. Early in ADO's lifetime, the Advanced Data Connector, a technology that was later renamed Remote Data Services (RDS), provided early versions of ADO with a cursor engine similar to RDO 2.0's client batch cursor library. This technology allowed ADO to pass a recordset across process boundaries. Rather than passing just a pointer to the object that contained the data, you could pass the data itself across the process boundaries. Beginning with ADO 2.0, recordsets can be persisted to a file and reopened later. You can also create your own recordset by taking a Recordset object variable and populating the Fields collection yourself.

Disconnecting a Recordset from a Connection

Prior to version 3.5 of DAO and version 2.0 of RDO, you couldn't work with a recordset without a live connection to your database. This restriction forced many programmers to maintain a live connection to the database for the lifetime of the application in order to continue to work with DAO and RDO objects, regardless of how infrequently that connection was used.

The other option was to retrieve the data from the DAO or RDO object and maintain that data within the application in some other fashion (Variant arrays, arrays of user-defined types, collections, and so forth). While this method of programming required a connection to the database only when necessary, it could require the developer to write a great deal more code than if she used DAO or RDO and maintained the database connection.

RDO 2.0 introduced the notion of disconnected recordsets. The operating principle here is that you should be able to open a connection to your database, retrieve data, close the connection, work with the data, and reopen the connection when you want to communicate with your database again.

You can use that same functionality in ADO 2.0 and later with client-side recordsets by setting the ActiveConnection property of the Recordset object to Nothing, as in the following example:

 'Open a connection to your database. Set cnDatabase = New ADODB.Connection cnDatabase.CursorLocation = adUseClient cnDatabase.Open strConn, strUserID, strPassword 'Make the query and retrieve the results. Set rsData = New ADODB.Recordset rsData.Open strSQL, cnDatabase, adOpenStatic, _ adLockBatchOptimistic, adCmdText Set rsData.ActiveConnection = Nothing 'Close your connection. cnDatabase.Close 'Modify your recordset.  'Reopen your connection. cnDatabase.Open 'Point your recordset at your connection. Set rsData.ActiveConnection = cnDatabase 'Submit the changes from the recordset to your database. rsData.UpdateBatch 

Figure 2-2 provides a summary of what's happening. Your application sends ADO a request for data. ADO asks the OLE DB provider or ODBC driver to establish a connection to the database. ADO then passes the query string to the provider/driver, the provider/driver retrieves the results from the database and passes them back to ADO, and ADO stores them in the ADO Cursor Engine. You can then close your connection. Using the ADO Cursor Engine's batch updating feature, you can modify the recordset and cache the changes until you want to submit them to the database.

Figure 2-2 The ADO Cursor Engine and disconnected recordsets.

Normally, when you call the Close method on the Connection object, all Recordset objects associated with that Connection object are implicitly closed as well. By setting the ActiveConnection property on the Recordset object to Nothing prior to closing the Connection object, you're asking the ADO Cursor Engine to dissociate the Recordset object from the Connection object.

After you've made all the desired changes to the Recordset object, reconnect to the database by calling the Open method of the Connection object, associate the Recordset with the Connection object, and submit the changes in the Recordset to the database by calling the UpdateBatch method on the Recordset.

Passing a Recordset Out of Process

The way in which Visual Basic handles objects might seem somewhat magical to developers who don't have a great deal of experience with COM. Visual Basic hides the complexities of COM from the developer. (I strongly recommend the books Understanding ActiveX and OLE, by David Chappell [Microsoft Press, 1996], and Dale Rogerson's Inside COM [Microsoft Press, 1996], to help you develop an understanding of working with COM objects across process boundaries.) For the benefit of the inexperienced, I'll attempt to explain the passing of COM objects across process boundaries without undue complexity.

You can create a function in Visual Basic that will return an object such as rdoResultset. If you call such a function locally, everything will behave exactly as you expect, just as if you'd returned simpler data such as a string or an integer. So long as you're using in-process libraries, your code will compile and run successfully. However, if your function passes the rdoResultset across process boundaries (such as from a Component Services component or from an ActiveX EXE), you're actually passing a pointer to the object; the object itself still resides on the server. (See Figure 2-3.) (Component Services was previously known as Microsoft Transaction Server, or MTS.) The reason only a pointer is passed has to do with the default functionality of COM. Maintaining data in your Component Services or MTS objects in this fashion is strongly discouraged. These server technologies are designed to handle objects that maintain no state from the calling component. Returning a pointer to a COM object that resides in your Component Services or MTS business object requires maintaining state; this can lead to unexpected problems and generally results in poor performance and limitations on your ability to scale your application.

While there is code built into COM that passes simple data structures such as strings, integers, and even Variant arrays from one process to another, COM does not know how to pass complete objects. By default, COM will instead pass a pointer to the actual object. As a result, developers who want to build n-tiered applications using DAO or RDO make use of the GetRows function on the DAO Recordset and RDO rdoResultset objects. This function returns a Variant array with the results of the query rather than an object.

While code that passes Variant arrays instead of objects runs quickly and allows you to build stateless server components, you lose the ability to use many of the RAD features of DAO and RDO. For instance, you can't use bound controls such as text boxes, grids, list boxes, or combo boxes. (Although, some developers will tell you that not being able to use bound controls is a blessing in disguise.) You have to write code that interprets the contents of the Variant array, manages that data within your client application, passes that data back to your server component, interprets that information in your server component, and updates your database.

Keep in mind that passing a pointer to an object rather than passing the object itself is the default behavior for passing COM object information across process boundaries. While it's easy to understand how to pass an integer into another application, passing a COM object is more complicated. Let's look at the rdoResultset object again as an example. The COM libraries don't automatically know what information RDO needs in order to take the information stored in the rdoResultset object on the server and create an rdoResultset on the client. If you're building your own COM object in C++, you can build custom marshaling libraries to pass the required information from the server to the client. Essentially, you're telling COM that rather than having COM pass a pointer to your object across process boundaries, you want to handle the marshaling process yourself. ADO saves you the trouble of having to build these libraries by providing a custom marshaling routine for Recordset objects. This routine is illustrated in Figure 2-4.

Figure 2-3 Passing a pointer to an rdoResultset across a process boundary.

Figure 2-4 Marshaling an ADO Recordset object across a process boundary.

When you pass a Recordset object across process boundaries, ADO is loaded in both processes. All of the metadata for the Recordset—such as information about the Fields collection, including data types, which Field objects are updatable, and which Field objects are part of the primary key—as well as the contents of the Recordset are passed across the process boundary. The client application has its very own Recordset object, rather than a pointer to a Recordset object that resides on the server. With ADO, you can pass a Recordset object to another process on the same computer, to a process on another computer inside your network through Distributed COM (DCOM), or across the Internet through RDS and HTTP. If you're using ADO's batch updating feature, the client application can make changes to the Recordset that you can later submit to your database by passing the Recordset back to a server.

NOTE

---

Before you pass judgment too quickly and decide the preceding steps are too much work to go through, note that this approach allows you to have almost complete control of how the server and client manage the data in the application. If you want your client application to update a row in the table based on a timestamp by retrieving that timestamp value and merging it into the data already in the client application, you can write code to do just that. Time or budget constraints might impose the only limits to the functionality you can add to your application.

Persistence Can Pay Off

A salesman is going to call on a customer and needs to be able to generate order information, but since he's on the road, he won't have access to the database server once he leaves the office. It would be helpful if he could download customer, order, and product information and persist that data into a file or files on his laptop before he leaves. That way he could generate new orders while disconnected from the network and submit those orders when he returns.

In the past, you would have had to use a desktop database such as Access to maintain that data. ADO 2.0 introduced functionality that makes it possible to store database information on the desktop without having a desktop database. We discussed earlier how ADO can pass a Recordset object from one process to another. As you can see in the following code, the ADO Recordset object also has a Save method (as of ADO 2.0), which essentially takes the same data that ADO would normally pass across the process boundary from the client to the server and writes it to a file instead. You can later turn that file back into a Recordset object.

 'Retrieve the results of your query ' and save them to a file. The path to the file ' and the filename are stored in the string ' strPathToOrdersFile. Set cnDatabase = New ADODB.Connection  Set rsOrders = New ADODB.Recordset rsOrders.Open strSQL, cnDatabase, adOpenStatic, _ adLockBatchOptimistic, adCmdText rsOrders.Save strPathToOrdersFile 'Retrieve the contents of the file into a Recordset, ' modify the Recordset accordingly, and then save the ' changes back to a file. rsOrders.Open strPathToOrdersFile, , , _ adLockBatchOptimistic, adCmdFile rsOrders.AddNew  rsOrders.Update rsOrders.Save strPathToOrdersFile 'Retrieve the contents of the file into a Recordset ' and submit the changes to your database. rsOrders.Open strPathToOrdersFile, , , _ adLockBatchOptimistic, adCmdFile Set rsOrders.ActiveConnection = cnDatabase rsOrders.UpdateBatch 

Creating Your Own Recordset

Maybe you don't want to use a database at all, but you like using Recordset objects to store data in your application. You can then allow the user to interact with that data by means of a bound control such as a data grid.

ADO 2.0 introduced an Append method to the Fields collection for this very reason. You use this method to supply the structure for each field in your Recordset. Once you've provided this information about the fields, you call the Open method and start adding, editing, and deleting data, as shown in the following code snippet. The Recordset isn't actually updated until the Update method has been called.

 'Retrieve the results of your query ' and store them in a file. Set rsData = New ADODB.Recordset rsData.Fields.Append "ID", adInteger, , adFldKeyColumn rsData.Fields.Append "Description", adVarChar, 40 rsData.Open LockType:=adLockBatchOptimistic rsData.AddNew rsData.Fields("ID").Value = 1 rsData.Fields("Description").Value = "First Record" rsData.Update