A day with .Net

My day to day experince in .net

Archive for July, 2012

Synchronizing SQL Server Databases using Microsoft Sync Framework

Posted by vivekcek on July 8, 2012

Source code Download http://sdrv.ms/PKZNd9

Problem Statement
———————————

I am designing an inventory application. Which have server side application and client side application. Through server application we can add products, can create orders etc. The server side application use a sql server database. The client side applications are used by marketing executives in their laptops or tablets. These marketing executives will be always out of office. They may not have access to internet to connect to server. So a local sql database of the same schema of server database will be installed with the client application. Then we need to do a synchronization with server and client databases.After the synchronization client database will contain all the product data from server. Then the marketing executives meet their clients and create orders for product in their local database. Now when marketing executives reach the main office they need to push all the order information from their local database to server database. At the mean time new products may be added to server database that is to be pushed to client database. This process will continue.

The problem come under the category of occasionally connected systems(OCS).

I love Microsoft because they have solution for everything. i Choose the Microsoft Sync Framework.

Development Environment Setup
———————————————-

1. Visual Studio 2010

2. SQL Server 2008 or SQL Express

3. Download and install Microsoft Sync SDK 2.1 (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=23217)

4. Add reference to the below library’s.

Microsoft.Synchronization.dll
Microsoft.Synchronization.Data.dll
Microsoft.Synchronization.Data.SqlServer.dll

5. Create a database named ‘SyncDB’ using the below script.

USE [master]
GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'SyncDB')
DROP DATABASE SyncDB

CREATE DATABASE [SyncDB] 
GO

USE [SyncDB]
GO

CREATE TABLE [dbo].[Products](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ListPrice] [money] NOT NULL
      
      CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([ID] ASC)
)

GO

CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [int] NOT NULL,
[OriginState] [nvarchar](2) NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([OrderID] ASC,[ProductID] ASC)
)
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ID])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Products]
GO

INSERT INTO Products VALUES (1, 'PC', 400)
INSERT INTO Products VALUES (2, 'Laptop', 600)
INSERT INTO Products VALUES (3, 'NetBook', 300)
INSERT INTO Orders VALUES (1, 1, 2, 'NC')
INSERT INTO Orders VALUES (2, 2, 1, 'NC')
INSERT INTO Orders VALUES (3, 1, 5, 'WA')
INSERT INTO Orders VALUES (3, 3, 10, 'WA')
INSERT INTO Orders VALUES (4, 2, 4, 'WA')

6. Create SyncClientDB.With out any tables in it.

HOW SYNC WORKS
——————————-

To synchronize SQL Server database with a SQL Client database we will complete the tasks below.

1. Define the scope based on the tables from the SQL Server database, then provision the SQL Server and SQL client databases.This will prepare the SQL Server and SQL client databases for Synchronization.
2. Synchronize the SQL Server and SQL client databases after they have been configured for synchronization as per step 1.
3. Optionally if you wish you can use the SqlSyncDeprovisioning class to deprovision the specified scope and remove all associated synchronization elements from the database.

1: Scope the SQL Server database and then provision SQL client databases.

 public static void SetUp()
        {
            // Connection to on  SQL Server database
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Create a scope named "product" and add tables to it.
            DbSyncScopeDescription productScope = new DbSyncScopeDescription("product");

            // Select the colums to be included in the Collection Object
            Collection<string> includeColumns = new Collection<string>();
            includeColumns.Add("ID");
            includeColumns.Add("Name");
            includeColumns.Add("ListPrice");

            // Define the Products table.
            DbSyncTableDescription productDescription =
                                                    SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Products",
                                                                                                        includeColumns, serverConn);

            // Add the Table to the scope object.    
            productScope.Tables.Add(productDescription);

            // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);


            // Filter Rows for the ListPrice column
            serverProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
            serverProvision.Tables["dbo.Products"].FilterClause = "[side].[ListPrice] < '600'";

            if (!serverProvision.ScopeExists("product"))
                serverProvision.Apply();

            // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);


            if (!clientProvision.ScopeExists("product"))
                clientProvision.Apply();

            // Shut down database connections.

            serverConn.Close();

            serverConn.Dispose();

            clientConn.Close();

            clientConn.Dispose();
        }

2: Synchronize the SQL Server and SQL Client databases

public static void Synchronize()
        {
            // Connection to  SQL Server
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Perform Synchronization between SQL Server and the SQL client.
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // Create provider for SQL Server
            SqlSyncProvider serverProvider = new SqlSyncProvider("product", serverConn);

            // Set the command timeout and maximum transaction size for the SQL Azure provider.
            SqlSyncProvider clientProvider = new SqlSyncProvider("product", clientConn);

            // Set Local provider of SyncOrchestrator to the server provider
            syncOrchestrator.LocalProvider = serverProvider;

            // Set Remote provider of SyncOrchestrator to the client provider
            syncOrchestrator.RemoteProvider = clientProvider;

            // Set the direction of SyncOrchestrator session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            // Create SyncOperations Statistics Object
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            // Display the Statistics
            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);

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();

        }

FULL CODE
————————-

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace SyncDb
{
    class Program
    {
        static void Main(string[] args)
        {
            string answer = string.Empty;

            string selection = string.Empty;

            do
            {

                Console.WriteLine("\nEnter a choice below:");

                Console.WriteLine(@"Type 'setup' to scope the database");

                Console.WriteLine(@"Type 'sync' to sync the database");

                Console.WriteLine(@"Type 'delete' to remove scope of the database");

                Console.Write("Enter command: ");

                selection = Console.ReadLine();

                if (selection.Equals("setup"))
                    SetUp();
                else if (selection.Equals("sync"))
                    Synchronize();
                else if (selection.Equals("delete"))
                    Deprovision();

                Console.Write("Do you wish to continue? (y/n): ");
                answer = Console.ReadLine();

            }
            while (answer.Equals("y"));
        }
        public static void SetUp()
        {
            // Connection to on  SQL Server database
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Create a scope named "product" and add tables to it.
            DbSyncScopeDescription productScope = new DbSyncScopeDescription("product");

            // Select the colums to be included in the Collection Object
            Collection<string> includeColumns = new Collection<string>();
            includeColumns.Add("ID");
            includeColumns.Add("Name");
            includeColumns.Add("ListPrice");

            // Define the Products table.
            DbSyncTableDescription productDescription =
                                                    SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Products",
                                                                                                        includeColumns, serverConn);

            // Add the Table to the scope object.    
            productScope.Tables.Add(productDescription);

            // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);


            // Filter Rows for the ListPrice column
            serverProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
            serverProvision.Tables["dbo.Products"].FilterClause = "[side].[ListPrice] < '600'";

            if (!serverProvision.ScopeExists("product"))
                serverProvision.Apply();

            // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);


            if (!clientProvision.ScopeExists("product"))
                clientProvision.Apply();

            // Shut down database connections.

            serverConn.Close();

            serverConn.Dispose();

            clientConn.Close();

            clientConn.Dispose();
        }
        public static void Synchronize()
        {
            // Connection to  SQL Server
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Perform Synchronization between SQL Server and the SQL client.
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // Create provider for SQL Server
            SqlSyncProvider serverProvider = new SqlSyncProvider("product", serverConn);

            // Set the command timeout and maximum transaction size for the SQL Azure provider.
            SqlSyncProvider clientProvider = new SqlSyncProvider("product", clientConn);

            // Set Local provider of SyncOrchestrator to the server provider
            syncOrchestrator.LocalProvider = serverProvider;

            // Set Remote provider of SyncOrchestrator to the client provider
            syncOrchestrator.RemoteProvider = clientProvider;

            // Set the direction of SyncOrchestrator session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            // Create SyncOperations Statistics Object
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            // Display the Statistics
            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);

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();

        }
        public static void Deprovision()
        {
            // Connection to  SQL Server database
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Create Scope Deprovisioning for Sql Server and SQL client.
            SqlSyncScopeDeprovisioning serverSqlDepro = new SqlSyncScopeDeprovisioning(serverConn);
            SqlSyncScopeDeprovisioning clientSqlDepro = new SqlSyncScopeDeprovisioning(clientConn);

            // Remove the scope from SQL Server remove all synchronization objects.
            serverSqlDepro.DeprovisionScope("product");
            serverSqlDepro.DeprovisionStore();

            // Remove the scope from SQL client and remove all synchronization objects.
            clientSqlDepro.DeprovisionScope("product");
            clientSqlDepro.DeprovisionStore();

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();

        }
    }
}

Posted in c#.net | 7 Comments »