Synchronize live database from local SQL Server database in C#

By 27th November 2018C#

Synchronize live database from local SQL Server database in C#

You can search for .NET Sync Framework With this framework you can create 3 methods, 1. for server machine or Destination machine 2. for client machine or Source machine DB 3. Sync Method

Create Connection String First for client and server and scope name according to your choice.


 static string sServerConnection = @"Data Source=192.168.1.112;Initial Catalog=Server;User ID=sa;Password=123456";

    static string sClientConnection = @"Data Source=MAHAVEER;Initial Catalog=Client;Integrated Security=True";

    static string sScope = "MainScope";
Get and store the data from Client Machine

 //Get Data From Client Provision
    public static void ProvisionClient()
    {
        SqlConnection serverConn = new SqlConnection(sServerConnection);
        SqlConnection clientConn = new SqlConnection(sClientConnection);

        //Drop scope_Info Table
        string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
                   WHERE TABLE_NAME='scope_info') DROP table scope_info";
        clientConn.Open();
        SqlCommand cmd = new SqlCommand(cmdText, clientConn);
        cmd.ExecuteScalar();
        clientConn.Close();


        List<string> tables = new List<string>();
        tables.Add("Demo"); // Add Tables in List
        tables.Add("Product");

        var scopeDesc = new DbSyncScopeDescription("MainScope");
        foreach (var tbl in tables) //Add Tables in Scope
        {
            scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tbl, clientConn));
        }

        SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc); //Provisioning

        //skip creating the user tables
        clientProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

        //skip creating the change tracking tables
        clientProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);

        //skip creating the change tracking triggers
        clientProvision.SetCreateTriggersDefault(DbSyncCreationOption.Skip);

        //skip creating the insert/update/delete/selectrow SPs including those for metadata
        clientProvision.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

        //create new SelectChanges SPs for selecting changes for the new scope
        //the new SelectChanges SPs will have a guid suffix
        clientProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);


        clientProvision.Apply();
    }
Set The data to Server machine with the help of provisioning

    //Set Data To Server Provision
    public static void ProvisionServer()
    {

        SqlConnection serverConn = new SqlConnection(sServerConnection);

        string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
                   WHERE TABLE_NAME='scope_info') DROP table scope_info";
        serverConn.Open();
        SqlCommand cmd = new SqlCommand(cmdText, serverConn);
        cmd.ExecuteScalar();
        serverConn.Close();

        List<string> tables = new List<string>();
        tables.Add("Demo");
        tables.Add("Product");

        var scopeDesc = new DbSyncScopeDescription("MainScope");
        foreach (var tbl in tables)
        {
            scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tbl, serverConn));
        }

        SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc); // Create Provision From All Tables

        //skip creating the user tables
        serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

        //skip creating the change tracking tables
        serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);

        //skip creating the change tracking triggers
        serverProvision.SetCreateTriggersDefault(DbSyncCreationOption.Skip);

        //skip creating the insert/update/delete/selectrow SPs including those for metadata
        serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

        serverProvision.Apply();


    }
After above two process sync process will start according to your provisining and SyncOrchestrator Class will be responsible for all sync process it is class of Microsoft Sync Framework

 public static void Sync()

    {
        SqlConnection serverConn = new SqlConnection(sServerConnection);

        SqlConnection clientConn = new SqlConnection(sClientConnection);

        SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

        syncOrchestrator.LocalProvider = new SqlSyncProvider(sScope, clientConn);

        syncOrchestrator.RemoteProvider = new SqlSyncProvider(sScope, serverConn);

        syncOrchestrator.Direction = SyncDirectionOrder.Upload;

        ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);

        SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

        Console.WriteLine("Start Time: " + syncStats.SyncStartTime);

        Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);

        //Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);

        Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);

        Console.WriteLine(String.Empty);

        Console.ReadLine();

    }
If any Changes or Error will occur than this method will return.

    static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)

    {

        Console.WriteLine(e.Conflict.Type);

        Console.WriteLine(e.Error);

    }
And than Call the above three method in main method. make sure you have to call these methods in a proper way, like first data receive from client machine than set to server machine and than sync process will start

static void Main(string[] args)
    {
        ProvisionClient();
        ProvisionServer();
        Sync();
    }
I Hope this will Helpfull for you, i used this code in a project and this worked fine for me. this is only for single client and single machine sync process not for multiple.

Leave a Reply