Synchronization Service

The synchronization service works closely with the Cursor Service for OLE DB to perform the following functions: propagate updates to the data source, coordinate conflicting updates, refresh the data from the data source, and perform nondestructive resynchronization.

Updates

The Cursor Service has two modes of sending the updates back to the data provider: immediate and deferred. In immediate mode, changes are committed to the provider on a row-by-row basis. In deferred mode, the changes made to the rowset are buffered locally and the row's state is marked as changed. When the application decides to commit the changes, it calls the batch update method on the rowset and all buffered changes are sent to the data source.

Using a local static rowset with deferred updates is a common application scenario, offering the following benefits:

It provides fast data navigation because after the data is fetched, most operations are local.
It eliminates heavy concurrency in accessing data on the server. Optimistic conflict resolution allows you to resolve any conflicts.
It reduces the overall network traffic associated with propagating changes to the data source by grouping multiple individual updates in a single request.

Optimistic Conflict Resolution

For each row that has changed in a rowset, the Cursor Service maintains a copy of the original row fetched from the provider. When the updates are sent to the server, the synchronization service compares original values with the current values on the server to verify whether the row has changed since the data was fetched. The synchronization service compares the values using a combination of key columns, row timestamps, changed fields, and sometimes the entire row.

If a conflict is detected, the status for that row changes to indicate an update conflict. You have the option to get the current values for the conflicting row and compare them with the original row values and the changed values in the rowset. After you resolve the conflict, the update can be submitted again, this time using the current values to detect new update conflicts. The update service has the option to get current data for all conflicting rows in a batch. It also allows you to control how the local data is updated when conflicts are detected, to match the transaction model implemented by the application.

Refresh

The ADO Refresh method updates the rowset with the most recent data available from the provider. The scope of the Refresh command can be a large data field, a row, a set of rows, or the entire rowset. To perform a refresh, the update service requires information about the base tables used to produce the rowset and their primary keys.

Nondestructive Resynchronization

The ADO Resync method gives the developer a way to find the current values on the server for the specified records in the recordset, without requiring a complete reexecution of the original query. The Resync method has existed in ADO since version 1.0, but it did not work for client cursors until after ADO 1.5. Additionally, the effects of Resync were "destructive"—that is, Resync discarded any pending changes to the resynchronized row. This was an intentional design. The original implementation did not consider disconnected operation.

Resync now supports client cursors. Additionally, it has a nondestructive option that updates just the UnderlyingValue property for each field without discarding pending changes. The nondestructive option is currently available for client cursors only. It relies on an OLE DB interface, IRowsetRefresh.

There can be cases in which the Resync operation generates an error. These cases arise when the client cursor cannot deduce enough information from the provider to uniquely determine how to get the new values from the server. This can also happen if the user requests a read-only client cursor. In that case, as a performance optimization, ADO does not ask for the full recordset metadata and is unable to perform the Resync operation.

To help ensure the operation's success, your result set should include either the primary key for the table or a column that has a unique key and is not opened as lock type adLockReadOnly.

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

   conn.Execute "create table testtable (dbkey int primary key, _
      field1 char(10))"
   conn.Execute "insert into testtable values (1, 'string1')"

   Set Rst.ActiveConnection = conn
   Rst.CursorLocation = adUseClient

   Rst.Open "select * from testtable", conn, adOpenStatic, _
      adLockBatchOptimistic

   'Create an update to the server row that the
   'client cursor doesn't know about
   conn.Execute "Update testtable set field1 = 'CONFLICT' _
      where dbkey = 1"

   'Change the row on the client
   Rst!field1 = "NewValue"

   'Resync the row to find out what's on the server
   Rst.Resync adAffectCurrent, adResyncUnderlyingValues

   Debug.Print "Current Edited Value: " & Rst!field1.Value
   Debug.Print "Value Before Editing: " & Rst!field1.OriginalValue
   Debug.Print "Value On the Server: " & Rst!field1.UnderlyingValue

End Sub