Cursor Service for OLE DB

Based on technology in Microsoft® Visual FoxPro®, query results are retrieved and stored in a cursor in memory on the client. Changes are made to a copy of the data stored in a second cursor. All changes are batched and sent to the server in one trip, reducing network traffic and improving performance.

The Cursor Service provides several functions, which are described in the following sections.

Data Fetching

When the consumer requests functionality that requires the Cursor Service, the Cursor Service component is invoked to fetch the data and cache it locally. The data is stored in memory buffers and, if needed, written to temporary files on disk. The data fetch can be performed synchronously or asynchronously. The fetch of large data fields, such as BLOBs, can be delayed until the data is actually required by the application.

Data Manipulation

You can access a rowset by using either ADO or OLE DB interfaces. Then, for example, you can use the ADO Recordset object to maintain currency and manipulate the data. The Recordset exposes methods implemented by the Cursor Service to position on a row, get field values, and filter and sort the rows based on one or more fields. A Recordset can be cloned, which produces a second Recordset based on the same underlying rowset data. Clones have their own currency, filter, and sort order.

The Cursor Service also generates events exposed by the associated rowset and used to bind rowset data to visual controls. Rowsets can be persisted in an internal binary format or in XML. A client rowset can be remoted between two processes or machines over DCOM or HTTP, enabling distributed data access for multitier architectures over intranets and the Internet.

Local Updates

You can make any changes to the local data—such as modifying data fields, deleting existing rows, and inserting new rows into the rowset. In ADO, for example, the changes are immediately committed to the cache and are visible from all the cloned Recordset objects on that rowset. Local changes can be committed to the data provider row by row or once for the entire rowset via the synchronization service.

Local Rowsets

A developer often needs a place to temporarily store some data, or for data binding in a user interface, a developer might want certain data to act as if it came from a server. By first defining the column information associated with the rowset and then opening it, the Cursor Service allows you to create a stand-alone rowset. Local rowsets can be used as data buffers or to hold and share local temporary data in an application. Indexes can be created on local rowsets to support sort, filter, and find operations. All data-manipulation operations are available on local rowsets.

The Cursor Service enables the developer to build an empty recordset by specifying column information and calling Open. The developer now has an empty recordset to work with, as demonstrated by the following ADO code example:

Dim Rst As New ADODB.Recordset
Rst.CursorLocation = adUseClient

'Add some fields
Rst.Fields.Append "dbkey", adInteger
Rst.Fields.Append "field1", adVarChar, 40, adFldIsNullable
Rst.Fields.Append "field2", adDate

'Create the recordset
Rst.Open , , adOpenStatic, adLockBatchOptimistic

'Add some rows
Rst.AddNew Array("dbkey", "field1", "field2"), _
   Array(1, "string1", Date)
Rst.AddNew Array("dbkey", "field1", "field2"), _
   Array(2, "string2", #1/6/1992#)

'Look at the values--a value of 1 for status column = newly added record
Rst.MoveFirst
Debug.Print "Status", "dbkey", "field1", "field2"
While Rst.EOF <> True
   Debug.Print Rst.Status, Rst!dbkey, Rst!field1, Rst!field2
   Rst.MoveNext
Wend

'Committing the rows without ActiveConnection set resets the status bits
Rst.UpdateBatch adAffectAll

'Change the first of the two rows
Rst.MoveFirst
Rst!field1 = "changed"

'Now look at the status: First row shows 2 (modified row),
'second shows 8 (no modifications).
'Also note that the OriginalValue property shows the value 
'before the modification.
Rst.MoveFirst
While Rst.EOF <> True
   Debug.Print
   Debug.Print Rst.Status, Rst!dbkey, Rst!field1, Rst!field2
   Debug.Print , Rst!dbkey.OriginalValue, _
      Rst!field1.OriginalValue, Rst!field2.OriginalValue
   Rst.MoveNext
Wend

One useful feature of a creatable recordset is that pending operations can be "committed" to the recordset. Anytime UpdateBatch is called on a client cursor that has no ActiveConnection set, the changes in the affected row (controlled by the AffectedRows parameter) will be committed to the buffer and the Status flags will be reset. The same applies to CancelBatch, except that the changes in the buffer will be reverted and the flag will be reset.

An additional scenario is to use the fabricated recordset as the basis for an OLE DB provider of sorts. For example, in ADO, an event called EndOfRecordset has been defined to notify the developer that the consumer has reached the end of the result set. The developer then has the option to add new rows within the event. To the consuming application, it appears that the rows have been there all along. The following example demonstrates this feature:

Dim WithEvents rst As ADODB.Recordset
Sub form_load()

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseClient

   rst.Fields.Append "dbkey", adInteger
   rst.Fields.Append "field1", adVarChar, 40, adFldIsNullable
   rst.Fields.Append "field2", adDate

   rst.Open , , adOpenStatic, adLockBatchOptimistic

   'Add some rows
   rst.AddNew Array("dbkey", "field1", "field2"), _
      Array(1, "string1", Date)
   rst.AddNew Array("dbkey", "field1", "field2"), _
      Array(2, "string2", #1/6/1992#)

   Debug.Print "Status", "dbkey", "field1", "field2"
   While rst.EOF <> True
      Debug.Print rst.Status, rst!dbkey, rst!field1, rst!field2
      rst.MoveNext
   Wend

End Sub

Private Sub rst_EndOfRecordset(fMoreData As Boolean, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

   If pRecordset.RecordCount < 50 Then
      StartIndex = pRecordset.RecordCount
      Debug.Print "Adding Some New Data"
      For ii = 1 To 10
         pRecordset.AddNew Array("dbkey", "field1", "field2"), _
            Array(StartIndex + ii, "string1", Date)
      Next
   fMoreData = True
   End If

End Sub

Independently Creatable Objects

All objects in ADO can be instantiated on their own, except the Error and Field objects. To allow greater flexibility in reusing objects in different contexts, the hierarchy of objects found in previous models like DAO and RDO is de-emphasized in the ADO model. For example, you can create a Command object, associate and execute it against one connection, and then associate it with a different connection and reexecute it. This approach also paves the way for you to create specialized objects (defined at design time) and temporary, unattached recordsets.

Extended Result Set Processing

OLE DB allows you to find a record within a rowset according to a column value. This provides the ability to support simple find operations and list box support. In addition, the specification defines a concept of views that, where appropriate, enable simple providers that do not support textual commands to expose simple filtering and sorting mechanisms. For command-based providers, applying a view to an existing rowset allows consumers to do postprocessing of results obtained from a query. The consumer does not have to reexecute the query and make an additional round-trip to the server to re-retrieve a subset of the data already obtained.

Finding, Sorting, and Filtering on Recordsets

ADO fully supports finding, sorting, and filtering on recordsets. Where the OLE DB provider supports these extensions, ADO uses the provider to achieve optimal performance for these operations. Where not supported by the provider, common services under ADO provide these extensions to ensure that the ADO functionality works when accessing data from any OLE DB provider.

Some operations that are easy for a desktop database application to perform are difficult (or, at the very least, exhibit poor performance characteristics) on a database server. ADO exposes a Find method and a Sort property. With these features, the user can do desktop-style data manipulation against any data source from within ADO.

Find and Sort are implemented to use support from the provider. If the provider does not support the proper OLE DB interface for Find, ADO attempts to carry out this operation at the client by brute force. (This brute-force operation for Find can be slow, but there is another alternative available, as discussed below.) If the provider does not support the proper OLE DB interfaces for sorting, setting the Sort property generates an error.

If client cursors are being used, the sort, find, and filter operations can be optimized in three ways:

The client cursor supports the OLE DB interfaces needed for both sorting and finding.
Because the data has already been downloaded to the client cursor, it requires few round-trips to the server (after the initial overhead of fetching the results).
The client cursor builds indexes on sorted columns that are used to optimize Find and Filter operations.

Sorting a Recordset

To sort a recordset, the developer sets the Recordset.Sort property to the name of the column that the recordset should be sorted by, as shown in the following code:

Sub main()

   Dim conn As New ADODB.Connection
   Dim Rst As New ADODB.Recordset

   conn.ConnectionString = _
   "Provider=SQLOLEDB;Data Source=sql70server;" _
      & "User ID=sa;Password='';Initial Catalog=pubs"
   conn.Open

   Set rst.ActiveConnection = conn
   rst.CursorLocation = adUseClient

   rst.Open "select * from authors", , adOpenStatic, _
      adLockBatchOptimistic

   'Print out the records in their original order
   While rst.EOF <> True
      Debug.Print rst!au_id, rst!au_lname, rst!au_fname
      Rst.MoveNext
   Wend

   'Print out the records ordered by author last name
   rst.Sort = "au_lname"

   'Note: Sort automatically moves you to the first row in the 
   'result set so a MoveFirst is not needed.
   While rst.EOF <> True
      Debug.print rst!au_id, rst!au_lname, rst!au_fname
      Rst.MoveNext
   Wend

End Sub

Sort can also be used to sort by multiple columns. To sort by last name and then by first name in the preceding example, the command is as follows:

   'Print out the records ordered by author last name
   'and then by first name
   rst.Sort = "au_lname, au_fname"

Finally, it can also be useful to sort one column in ascending direction and another in descending. To do this you specify DESC following the name to be sorted in descending direction, as follows:

   'Print out the records ordered by author last name and then by 
   'first name, with the first name sorted in descending order
   rst.Sort = "au_lname, au_fname DESC"

Finding a Value in a Recordset

Even after narrowing the results returned from the server by using the SQL command, it is often interesting to search for a particular row in the recordset. ADO has implemented a Find method that will search a named row for a specified value.

In the Microsoft® Visual Basic® object browser, the Find method appears as follows:

Find(Criteria As String, [SkipRecords As Long], [SearchDirection As SearchDirectionEnum = adSearchForward], [Start])

This may seem complex for just finding a record in a recordset. However, here is a description of what each parameter to the method means:

Criteria This is where the actual Find expression is specified. The syntax is fieldname operator value. The fieldname value must be the name of a field, as found in the ADO Fields collection.
SkipRecords By using this optional parameter, you can specify whether to offset your search by a particular row. By default, Find searches always include the current row. A value of 1 for this parameter starts the search with the next row (in the direction you are searching). A value of –1 starts the search with the previous row.
Note You can use values other than –1 and 1 to indicate other offsets.
SearchDirection By using this parameter, you specify whether to search forward or backward in the recordset.
Start In ADO, you can optionally specify a bookmark from which to start searching. If you specify a bookmark, the SearchDirection and SkipRecords parameters are applied from the bookmark.

If you understand how to use these parameters, you can get exactly the behavior you want from Find.

Note Find works only with a single column. It does not support searching multiple columns. If Find does not find a match, it positions you at either end-of-file (EOF), if you were searching forward, or beginning-of-file (BOF), if you were searching backward.

Sub main()

   Dim conn As New ADODB.Connection
   Dim Rst As New ADODB.Recordset

   conn.ConnectionString = _
   "Provider=SQLOLEDB;Data Source=sql70server;" _
      & "User ID=sa;Password='';Initial Catalog=pubs"
   conn.Open

   Set rst.ActiveConnection = conn
   rst.CursorLocation = adUseClient

   rst.Open "select * from authors", , adOpenStatic, _
      adLockBatchOptimistic

   'Search from the beginning and find an author named Ringer
   rst.Find "au_lname = 'Ringer'"
   'Search from the beginning and find an author named Green
   rst.Find "au_lname = 'Green'", , , adBookmarkFirst

   'Search from the end and find an author named Ringer
   rst.Find "au_lname = 'Ringer'", , adSearchBackward, adBookmarkLast

End Sub