How to: Specify the Order and Batch Size of Changes
This topic shows you how to specify the order and batch size of changes that are downloaded to a client database by using Microsoft Synchronization Services for ADO.NET. The examples in this topic focus on the following Synchronization Services types and events:
For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Synchronization Tasks.
Understanding Synchronization Order and Batching
For each table that is synchronized, changes are selected from the server database in the order of inserts, updates, and then deletes. Changes are applied to the client database in the order of deletes, inserts, and then updates. When several tables are synchronized, the order in which each table is processed depends on the order in which its SyncTable object was added to the collection of tables for the synchronization agent. For example, if Customer and OrderHeader tables are added in that order, Customer table inserts are selected first, followed by updates and deletes. Then the changes to the OrderHeader table are selected. All Customer table changes are applied to the client database in a single transaction (if batching is not used), followed by OrderHeader changes that are applied in a second transaction. If the Customer and OrderHeader tables are assigned to the same SyncGroup object, the inserts, updates and deletes for both tables are selected once. All changes are applied to the client database in a single transaction (again, if batching is not used).
By default, Synchronization Services does not divide changes into batches. Changes are downloaded to and uploaded from the client database as a unit. For many applications, it makes sense to divide changes into smaller batches. For example, if a synchronization session is interrupted, synchronization can restart from the last batch instead of resending all the changes. There can also be a performance benefit, because the client can manage a smaller batch of changes at a time. Because of these advantages, Synchronization Services enables applications to download batches of changes to the client (batching is not supported on upload).
Batching is enabled by specifying a value for the BatchSize property, and creating a command for the SelectNewAnchorCommand property that can return anchor values for each batch of changes. Without batching, applications use new anchor and last anchor values to define the upper and lower bounds for the whole set of changes to download. For more information, see Getting Started: A Synchronization Services Application. With batching, the maximum received anchor value defines the upper bound for the whole set of changes, and the new anchor value and last anchor value define the upper and lower bounds for each batch of changes. The SessionProgress event provides a convenient way to monitor overall synchronization progress, and the BatchProgress property provides access to progress information at the batch level.
Example
The following code examples show how to synchronize the Customer and OrderHeader tables in the Synchronization Services sample database. The changes for these tables are downloaded in batches, with 50 changes per batch. The initial synchronization downloads 10 rows. All rows are downloaded in a single batch and applied in a single transaction. The subsequent synchronization downloads 92 rows in two batches. Each batch contains changes from the Customer table and the OrderHeader table, and each batch is applied in a single transaction.
Key Parts of the API
This section provides code examples that point out the key parts of the API to use when you are ordering and batching changes. The following code example is from a class that derives from SyncAgent. The code creates a SyncGroup object for the Customer and OrderHeader tables.
//Create a SyncGroup so that changes to Customer
//and OrderHeader are made in one transaction.
SyncGroup customerOrderSyncGroup = new SyncGroup("CustomerOrder");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
'Create a SyncGroup so that changes to Customer
'and OrderHeader are made in one transaction.
Dim customerOrderSyncGroup As New SyncGroup("CustomerOrder")
'Add each table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
customerSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
The following code example is from a class that derives from DbServerSyncProvider. The code creates an anchor command for an application that does not use batching. It returns a new anchor value once for the whole set of changes to be synchronized. This example is included to make it easy to see the difference between a command that uses batching and one that does not.
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
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
The following code example creates an anchor command that can be used if changes are delivered in batches. Instead of returning a new anchor value once for the whole set of changes, it returns a new anchor value for each batch of changes. It uses the BatchSize property to specify how many changes should be in each batch, and session variables to pass anchor values back and forth between a stored procedure and the synchronization runtime. If you write synchronization adapter commands manually, you still use the @sync_new_received_anchor and @sync_last_received_anchor session variables; the @sync_max_received_anchor session variable is used only by the new anchor command.
SqlCommand selectNewAnchorCommand = new SqlCommand();
selectNewAnchorCommand.Connection = serverConn;
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncMaxReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncNewReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncBatchCount].Direction = ParameterDirection.InputOutput;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
this.BatchSize = 50;
Dim selectNewAnchorCommand As New SqlCommand()
selectNewAnchorCommand.Connection = serverConn
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor"
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncMaxReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncNewReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncBatchCount).Direction = ParameterDirection.InputOutput
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Me.BatchSize = 50
The following code example creates a stored procedure that outputs new anchor values, a maximum anchor value, and the current batch count for inserts and updates. The procedure enables the server synchronization provider to select batches of changes from the server database. The logic in this stored procedure is an example, but any logic can be used as long as it provides the output values shown here. One shortcoming of the example code is that empty batches can occur if a row is changed more than 50 times between synchronizations. You could add logic to handle this case.
CREATE PROCEDURE usp_GetNewBatchAnchor (
@sync_last_received_anchor timestamp,
@sync_batch_size bigint,
@sync_max_received_anchor timestamp out,
@sync_new_received_anchor timestamp out,
@sync_batch_count int output)
AS
-- Set a default batch size if a valid one is not passed in.
IF @sync_batch_size IS NULL OR @sync_batch_size <= 0
SET @sync_batch_size = 1000
-- Before selecting the first batch of changes,
-- set the maximum anchor value for this synchronization session.
-- After the first time that this procedure is called,
-- Synchronization Services passes a value for @sync_max_received_anchor
-- to the procedure. Batches of changes are synchronized until this
-- value is reached.
IF @sync_max_received_anchor IS NULL
SELECT @sync_max_received_anchor = MIN_ACTIVE_ROWVERSION() - 1
-- If this is the first synchronization session for a database,
-- get the lowest timestamp value from the tables. By default,
-- Synchronization Services uses a value of 0 for @sync_last_received_anchor
-- on the first synchronization. If you do not set @sync_last_received_anchor,
-- this can cause empty batches to be downloaded until the lowest
-- timestamp value is reached.
IF @sync_last_received_anchor IS NULL OR @sync_last_received_anchor = 0
BEGIN
SELECT @sync_last_received_anchor = MIN(TimestampCol) FROM (
SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.Customer
UNION
SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.Customer
UNION
SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.OrderHeader
UNION
SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.OrderHeader
) MinTimestamp
SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
-- Determine how many batches are required during the initial synchronization.
IF @sync_batch_count <= 0
SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_last_received_anchor / @sync_batch_size))
END
ELSE
BEGIN
SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
-- Determine how many batches are required during subsequent synchronizations.
IF @sync_batch_count <= 0
SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_new_received_anchor / @sync_batch_size)) + 1
END
-- Check whether this is the last batch.
IF @sync_new_received_anchor >= @sync_max_received_anchor
BEGIN
SET @sync_new_received_anchor = @sync_max_received_anchor
IF @sync_batch_count <= 0
SET @sync_batch_count = 1
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 Synchronization 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 and
//from SyncAgent events.
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.
util.MakeDataChangesOnServer("CustomerAndOrderHeader");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return 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();
//Create a SyncGroup so that changes to Customer
//and OrderHeader are made in one transaction.
SyncGroup customerOrderSyncGroup = new SyncGroup("CustomerOrder");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
}
}
//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 call a stored procedure
//that returns an anchor that can be used with batches
//of changes.
SqlCommand selectNewAnchorCommand = new SqlCommand();
selectNewAnchorCommand.Connection = serverConn;
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncMaxReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncNewReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncBatchCount].Direction = ParameterDirection.InputOutput;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
this.BatchSize = 50;
//Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// and where changes are made.
// * Specify download only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//Customer table
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//OrderHeader table.
SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);
orderHeaderBuilder.TableName = "Sales.OrderHeader";
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//Handle the ChangesSelected event, and display
//information to the console.
this.ChangesSelected += new EventHandler<ChangesSelectedEventArgs>(SampleServerSyncProvider_ChangesSelected);
}
public void SampleServerSyncProvider_ChangesSelected(object sender, ChangesSelectedEventArgs e)
{
Console.WriteLine("Total number of batches: " + e.Context.BatchCount);
Console.WriteLine("Changes applied for group " + e.GroupMetadata.GroupName);
Console.WriteLine("Inserts applied for group: " + e.Context.GroupProgress.TotalInserts.ToString());
Console.WriteLine("Updates applied for group: " + e.Context.GroupProgress.TotalUpdates.ToString());
Console.WriteLine("Deletes applied for group: " + e.Context.GroupProgress.TotalDeletes.ToString());
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = util.ClientConnString;
}
}
//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 Stats ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization Stats ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
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 and
'from SyncAgent events.
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.
util.MakeDataChangesOnServer("CustomerAndOrderHeader")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return 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()
'Create a SyncGroup so that changes to Customer
'and OrderHeader are made in one transaction.
Dim customerOrderSyncGroup As New SyncGroup("CustomerOrder")
'Add each table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
customerSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
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 call a stored procedure
'that returns an anchor that can be used with batches
'of changes.
Dim selectNewAnchorCommand As New SqlCommand()
selectNewAnchorCommand.Connection = serverConn
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor"
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncMaxReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncNewReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncBatchCount).Direction = ParameterDirection.InputOutput
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Me.BatchSize = 50
'Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
' * Specify the base table and tombstone table names.
' * Specify the columns that are used to track when
' and where changes are made.
' * Specify download only synchronization.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
'Customer table
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
customerBuilder.SyncDirection = SyncDirection.DownloadOnly
customerBuilder.CreationTrackingColumn = "InsertTimestamp"
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
'OrderHeader table.
Dim orderHeaderBuilder As New SqlSyncAdapterBuilder(serverConn)
orderHeaderBuilder.TableName = "Sales.OrderHeader"
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone"
orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp"
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp"
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim orderHeaderSyncAdapter As SyncAdapter = orderHeaderBuilder.ToSyncAdapter()
orderHeaderSyncAdapter.TableName = "OrderHeader"
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'Handle the ChangesSelected event, and display
'information to the console.
AddHandler Me.ChangesSelected, AddressOf SampleServerSyncProvider_ChangesSelected
End Sub 'New
Public Sub SampleServerSyncProvider_ChangesSelected(ByVal sender As Object, ByVal e As ChangesSelectedEventArgs)
Console.WriteLine("Total number of batches: " & e.Context.BatchCount)
Console.WriteLine("Changes applied for group " & e.GroupMetadata.GroupName)
Console.WriteLine("Inserts applied for group: " & e.Context.GroupProgress.TotalInserts.ToString())
Console.WriteLine("Updates applied for group: " & e.Context.GroupProgress.TotalUpdates.ToString())
Console.WriteLine("Deletes applied for group: " & e.Context.GroupProgress.TotalDeletes.ToString())
End Sub 'SampleServerSyncProvider_ChangesSelected
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
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
End Sub 'New
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 Stats ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization Stats ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats