Getting Started: Client and Server Synchronization
This topic describes a console application that downloads an initial dataset and then a set of incremental changes from a single table. The application is straightforward, but it will introduce you to code that is built on, in many ways, throughout the Sync Framework documentation. If you have read Architecture and Classes for Client and Server Synchronization, you should have an understanding of the main classes that are used in the application.
You can learn by just reading through the example code. However, it is more instructive to run the application and to see it in action. Before you run the code, make sure that you have the following installed:
Sync Framework
The application requires references to Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.Data.Server.dll, and Microsoft.Synchronization.Data.SqlServerCe.dll.
SQL Server Compact Service Pack 1
The application requires a reference to System.Data.SqlServerCe.dll.
A version of SQL Server other than SQL Server Compact to act as the server database.
The example code uses
localhostin connection strings. To use the instance of SQL Server Express that installs with Visual Studio, changelocalhostto.\sqlexpress. To use a remote server, changelocalhostto the appropriate server name.The Sync Framework sample databases. Execute both scripts that are available in Setup Scripts for Database Provider How-to Topics. We recommend to that you review these scripts to see how change tracking is handled in the server database.
The application is composed of six classes:
SampleSyncAgent. This class is derived from SyncAgent and contains a SyncTable.SampleServerSyncProvider. This class is derived from DbServerSyncProvider and contains the SyncAdapter.SampleClientSyncProvider. This class is derived from SqlCeClientSyncProvider. In this example, this class contains only a connection string to the client database.SampleStats. This class uses the statistics that are returned by theSyncAgent.Program. This class sets up synchronization and calls methods from theUtilityclass.Utility. This class handles all functionality that is not directly related to synchronization, such as holding connection string information and making changes to the server database. A completeUtilityclass is used in other topics. The complete class is available from Utility Class for Database Provider How-to Topics.
Key Parts of the API
Before you look at the complete code example, we recommend that you review the following examples that illustrate several key sections of the API that are used in this application.
Creating a SyncTable
The following code example creates a SyncTable object for the Customer table, specifies the synchronization direction, and specifies how the table should be created on the client. In this case, if the table already exists in the client database, the table will be dropped during the first synchronization.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
Using the SqlSyncAdapterBuilder
Each of the code examples in this section creates a SyncAdapter for the Customer table. The synchronization adapter provides to the server synchronization provider the specific commands that are required to interact with the server database. In this application, the synchronization adapter is created by using the SqlSyncAdapterBuilder. The first example shows how to use the SqlSyncAdapterBuilder with a custom change tracking system. The second example shows how to use the SqlSyncAdapterBuilder with SQL Server change tracking (available in SQL Server 2008). For more information about change tracking, see Tracking Changes in the Server Database.
For information about how to create commands manually instead of using the builder, see How to: Download Incremental Data Changes to a Client.
Using a Custom Change Tracking System
To use a custom change tracking system, specify the following information for the SqlSyncAdapterBuilder and SyncAdapter:
The name of the table to synchronize and the tombstone table. A tombstone table is used to track delete operations in the server database. For more information, see Tracking Changes in the Server Database. If the tables are in a schema other than dbo, the schema must be specified.
The direction of synchronization. This controls which commands the
SqlSyncAdapterBuildercreates. For more information about commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.The tracking columns in the server database. The columns are used to track when changes are made, so that only new changes are downloaded. You can include additional columns to track where changes are made. For more information, see How to: Use a Custom Change Tracking System.
The name of the
SyncAdapter. This must match the name of theSyncTable. Therefore, it should not include the schema name.
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(false, false, false, false);
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
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)
Using SQL Server Change Tracking
To use SQL Server change tracking, specify the following information for the SqlSyncAdapterBuilder and SyncAdapter:
The name of the table to synchronize.
The direction of synchronization. This controls which commands the
SqlSyncAdapterBuildercreates. For more information about commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.The type of change tracking to use. By default, Sync Framework expects you to specify custom change tracking columns. In this code example, SQL Server change tracking is specified.
The name of the
SyncAdapter. This must match the name of theSyncTable. Therefore, it should not include the schema name.
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
Specifying the New Anchor Command
The following code example specifies a command to retrieve a new anchor value from the server. The value is stored in the client database and is used by the commands that synchronize changes. 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.
In this case, MIN_ACTIVE_ROWVERSION returns a timestamp value from a SQL Server database. (MIN_ACTIVE_ROWVERSION was introduced in SQL Server 2005 Service Pack 2.) A timestamp value is used because the tracking columns that are specified for the SqlSyncAdapterBuilder contain timestamp values. If the tracking columns contained date values, you could use a function such as GETUTCDATE() instead of MIN_ACTIVE_ROWVERSION. For more information about anchors, see Tracking Changes in the Server Database.
The SyncSession class contains several string constants that can be used in synchronization commands. SyncNewReceivedAnchor is one of these constants. You could also use the literal @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;
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
Calling the Synchronize Method
The following code example instantiates SampleSyncAgent and calls the Synchronize method. In the SampleSyncAgent class, the SampleClientSyncProvider is specified as the LocalProvider and the SampleServerSyncProvider is specified as the RemoteProvider, and also the synchronization table that has already been described.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
In the SampleStats class, statistics that are returned by the SyncAgent are used to provide feedback to the user about the synchronization session. For more information, see How to: Work with Events and Program Business Logic.
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
Complete Code Examples
Now that you have seen the main sections of code that are involved in synchronization, these sections are combined in a complete application that is thoroughly commented. After you run this application, we recommend that you read the topics in the section Programming Common Client and Server Synchronization Tasks. You will see the same classes that are used in the code examples in this topic. However, they are applied across additional tables in more sophisticated ways.
Complete Example Using Custom Change Tracking
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 SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetClientPassword();
Utility.RecreateCompactDatabase();
//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.
Utility.MakeDataChangesOnServer();
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
Utility.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 Customer table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//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(Utility.ConnStr_DbServerSync);
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 SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// 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).
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(false, false, false, false);
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
//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 you could use this class to handle client
//provider events and other client-side processing.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
}
}
//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 Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public static void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public static string ConnStr_SqlCeClientSync
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public static string ConnStr_DbServerSync
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public static void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public static void CleanUpServer()
{
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public static void RecreateCompactDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
sqlCeEngine.CreateDatabase();
}
}
}
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 SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetClientPassword()
Utility.RecreateCompactDatabase()
'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.
Utility.MakeDataChangesOnServer()
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
Utility.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 Customer table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
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(Utility.ConnStr_DbServerSync)
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
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
'Create a SyncAdapter for the Customer table by using
'the SqlSyncAdapterBuilder:
' * Specify the base table and tombstone table names.
' * Specify the columns that are used to track when
' 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).
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)
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 you could use this class to handle client
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
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 ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
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
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Shared Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public Shared ReadOnly Property ConnStr_SqlCeClientSync() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public Shared ReadOnly Property ConnStr_DbServerSync() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Shared Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Shared Sub CleanUpServer()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Shared Sub RecreateCompactDatabase()
Dim clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Utility.ConnStr_SqlCeClientSync)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility
Complete Example Using SQL Server Change Tracking
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 SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetClientPassword();
Utility.RecreateCompactDatabase();
//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.
Utility.MakeDataChangesOnServer();
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
Utility.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 Customer table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//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(Utility.ConnStr_DbServerSync);
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 + " = change_tracking_current_version()";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table name.
// * Specify that the server uses SQL Server change tracking.
// * 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).
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
//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 you could use this class to handle client
//provider events and other client-side processing.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
}
}
//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 Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public static void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public static string ConnStr_SqlCeClientSync
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public static string ConnStr_DbServerSync
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public static void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public static void CleanUpServer()
{
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public static void RecreateCompactDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
sqlCeEngine.CreateDatabase();
}
}
}
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 SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetClientPassword()
Utility.RecreateCompactDatabase()
'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.
Utility.MakeDataChangesOnServer()
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
Utility.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 Customer table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
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(Utility.ConnStr_DbServerSync)
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 + " = change_tracking_current_version()"
.Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer table by using
'the SqlSyncAdapterBuilder:
' * Specify the base table names.
' * Specify that the server uses SQL Server change tracking.
' * 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).
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
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 you could use this class to handle client
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
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 ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
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
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Shared Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public Shared ReadOnly Property ConnStr_SqlCeClientSync() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public Shared ReadOnly Property ConnStr_DbServerSync() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Shared Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Shared Sub CleanUpServer()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Shared Sub RecreateCompactDatabase()
Dim clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Utility.ConnStr_SqlCeClientSync)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility
See Also
Concepts
Architecture and Classes for Client and Server Synchronization
Offline Scenarios