Extending SQL Azure data to SQL Compact using Sync Framework 2.1

Extending SQL Azure data to SQL Compact using Sync Framework 2.1

  • Comments 3

I have just posted a webcast entitled "Extending SQL Azure data to SQL Compact using Sync Framework 2.1".  This is part 2 to my original webcast where I showed how to use the Sync Framework to synchronize between an on-premises SQL Server database and a SQL Azure database.  In this video I show you how to modify the code from the original webcast to allow a SQL Compact database to synchronize with a SQL Azure database.

Below I have included the main code (program.cs) associated with this console application that allows me to synchronize the Customer and Product table from the SQL Azure AdventureWorks databases to SQL Compact. 

Make sure to update it with your own connection information and add references to the Sync Framework components.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data.SqlClient;

using System.Data.SqlServerCe;

using Microsoft.Synchronization.Data.SqlServer;

using Microsoft.Synchronization.Data.SqlServerCe;

using Microsoft.Synchronization.Data;

using Microsoft.Synchronization;

 

namespace SQLAzureDataSync

{

    class Program

    {

        public static string sqlazureConnectionString = "Server=[Your SQL Azure Server].database.windows.net;Database=AdventureWorksLTSQLAzure;User ID=[Your SQL Azure User Name]@[Your SQL Azure Server];Password=[Your SQL Azure Password];Trusted_Connection=False;Encrypt=True;";

        public static string sqlCeConnectionString = "Data Source=SyncTest.sdf";

        public static readonly string scopeName = "alltablesyncgroup";

 

        static void Main(string[] args)

        {

            // Test if input arguments were supplied:

            if (args.Length == 0)

            {

                System.Console.WriteLine("Please enter an argument.");

                System.Console.WriteLine("Usage: SyncTest.exe -setup");

                System.Console.WriteLine("       SyncTest.exe -sync");

            }

            else if (args[0] == "-setup")

                Setup();

            else if (args[0] == "-sync")

                Sync();

        }

 

        public static void Setup()

        {

            try

            {

 

                SqlCeConnection sqlCeConn = new SqlCeConnection(sqlCeConnectionString);

                SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);

                DbSyncScopeDescription myScope = new DbSyncScopeDescription(scopeName);

 

                DbSyncTableDescription Customer = SqlSyncDescriptionBuilder.GetDescriptionForTable("SalesLt.Customer", sqlAzureConn);

                DbSyncTableDescription Product = SqlSyncDescriptionBuilder.GetDescriptionForTable("SalesLt.Product", sqlAzureConn);

 

                // Add the tables from above to the scope

                myScope.Tables.Add(Customer);

                myScope.Tables.Add(Product);

 

                // Setup SQL Azure for sync

                SqlSyncScopeProvisioning sqlAzureProv = new SqlSyncScopeProvisioning(sqlAzureConn, myScope);

                if (!sqlAzureProv.ScopeExists(scopeName))

                {

                    // Apply the scope provisioning.

                    Console.WriteLine("Provisioning SQL Azure for sync " + DateTime.Now);

                    sqlAzureProv.Apply();

                    Console.WriteLine("Done Provisioning SQL Azure for sync " + DateTime.Now);

                }

                else

                    Console.WriteLine("SQL Azure Database server already provisioned for sync " + DateTime.Now);

 

                // Setup SQL CE for sync

 

                if (!(System.IO.File.Exists("SyncTest.sdf")))

                {

                    SqlCeEngine engine = new SqlCeEngine(sqlCeConnectionString);

                    engine.CreateDatabase();

                }

 

                SqlCeSyncScopeProvisioning sqlCeProv = new SqlCeSyncScopeProvisioning(sqlCeConn, myScope);

                if (!sqlCeProv.ScopeExists(scopeName))

                {

                    // Apply the scope provisioning.

                    Console.WriteLine("Provisioning SQL CE for sync " + DateTime.Now);

                    sqlCeProv.Apply();

                    Console.WriteLine("Done Provisioning SQL CE for sync " + DateTime.Now);

                }

                else

                    Console.WriteLine("SQL CE Database server already provisioned for sync " + DateTime.Now);

 

                sqlAzureConn.Close();

                sqlCeConn.Close();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

 

        public static void Sync()

        {

            try

            {

 

                SqlCeConnection sqlCeConn = new SqlCeConnection(sqlCeConnectionString);

                SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);

                SyncOrchestrator orch = new SyncOrchestrator

                {

                    RemoteProvider = new SqlSyncProvider(scopeName, sqlAzureConn),

                    LocalProvider = new SqlCeSyncProvider(scopeName, sqlCeConn),

                    Direction = SyncDirectionOrder.UploadAndDownload

                };

                Console.WriteLine("ScopeName={0} ", scopeName.ToUpper());

                Console.WriteLine("Starting Sync " + DateTime.Now);

                ShowStatistics(orch.Synchronize());

 

                sqlAzureConn.Close();

                sqlCeConn.Close();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

 

        public static void ShowStatistics(SyncOperationStatistics syncStats)

        {

            string message;

 

            message = "\tSync Start Time :" + syncStats.SyncStartTime.ToString();

            Console.WriteLine(message);

            message = "\tSync End Time   :" + syncStats.SyncEndTime.ToString();

            Console.WriteLine(message);

            message = "\tUpload Changes Applied :" + syncStats.UploadChangesApplied.ToString();

            Console.WriteLine(message);

            message = "\tUpload Changes Failed  :" + syncStats.UploadChangesFailed.ToString();

            Console.WriteLine(message);

            message = "\tUpload Changes Total   :" + syncStats.UploadChangesTotal.ToString();

            Console.WriteLine(message);

            message = "\tDownload Changes Applied :" + syncStats.DownloadChangesApplied.ToString();

            Console.WriteLine(message);

            message = "\tDownload Changes Failed  :" + syncStats.DownloadChangesFailed.ToString();

            Console.WriteLine(message);

            message = "\tDownload Changes Total   :" + syncStats.DownloadChangesTotal.ToString();

            Console.WriteLine(message);

        }

    }

}

Liam 

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • How do you make it sync a table with a geography or geometry column?

    Thanks

  • If we want to sync like we have old sync 1.0 framework over ado.net services.

    Does it make us some bettor options?

    Can we make multiple scopes one for download only, one for bidirection and for Upload only. Also can we filter the data on server only data relate to client should be downloaded and not using peer to peer.

  • Hi,

    If my source(SQL CE) and Destination(SQL Azure) has different table schema,  I cann't create DbSyncTableDescription from SQL CE. Please suggest

Page 1 of 1 (3 items)