Extending SQL Azure data to SQL Compact using Sync Framework 2.1
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
Comments
Anonymous
October 07, 2010
How do you make it sync a table with a geography or geometry column? ThanksAnonymous
December 12, 2010
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.Anonymous
November 20, 2011
Hi, If my source(SQL CE) and Destination(SQL Azure) has different table schema, I cann't create DbSyncTableDescription from SQL CE. Please suggest