How to: Use Session Variables for Collaborative Synchronization (Non-SQL Server)
Nota
The topics in this section of the documentation, Synchronizing Other ADO.NET Compatible Databases, are designed to demonstrate how databases other than SQL Server can be synchronized by using Sync Framework. In this release, SQL Server is used in code examples, but the code can be used for other ADO.NET compatible databases, with some modifications to the SQL Server-specific objects (such as SqlConnection) and the SQL queries that are shown. For information about SQL Server synchronization, see How to: Execute Database Synchronization (SQL Server).
This topic shows how to use session variables in Sync Framework. The examples in this topic focus on the following Sync Framework types and properties:
For information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing Other ADO.NET Compatible Databases.
Understanding Session Variables
Sync Services provides a set of session variables that enable you to pass values to DbSyncAdapter and DbSyncProvider commands during synchronization. These variables are specified like other parameters to queries or stored procedures in ADO.NET commands. During a synchronization session, when each ADO.NET command object is invoked by the DbSyncProvider, the provider goes through the synchronization parameters collection (SyncParameters) to determine if it can match each parameter to a ADO.NET command parameter based on name. If there is a match to a built-in session variable, or to a custom parameter that you have defined, the variable is populated by Sync Framework before the provider calls the command.
For example, the following UPDATE statement applies changes to the Customer table by using the @sync\_min\_timestamp, @sync\_force\_write, and @sync\_row\_count session variables.
The values for @sync\_min\_timestamp and @sync\_force\_write are set by Sync Services during synchronization, and the value for @sync\_row\_count is set by the SQL Server @@rowcount function. You can use the variable names directly as in the previous query, or you can use the string constants that are available from the SyncSession object. The following table lists all the available session variables and how they are used.
Session variable |
Usage |
|---|---|
sync_check_concurrency |
Used by Sync Framework for the commands that update and delete metadata. Sync Framework uses this value to determine whether a metadata change can be made if the row to which the metadata refers has changed. If a metadata change cannot be made, the ApplyMetadataFailed event is raised. |
sync_create_peer_key, sync_create_peer_timestamp, sync_row_is_tombstone, sync_row_timestamp, sync_update_peer_key, sync_update_peer_timestamp, sync_scope_cleanup_timestamp, sync_scope_local_id, sync_shared_scope_name, and sync_table_name |
Used by the commands that select and apply changes to change tracking columns. For more information about these columns, see "Create Tracking Tables for Per-Table Metadata " in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server). |
sync_force_write |
Used with an ApplyAction of RetryWithForceWrite to force applying a change that failed because of a conflict or an error. For more information, see How to: Handle Data Conflicts and Errors for Database Synchronization (SQL Server). |
sync_initialize |
Returns whether the current synchronization session is the initial session (a value of 1), or a subsequent session (a value of 0). |
sync_metadata_only |
Used by Sync Framework for the commands that are specified for the SelectIncrementalChangesCommand and SelectRowCommand properties. When Sync Framework specifies a value of 1 for this variable, it selects only metadata but not the associated data changes. This is an optimization for cases in which Sync Framework does not require the data changes, such as selecting conflicting rows with the SelectRowCommand. |
sync_min_timestamp and sync_new_timestamp |
Used to define the set of changes to be synchronized during a session. During the current synchronization session, the command specified for the SelectNewTimestampCommand property provides a new timestamp value. Changes that are made after the minimum value and before the new value are synchronized. The new value is then stored and used as the minimum value for the next synchronization session. |
sync_row_count |
Returns the number of rows that were affected by the last operation at the server. In SQL Server databases, @@ROWCOUNT provides the value for this variable. As shown in the code examples in this topic, stored procedures should include an output parameter that sets the value for sync_row_count. A row count of 0 indicates that an operation failed, typically because of a conflict or an error. For more information, see How to: Handle Data Conflicts and Errors for Database Synchronization (SQL Server). |
sync_scope_cleanup_knowledge, sync_scope_id, sync_scope_knowledge, sync_scope_name, and sync_scope_timestamp |
Used by the commands that select and apply changes to the table that stores synchronization knowledge. For an example of this table, see "Create Tracking Tables for Per-Scope Metadata" in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server). |
sync_session_id |
Returns a GUID value that identifies the current synchronization session. |
sync_stage_name |
Returns a value that corresponds to one of the values in the DbSyncStage enumeration. |
Example
The following code examples show how to use session variables when you are synchronizing the Customer table in the Sync Framework peer sample database.
Key Parts of the API
This section provides code examples that point out some key parts of the API that involve session variables. Several of the commands that are shown call stored procedures that are included in the sample peer database. For more information, see "Create Stored Procedures to Select and Update Data and Metadata" in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server).
The following code example specifies a query for the SelectNewTimestampCommand property. This property sets the value for the sync_new_timestamp variable. This value is used by the synchronization commands that select changes from the server database.
The MIN_ACTIVE_ROWVERSION function returns the lowest active timestamp (also known as rowversion) value in the current database. A timestamp value is active when it is used in a transaction that has not yet been committed. If there are no active values in the database, MIN_ACTIVE_ROWVERSION returns the same value as @@DBTS + 1. MIN_ACTIVE_ROWVERSION is useful for scenarios such as data synchronization that use timestamp values to group sets of changes together. If an application uses @@DBTS in its anchor commands instead of MIN_ACTIVE_ROWVERSION, it can miss changes that are active when synchronization occurs.
The following code example specifies a stored procedure for the SelectIncrementalChangesCommand property. This procedure selects inserts, updates, and deletes from a peer to apply to a second peer during a synchronization session. The sync_min_timestamp variable specifies the minimum timestamp that is included in the set of changes to be synchronized. The value of this variable is compared to values in the sync_row_timestamp column in the tracking table to determine which rows to select. The sync_initialize variable is used to specify whether a synchronization session is the first session between two peers. If it is the first session between two peers, the sp_Customer_SelectChanges stored procedure can include logic that only executes during this first session.
The following code example specifies a command to apply updates to one peer that are selected from the other peer. The sync_row_count variable enables Sync Framework to determine whether an update succeeded or failed. If an update fails, the sync_force_write variable can be set to 1. This enables different logic in the sp_Customer_ApplyUpdate stored procedure. For more information, see How to: Handle Data Conflicts and Errors for Database Synchronization (SQL Server). Before an update is applied at a peer, the sync_min_timestamp variable is used to check whether a row has been updated at the peer since the previous synchronization session.
The following code examples specify the commands that apply updates to the Customer_Tracking table and the ScopeInfo table. Most of the session variables correspond to columns in the tracking tables. The sync_check_concurrency variable is used in the commands to determine whether a metadata changes can be made when the rows to which the metadata refers have changed.
Complete Code Example
The following complete code example includes the code examples that are described earlier in this topic, and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.