Disconnected Recordsets

Disconnected Recordsets

This knowledge of batch updates allows you to take advantage of the next ADO feature: disconnected recordsets. A disconnected recordset is a recordset that has been disconnected from its connection. This feature is impressive because the user cannot tell the difference between a regular recordset and a disconnected one; their feature sets and behavior are almost identical. To disconnect a recordset from its connection, you must set the CursorLocation to clUseClient and the LockType to ltBatchOptimistic. You then tell the dataset that it no longer has a connection:

ADODataSet1.Connection := nil;

Hereafter, the recordset will continue to contain the same data, support the same navigational features, and allow records to be added, edited, and deleted. The only relevant difference is that you cannot update the batch because you need to be connected to the server to update the server. You can reconnect the connection (and use UpdateBatch) as follows:

ADODataSet1.Connection := ADOConnection1;

This feature is also available to the BDE and other database technologies by switching over to ClientDataSets, but the beauty of the ADO solution is that you can build your entire application using dbGo dataset components and be unaware of disconnected recordsets. When you discover this feature and want to take advantage of it, you can continue to use the same components you always used.

You might want to disconnect your recordsets for two reasons:

  • To keep the total number of connections lower

  • To create a briefcase application

I'll discuss keeping down the number of connections in this section and return to briefcaseapplications later.

Most regular client/server business applications open tables and maintain a permanent connection to their database while the table is open. However, there are usually only two reasons to be connected to the database: to retrieve data and to update data. Suppose you change your regular client/server application so that after the table is opened and the data is retrieved, the dataset is disconnected from the connection and the connection is dropped; your user will be none the wiser, and the application will not need to maintain an open database connection. The following code shows the two steps:

ADODataSet1.Connection := nil;
ADOConnection1.Connected := False;

The only other point at which a connection is required is when the batch of updates needs to be applied. The update code looks like this:

ADOConnection1.Connected := True;
ADODataSet1.Connection := ADOConnection1;
  ADODataSet1.Connection := nil;
  ADOConnection1.Connected := False;

If you followed this approach throughout the application, the average number of open connections at any one time would be minimal—the connections would be open only for the brief time they were required. The consequence of this change is scalability; the application can cope with significantly more simultaneous users than an application that maintains an open connection. The downside is that reopening the connection can be a lengthy process on some (but not all) database engines, so the application will be slower to update the batch.

Connection Pooling

All this talk about dropping and reopening connections brings us to the subject of connection pooling. Connection pooling—not to be confused with session pooling—allows connections to a database to be reused once you have finished with them. This process happens automatically; if your OLE DB provider supports it and it is enabled, no action is necessary for you to take advantage of connection pooling.

There is a single reason to pool your connections: performance. The problem with database connections is that it can take time to establish a connection. In a desktop database such as Access, this time is typically brief. However, in a client/server database such as Oracle used on a network, this time could be measured in seconds. It makes sense to promote the reuse of such an expensive (in performance terms) resource.

With ADO connection pooling enabled, ADO Connection objects are placed in a pool when the application "destroys" them. Subsequent attempts to create an ADO connection will automatically search the connection pool for a connection with the same connection string. If a suitable connection is found, it is reused; otherwise, a new connection is created. The connections themselves stay in the pool until they are reused, the application closes, or they time out. By default, connections will time out after 60 seconds, but from MDAC 2.5 onward you can set this time-out period using the HKEY_CLASSES_ROOT\CLSID\ <ProviderCLSID>\SPTimeout Registry key. The connection pooling process occurs seamlessly, without the intervention or knowledge of the developer. This process is similar to the BDE database pooling under Microsoft Transaction Server (MTS) and COM+, with the important exception that ADO performs its own connection pooling without the aid of MTS or COM+.

By default, connection pooling is enabled on all MDAC OLE DB providers for relational databases (including SQL Server and Oracle), with the notable exception of the Jet OLE DB provider. If you use ODBC, you should choose between ODBC connection pooling and ADO connection pooling, but you should not use both. From MDAC 2.1 on, ADO connection pooling is enabled and ODBC is disabled.


Connection pooling does not occur on Windows 95 regardless of the OLE DB provider.

To be comfortable with connection pooling, you need to see the connections being pooled and timed out. Unfortunately, no adequate ADO connection pool spying tools are available at the time of this writing; but you can use SQL Server's Performance Monitor, which can accurately spy on SQL Server database connections.

You can enable or disable connection pooling either in the Registry or in the connection string. The key in the Registry is OLEDB_SERVICES, which can be found at HKEY_CLASSES_ROOT\CLSID\<ProviderCLSID>. It is a bit mask that allows you to disable several OLE DB services, including connection pooling, transaction enlistment, and the cursor engine. To disable connection pooling using the connection string, include ";OLE DB Services=-2" at the end of the connection string. To enable connection pooling for the Jet OLE DB provider, you can include ";OLE DB Services=-1" at the end of the connection string, which enables all OLE DB services.

Persistent Recordsets

The persistent recordset is a useful feature that contributes to the briefcase model (discussed in the next section). Persistent recordsets allow you to save the contents of any recordset to a local file, which can be loaded later. In addition to aiding with the briefcase model, this feature allows developers to create true single-tier applications—you can deploy a database application without having to deploy a database. This makes for a very small footprint on your client's machine.

You can "persist" your datasets using the SaveToFile method:


This method saves the data and its delta in a file on your hard disk. You can reload this file using the LoadFromFile method, which accepts a single parameter indicating the file to load. The format of the file is Advanced Data Table Gram (ADTG), which is a proprietary Microsoft format. It does, however, have the advantage of being very efficient. If you prefer, you can save the file as XML by passing a second parameter to SaveToFile:

ADODataSet1.SaveToFile('Local.XML', pfXML);

However, ADO does not have a built-in XML parser (as the ClientDataSet does), so it must use the MSXML parser. Your user must either install Internet Explorer 5 or later or download the MSXML parser from the Microsoft website.

If you intend to persist your files locally in XML format, be aware of a few disadvantages:

  • Saving and loading XML files is slower than saving and loading ADTG files.

  • ADO's XML files (and XML files in general) are significantly larger than their ADTG counterparts (XML files are typically twice as large as their ADTG counterparts).

  • ADO's XML format is specific to Microsoft, like most companies' XML implementations. This means the XML generated in ADO is not readable by the ClientDataSet and vice versa. Fortunately this problem can be overcome using Delphi's XMLTransform component, which can be used to translate between different XML structures.

If you intend to use these features solely for single-tier applications and not as part of the briefcase model, then you can use an ADODataSet component and set its CommandType to cmdFile and its CommandText to the name of the file. Doing so will save you the effort of calling LoadFromFile manually. However, you will still have to call SaveToFile. In a briefcase application this approach is too limiting, because the dataset can be used in two different modes.

The Briefcase Model

Using this knowledge of batch updates, disconnected recordsets, and persistent recordsets, you can take advantage of the briefcase model. The idea behind the briefcase model is that your users want to be able to use your application while they are on the road—they want to take the same application they use on their office desktops and use it on their laptops at client sites. Traditionally, the problem with this scenario is that when your users are at client sites, they are not connected to the database server, because the database server is running on the network back at their office. Consequently, there is no data on the laptop (and the data cannot be updated anyway).

This is where your newfound understanding comes in handy. Assume the application has been written; the user has requested a new briefcase enhancement, and you have to retrofit it into your existing application. You need to add a new option for your users to allow them to prepare the briefcase application by executing SaveToFile for every table in the database. The result is a collection of ADTG or XML files that mirror the contents of the database. These files are then copied to the laptop, where a copy of the application has previously been installed.

The application needs to be sensitive to whether it is running locally or connected to the network. You can determine this by attempting to connect to the database and seeing whether the connection fails, by detecting the presence of a local briefcase file, or by creating a flag of your own design. If the application is running in briefcase mode, then it needs to use LoadFromFile for each table instead of setting Connected to True for the ADOConnections and Active to True for the ADO datasets. Thereafter, the briefcase application needs to use SaveToFile instead of UpdateBatch whenever data is saved. When the user returns to the office, they need to follow an update process that loads each table from its local file, connects the dataset to the database, and applies the changes using UpdateBatch.


To see a complete implementation of the briefcase model, refer to the BatchUpdates example mentioned earlier.

Part I: Foundations