How to: Use Session Variables
This topic shows you how to use session variables in Sync Services for ADO.NET 2.0. The examples in this topic focus on the following Sync Services types:
For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks.
Understanding Session Variables
Sync Services provides a set of session variables that enable you to pass values to SyncAdapter commands during synchronization. These variables are specified like other parameters to queries or stored procedures in ADO.NET commands. For example, the following query selects changes from the Customer table by using the sync_last_received_anchor, sync_new_received_anchor, and sync_client_id session variables.
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor AND
InsertTimestamp <= @sync_new_received_anchor AND InsertId <>
@sync_client_id"
The values for these variables are supplied by Sync Services during synchronization. You can use the variable names directly as in the previous query, or you can use the constants that are available from the SyncSession object.
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @" +
SyncSession.SyncLastReceivedAnchor +
" AND InsertTimestamp <= @" +
SyncSession.SyncNewReceivedAnchor +
" AND InsertId <> @"
SyncSession.SyncClientId
The following table lists all of the available session variables and how they are used.
| Session variable | Usage |
|---|---|
sync_client_id, sync_client_id_hash, sync_client_id_binary, and sync_originator_id |
Used to identify the client that is currently synchronizing. An ID is typically used for conflict detection and to prevent echoing of changes back to the client during bidirectional synchronization. For more information, see How to: Exchange Bidirectional Incremental Data Changes Between a Client and Server. By default, Sync Services identifies each client with a GUID, which is returned by sync_client_id. You can also create a hash of the ID and use sync_client_id_hash in queries. sync_client_id_binary is useful when you track changes by using SQL Server change tracking. You can map the GUID from sync_client_id to an integer and use sync_originator_id. For more information, see the "Examples" section later in this topic. |
sync_last_received_anchor and sync_new_received_anchor |
Used to define the set of changes to be synchronized during a session. During the current synchronization, the command specified for the SelectNewAnchorCommand property provides a new anchor value. Changes that are made after the last received anchor value and before the new received anchor value are synchronized. The new received anchor is then stored and used as the last received anchor value for the next synchronization. For more information, see "Determining Which Data Changes to Download to a Client" in Tracking Changes in the Server Database. |
sync_force_write |
Used with an ApplyAction of RetryWithForceWrite to force the application of a change that failed because of a conflict or an error. For more information, see How to: Handle Data Conflicts and Errors. |
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. 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. |
sync_initialized |
Returns whether the current synchronization is the initial synchronization (a value of 0), or a subsequent synchronization (a value of 1). |
sync_table_name and sync_group_name |
Used if you must specify a table name or group name in a query. |
sync_batch_count, sync_batch_size, and sync_max_received_anchor |
Used if you batch changes. For more information, see How to: Specify the Order and Batch Size of Changes. |
sync_session_id |
Returns a GUID value that identifies the current synchronization session. |
Example
The following code examples show how to use session variables when you are synchronizing the Vendor table in the Sync Services sample database.
Key Parts of the API
This section provides code examples that point out the key parts of the API that involve session variables. The following code example specifies a query for the SelectNewAnchorCommand property, which sets the value for the sync_new_received_anchor variable. This value is used by the synchronization commands that select changes from the server database.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
The following code example specifies a command to select incremental updates from the server to apply to the client. The command includes anchor variables and the sync_originator_id variable. The value for the sync_originator_id variable is supplied by the query that is specified for the SelectClientIdCommand property. This query and property are described at the end of this section.
SqlCommand vendorIncrUpdates = new SqlCommand();
vendorIncrUpdates.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor " +
"WHERE (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_originator_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_originator_id))";
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrUpdates.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates;
Dim vendorIncrUpdates As New SqlCommand()
With vendorIncrUpdates
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor " _
& "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_originator_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_originator_id))"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates
The following code example specifies a command to apply updates from the client to the server. In addition to anchor and ID variables, this command includes the sync_force_write and sync_row_count variables.
SqlCommand vendorUpdates = new SqlCommand();
vendorUpdates.CommandText =
"UPDATE Sales.Vendor SET " +
"VendorName = @VendorName, CreditRating = @CreditRating, " +
"PreferredVendor = @PreferredVendor, " +
"UpdateId = @sync_originator_id " +
"WHERE (VendorId = @VendorId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_originator_id)) " +
"SET @sync_row_count = @@rowcount";
vendorUpdates.Parameters.Add("@VendorName", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorUpdates.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorUpdates.Connection = serverConn;
vendorSyncAdapter.UpdateCommand = vendorUpdates;
Dim vendorUpdates As New SqlCommand()
With vendorUpdates
.CommandText = _
"UPDATE Sales.Vendor SET " _
& "VendorName = @VendorName, CreditRating = @CreditRating, " _
& "PreferredVendor = @PreferredVendor, " _
& "UpdateId = @sync_originator_id " _
& "WHERE (VendorId = @VendorId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_originator_id)) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@VendorName", SqlDbType.NVarChar)
.Parameters.Add("@CreditRating", SqlDbType.NVarChar)
.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.UpdateCommand = vendorUpdates
The following code example specifies a command to select incremental deletes from the server to apply to the client. In addition to anchor and ID parameters, this command includes the sync_initialized variable. In this case, rows are selected from the tombstone table only if this is a subsequent synchronization. Rows in the tombstone table are not relevant during an initial synchronization.
SqlCommand vendorIncrDeletes = new SqlCommand();
vendorIncrDeletes.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_originator_id)";
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrDeletes.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes;
Dim vendorIncrDeletes As New SqlCommand()
With vendorIncrDeletes
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_originator_id)"
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes
The following code example specifies a command to map a client ID to an originator ID. This is not required, but it can be useful to use an integer to represent a client instead of the GUID that Sync Services uses. The stored procedure is described in the next code example.
SqlCommand selectClientIdCommand = new SqlCommand();
selectClientIdCommand.CommandType = CommandType.StoredProcedure;
selectClientIdCommand.CommandText = "usp_GetOriginatorId";
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;
selectClientIdCommand.Connection = serverConn;
this.SelectClientIdCommand = selectClientIdCommand;
Dim selectClientIdCommand As New SqlCommand()
With selectClientIdCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_GetOriginatorId"
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectClientIdCommand = selectClientIdCommand
The following code example creates a mapping table and stored procedure to read from and populate the table. The stored procedure takes a client ID (a GUID) as an input and returns an originator ID (an integer). A mapping row is inserted for the server, and additional rows are added as new clients synchronize. After a client synchronizes once, the mapping table contains an entry for this client. Because the SyncAdapter commands use the originator ID, tracking columns in the Vendor table are of type int instead of uniqueidentifier.
CREATE TABLE IdMapping(
ClientId uniqueidentifier NOT NULL PRIMARY KEY,
OriginatorId int NOT NULL)
GO
--Insert a mapping for the server.
INSERT INTO IdMapping VALUES ('00000000-0000-0000-0000-000000000000', 0)
GO
CREATE PROCEDURE usp_GetOriginatorId
@sync_client_id uniqueidentifier,
@sync_originator_id int out
AS
SELECT @sync_originator_id = OriginatorId FROM IdMapping WHERE ClientId = @sync_client_id
IF ( @sync_originator_id IS NULL )
BEGIN
SELECT @sync_originator_id = MAX(OriginatorId) + 1 FROM IdMapping
INSERT INTO IdMapping VALUES (@sync_client_id, @sync_originator_id)
END
GO
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Sync Services How-to Topics.
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The Utility class handles all functionality that is not
//directly related to synchronization, such as holding
//connection string information and making changes to the
//server and client databases.
Utility util = new Utility();
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
util.SetClientPassword();
util.RecreateClientDatabase();
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server and client.
util.MakeDataChangesOnServer("Vendor");
util.MakeDataChangesOnClient("Vendor");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return the server data back to its original state.
util.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Add the Vendor table: specify a synchronization direction of
//Bidirectional, and that an existing table should be dropped.
SyncTable vendorSyncTable = new SyncTable("Vendor");
vendorSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
vendorSyncTable.SyncDirection = SyncDirection.Bidirectional;
this.Configuration.SyncTables.Add(vendorSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a command that enables you to pass in a
//client ID (a GUID) and get back the orginator ID (an integer)
//that is defined in a mapping table on the server.
SqlCommand selectClientIdCommand = new SqlCommand();
selectClientIdCommand.CommandType = CommandType.StoredProcedure;
selectClientIdCommand.CommandText = "usp_GetOriginatorId";
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;
selectClientIdCommand.Connection = serverConn;
this.SelectClientIdCommand = selectClientIdCommand;
//Create a SyncAdapter for the Vendor table, and then define
//the commands to synchronize changes:
//* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
// and SelectIncrementalDeletesCommand are used to select changes
// from the server that the client provider then applies to the client.
//* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
// to the server the changes that the client provider has selected
// from the client.
//Create the SyncAdapter
SyncAdapter vendorSyncAdapter = new SyncAdapter("Vendor");
//Select inserts from the server.
//This command includes three session variables:
//@sync_last_received_anchor, @sync_new_received_anchor,
//and @sync_originator_id. The anchor variables are used with
//SelectNewAnchorCommand to determine the set of changes to
//synchronize. In other example code, the commands use
//@sync_client_id instead of @sync_originator_id. In this case,
//@sync_originator_id is used because the SelectClientIdCommand
//is specified.
SqlCommand vendorIncrInserts = new SqlCommand();
vendorIncrInserts.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor " +
"WHERE (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_originator_id)";
vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrInserts.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalInsertsCommand = vendorIncrInserts;
//Apply inserts to the server.
//This command includes @sync_row_count, which returns
//a count of how many rows were affected by the
//last database operation. In SQL Server, the variable
//is assigned the value of @@rowcount. The count is used
//to determine whether an operation was successful or
//was unsuccessful due to a conflict or an error.
SqlCommand vendorInserts = new SqlCommand();
vendorInserts.CommandText =
"INSERT INTO Sales.Vendor (VendorId, VendorName, CreditRating, PreferredVendor, InsertId, UpdateId) " +
"VALUES (@VendorId, @VendorName, @CreditRating, @PreferredVendor, @sync_originator_id, @sync_originator_id) " +
"SET @sync_row_count = @@rowcount";
vendorInserts.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorInserts.Parameters.Add("@VendorName", SqlDbType.NVarChar);
vendorInserts.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
vendorInserts.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
vendorInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorInserts.Connection = serverConn;
vendorSyncAdapter.InsertCommand = vendorInserts;
//Select updates from the server
SqlCommand vendorIncrUpdates = new SqlCommand();
vendorIncrUpdates.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor " +
"WHERE (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_originator_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_originator_id))";
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrUpdates.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates;
//Apply updates to the server.
//This command includes @sync_force_write, which can
//be used to apply changes in case of a conflict.
SqlCommand vendorUpdates = new SqlCommand();
vendorUpdates.CommandText =
"UPDATE Sales.Vendor SET " +
"VendorName = @VendorName, CreditRating = @CreditRating, " +
"PreferredVendor = @PreferredVendor, " +
"UpdateId = @sync_originator_id " +
"WHERE (VendorId = @VendorId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_originator_id)) " +
"SET @sync_row_count = @@rowcount";
vendorUpdates.Parameters.Add("@VendorName", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorUpdates.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorUpdates.Connection = serverConn;
vendorSyncAdapter.UpdateCommand = vendorUpdates;
//Select deletes from the server.
//This command includes @sync_initialized, which is
//used to determine whether a client has been
//initialized already. If this variable returns 0,
//this is the first synchronization for this client ID
//or originator ID.
SqlCommand vendorIncrDeletes = new SqlCommand();
vendorIncrDeletes.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_originator_id)";
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrDeletes.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes;
//Apply deletes to the server.
SqlCommand vendorDeletes = new SqlCommand();
vendorDeletes.CommandText =
"DELETE FROM Sales.Vendor " +
"WHERE (VendorId = @VendorId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_originator_id)) " +
"SET @sync_row_count = @@rowcount " +
"IF (@sync_row_count > 0) BEGIN " +
"UPDATE Sales.Vendor_Tombstone " +
"SET DeleteId = @sync_originator_id " +
"WHERE (VendorId = @VendorId) " +
"END";
vendorDeletes.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorDeletes.Connection = serverConn;
vendorSyncAdapter.DeleteCommand = vendorDeletes;
//Add the SyncAdapter to the server synchronization provider.
this.SyncAdapters.Add(vendorSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but here we use this class to handle client
//provider events.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = util.ClientConnString;
//We use the CreatingSchema event to change the schema
//by using the API. We use the SchemaCreated event to
//change the schema by using SQL.
this.CreatingSchema +=new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
this.SchemaCreated +=new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
}
private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
//Set the RowGuid property because it is not copied
//to the client by default. This is also a good time
//to specify literal defaults with .Columns[ColName].DefaultValue,
//but we will specify defaults like NEWID() by calling
//ALTER TABLE after the table is created.
Console.Write("Creating schema for " + e.Table.TableName + " | ");
e.Schema.Tables["Vendor"].Columns["VendorId"].RowGuid = true;
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
//Call ALTER TABLE on the client. This must be done
//over the same connection and within the same
//transaction that Synchronization Services uses
//to create the schema on the client.
Utility util = new Utility();
util.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName);
Console.WriteLine("Schema created for " + e.Table.TableName);
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.TotalChangesUploaded);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The Utility class handles all functionality that is not
'directly related to synchronization, such as holding
'connection string information and making changes to the
'server and client databases.
Dim util As New Utility()
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
util.SetClientPassword()
util.RecreateClientDatabase()
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server and client.
util.MakeDataChangesOnServer("Vendor")
util.MakeDataChangesOnClient("Vendor")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return the server data back to its original state.
util.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Add the Vendor table: specify a synchronization direction of
'Bidirectional, and that an existing table should be dropped.
Dim vendorSyncTable As New SyncTable("Vendor")
vendorSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
vendorSyncTable.SyncDirection = SyncDirection.Bidirectional
Me.Configuration.SyncTables.Add(vendorSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(util.ServerConnString)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a command that enables you to pass in a
'client ID (a GUID) and get back the orginator ID (an integer)
'that is defined in a mapping table on the server.
Dim selectClientIdCommand As New SqlCommand()
With selectClientIdCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_GetOriginatorId"
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectClientIdCommand = selectClientIdCommand
'Create a SyncAdapter for the Vendor table, and then define
'the commands to synchronize changes:
'* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
' and SelectIncrementalDeletesCommand are used to select changes
' from the server that the client provider then applies to the client.
'* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
' to the server the changes that the client provider has selected
' from the client.
'Create the SyncAdapter
Dim vendorSyncAdapter As New SyncAdapter("Vendor")
'Select inserts from the server.
'This command includes three session variables:
'@sync_last_received_anchor, @sync_new_received_anchor,
'and @sync_originator_id. The anchor variables are used with
'SelectNewAnchorCommand to determine the set of changes to
'synchronize. In other example code, the commands use
'@sync_client_id instead of @sync_originator_id. In this case,
'@sync_originator_id is used because the SelectClientIdCommand
'is specified.
Dim vendorIncrInserts As New SqlCommand()
With vendorIncrInserts
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor " _
& "WHERE (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_originator_id)"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalInsertsCommand = vendorIncrInserts
'Apply inserts to the server.
'This command includes @sync_row_count, which returns
'a count of how many rows were affected by the
'last database operation. In SQL Server, the variable
'is assigned the value of @@rowcount. The count is used
'to determine whether an operation was successful or
'was unsuccessful due to a conflict or an error.
Dim vendorInserts As New SqlCommand()
With vendorInserts
.CommandText = _
"INSERT INTO Sales.Vendor (VendorId, VendorName, CreditRating, PreferredVendor, InsertId, UpdateId) " _
& "VALUES (@VendorId, @VendorName, @CreditRating, @PreferredVendor, @sync_originator_id, @sync_originator_id) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@VendorName", SqlDbType.NVarChar)
.Parameters.Add("@CreditRating", SqlDbType.NVarChar)
.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.InsertCommand = vendorInserts
'Select updates from the server
Dim vendorIncrUpdates As New SqlCommand()
With vendorIncrUpdates
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor " _
& "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_originator_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_originator_id))"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates
'Apply updates to the server.
'This command includes @sync_force_write, which can
'be used to apply changes in case of a conflict.
Dim vendorUpdates As New SqlCommand()
With vendorUpdates
.CommandText = _
"UPDATE Sales.Vendor SET " _
& "VendorName = @VendorName, CreditRating = @CreditRating, " _
& "PreferredVendor = @PreferredVendor, " _
& "UpdateId = @sync_originator_id " _
& "WHERE (VendorId = @VendorId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_originator_id)) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@VendorName", SqlDbType.NVarChar)
.Parameters.Add("@CreditRating", SqlDbType.NVarChar)
.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.UpdateCommand = vendorUpdates
'Select deletes from the server.
'This command includes @sync_initialized, which is
'used to determine whether a client has been
'initialized already. If this variable returns 0,
'this is the first synchronization for this client ID
'or originator ID.
Dim vendorIncrDeletes As New SqlCommand()
With vendorIncrDeletes
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_originator_id)"
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes
'Apply deletes to the server.
Dim vendorDeletes As New SqlCommand()
With vendorDeletes
.CommandText = _
"DELETE FROM Sales.Vendor " _
& "WHERE (VendorId = @VendorId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_originator_id)) " _
& "SET @sync_row_count = @@rowcount " _
& "IF (@sync_row_count > 0) BEGIN " _
& "UPDATE Sales.Vendor_Tombstone " _
& "SET DeleteId = @sync_originator_id " _
& "WHERE (VendorId = @VendorId) " _
& "END"
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.DeleteCommand = vendorDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(vendorSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but here we use this class to handle client
'provider events.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = util.ClientConnString
'We use the CreatingSchema event to change the schema
'by using the API. We use the SchemaCreated event to
'change the schema by using SQL.
AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated
End Sub 'New
Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
'Set the RowGuid property because it is not copied
'to the client by default. This is also a good time
'to specify literal defaults with .Columns[ColName].DefaultValue,
'but we will specify defaults like NEWID() by calling
'ALTER TABLE after the table is created.
Console.Write("Creating schema for " + e.Table.TableName + " | ")
e.Schema.Tables("Vendor").Columns("VendorId").RowGuid = True
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
'Call ALTER TABLE on the client. This must be done
'over the same connection and within the same
'transaction that Synchronization Services uses
'to create the schema on the client.
Dim util As New Utility()
util.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName)
Console.WriteLine("Schema created for " + e.Table.TableName)
End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.TotalChangesUploaded)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats