Applications are increasingly being used on mobile clients, such as portable computers and devices. Because these mobile clients do not have a consistent or reliable network connection to a central server, it is important for these applications to work against a local copy of data on the client. Equally important is the need to synchronize the local copy of the data with a central server when a network connection is available. Modeled after the ADO.NET data access APIs, the Synchronization Services API provides an intuitive way to synchronize data. Synchronization Services makes building applications for occasionally connected environments a logical extension of building applications in which you can depend on a consistent network connection.
Architecture
Microsoft Synchronization Services for ADO.NET is a set of DLLs that provides a compassable API. Depending on the architecture and requirements of an application, you can use all or only some of the components that are provided. Synchronization Services enables synchronization between a SQL Server Compact 3.5 client database and a server database or any other data source, such as a service that provides stock quotes in XML. For synchronizing two databases, Synchronization Services supports two-tier and N-tier architectures that use any server database for which an ADO.NET provider is available. For synchronizing between a client database and other types of data sources, Synchronization Services supports a service-based architecture. This architecture requires more application code than two-tier and N-tier architectures; however, it does not require a developer to take a different approach to synchronization.
The following illustrations show the components that are involved in two-tier, N-tier, and service-based architectures. Each illustration shows a single client, but there are frequently multiple clients that synchronize with a single server. Synchronization Services uses a hub-and-spoke model. Synchronization is always initiated by the client. All changes from each client are synchronized with the server before the changes are sent from the server to other clients. (These are clients that do not exchange changes directly with one another.)
Synchronization Services provides snapshot, download-only, upload-only, and bidirectional synchronization:
- Snapshot and download-only synchronization are typically used to store and update reference data, such as a product list, on a client. Data changes that are made at the server are downloaded to the client database during synchronization. Snapshot synchronization completely refreshes data every time that the client is synchronized. This is appropriate when you do not want to track incremental changes or the server cannot do so. Download-only synchronization downloads only the incremental changes that have occurred since the previous synchronization.
- Upload-only synchronization is typically used to insert data, such as a sales order, on a client. Inserts and other changes to data that are made in the client database are uploaded to the server during synchronization.
- Bidirectional synchronization is typically used for data, such as customer contact information, that can be updated at the client and server. Any conflicting changes must be handled during synchronization.
- The Synchronization Services architecture is asymmetric: This means that change-tracking is built into the client database, but you must track changes in the server data store if you want incremental changes to be downloaded. For more information about the types of synchronization
The components in the architecture illustrations include the client and server databases and a set of classes from the Synchronization Services API. The N-tier and service-based architectures also include Web Service and Transport components that you must write.
Two – Tier Architecture
Except for the two databases, all items in the illustration correspond to Synchronization Services classes. These classes are contained in the following DLLs:
- Microsoft.Synchronization.Data.dll contains Synchronization Agent, Synchronization Tables, and Synchronization Groups.
- Microsoft. Synchronization.Data.SqlServerCe.dll contains the Client Synchronization Provider.
- Microsoft. Synchronization.Data.Server.dll contains the Server Synchronization Provider and Synchronization Adapters.
- All the above dlls depend on System.Data.dll and System.dll with related .NET Framework version.Microsoft.Synchronization.Data.SqlServerCe.dll also depend of System.Data.SqlServerCe.dll from SQL Server Compact 3.5 for Two tire applications, all Synchronization Service dlls reside on the client. For N-tires applications same dlls reside on computer that provides a Synchronization Services.
- N-Tier Architecture
The second illustration shows N-tier architecture. This requires a proxy, a service, and a transport mechanism to communicate between the client database and the server database. This architecture is more common than two-tier architecture, because N-tier architecture does not require a direct connection between the client and server databases.
Service-based Architecture
The third illustration shows a service-based architecture. This architecture includes a client database, but does not include a server database or the corresponding Server Synchronization Provider and Synchronization Adapters. To use this kind of architecture, an application must be able to communicate to the Synchronization Agent through a custom proxy and custom service. These must provide the same functionality that the Server Synchronization Provider and Synchronization Adapters usually provide, such as retrieving changes to synchronize.
Pre – requirement
- ADO.NET Synchronization Framework – Click to download
- SQL CE 3.5 or later on – Click to down load
- Microsoft ActiveSync – Click to download
- NETCF 3.5 or later on - Click to download
A Synchronized Service Application
You can easily write your first application for synchronization after reading sample of code. Before started make sure that following items are installed on your computer
- Synchronization Service
- The application required Microosft.Synchronization.Data.dll, Micorosoft.Synchronization.Data.SQLServerCe.dll
- SQL Server Compact 3.5
- The Application requires a reference to System.Data.SqlServerCe.dll
A application is composed in Six class
- SampleServiceAgent Class . This class derived from SyncAgent and contain 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 the SyncAgent.
- Program. This class sets up synchronization and calls methods from the Utility class.
- 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 complete Utility class is used in other topics
Key Parts of the API
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.
C # Code |
SyncTable customerSyncTable = new SyncTable("Customer"); customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable; customerSyncTable.SyncDirection = SyncDirection.DownloadOnly; this.Configuration.SyncTables.Add(customerSyncTable); |
Using the SqlSyncAdapterBuilder
The following code example creates a SyncAdapter for the Customer table. The synchronization adapter makes available to the server synchronization provider the specific commands that are required to interact with the server database. In this application, synchronization adapter is created by using the SqlSyncAdapterBuilder. The following information is specified for the SqlSyncAdapterBuilder and SyncAdapter:
- The name of the table and the tombstone table. A tombstone table is used to track delete operations in the server database. For more information, see How to: Prepare a Server Database for Synchronization. If the tables are in a schema other than dbo, the schema must be specified.
- The direction of synchronization. This controls which commands the SqlSyncAdapterBuilder creates. 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: Prepare a Server Database for Synchronization.
- The name of the SyncAdapter. This must match the name of the SyncTable. Therefore, it should not include the schema name.
For information about how to create commands manually instead of using the builder
C # Code |
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); |
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 How to: Prepare a Server Database for Synchronization.
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.
C # Code |
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; |
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.
C # Code |
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent(); SyncStatistics syncStatistics = sampleSyncAgent.Synchronize(); |
Complete code example in C# |
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 database. Utility util = new Utility(); //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. 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(); //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(); //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(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 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(); 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 = 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 ******"); } 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 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 string ClientConnString { get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; } } //Return the server connection string. public string ServerConnString { get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"; } } //Make server changes that are synchronized on the second //synchronization. public void MakeDataChangesOnServer() { int rowCount = 0; using (SqlConnection serverConn = new SqlConnection(this.ServerConnString)) { 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 void CleanUpServer() { using(SqlConnection serverConn = new SqlConnection(this.ServerConnString)) { SqlCommand sqlCommand = serverConn.CreateCommand(); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "usp_InsertSampleData"; serverConn.Open(); sqlCommand.ExecuteNonQuery(); serverConn.Close(); } } //Delete the client database. public void RecreateClientDatabase() { using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString)) { if (File.Exists(clientConn.Database)) { File.Delete(clientConn.Database); } } SqlCeEngine sqlCeEngine = new SqlCeEngine(this.ClientConnString); sqlCeEngine.CreateDatabase(); } } } |
