Introduction:
Here I will explain how to Synchronizing SQL Server 2 Databases in c#.net.
One of our Client have maintain a website which having database and MS Dynamics Navision for
manual orders. If any new Items placed to the company they need to change the product
information in website (i.e it is reflected to the website database) as well to change the
product information in MS Dynamics Navision. At this situation we implemented the both databases
to synchronize to run the Windows Service, now no need to change the data both side in manually.
Description:
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()
{
try
{
SqlConnection serverConn;
SqlConnection clientConn;
Server_Client_Connection(out serverConn, out clientConn);
// 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'";
serverProvision.Tables["dbo.Products"].AddFilterColumn("ID");
serverProvision.Tables["dbo.Products"].AddFilterColumn("Name");
serverProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
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();
Server_Client_Connection_Close(serverConn, clientConn);
}
catch (SqlException sx)
{
Console.WriteLine("SQL Exception : " + sx.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
2: Synchronize the SQL Server and SQL Client databases
public static void Synchronize()
{
try
{
SqlConnection serverConn;
SqlConnection clientConn;
Server_Client_Connection(out serverConn, out clientConn);
// 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);
Server_Client_Connection_Close(serverConn, clientConn);
}
catch (SqlException sx)
{
Console.WriteLine("SQL Exception : " + sx.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
FULL CODE
static void Main(string[] args)
{
string answer = string.Empty;
string selection = string.Empty;
do
{
Console.WriteLine("\nEnter a choice below:");
Console.WriteLine(@"Type 1 to scope the database ( Setup )");
Console.WriteLine(@"Type 2 to sync the database");
Console.WriteLine(@"Type 3 to remove scope of the database");
Console.Write("Enter command: ");
selection = Console.ReadLine();
switch (selection)
{
case "1":
SetUp();
break;
case "2":
Synchronize();
break;
case "3":
Deprovision();
break;
default:
Console.Write("Invalid Selection Please Enter the Valid Value like 1,2 or 3: ");
break;
}
Console.Write("Do you wish to continue? (y/n): ");
answer = Console.ReadLine();
}
while (answer.Equals("y"));
}
public static void SetUp()
{
try
{
SqlConnection serverConn;
SqlConnection clientConn;
Server_Client_Connection(out serverConn, out clientConn);
// 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'";
serverProvision.Tables["dbo.Products"].AddFilterColumn("ID");
serverProvision.Tables["dbo.Products"].AddFilterColumn("Name");
serverProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
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();
Server_Client_Connection_Close(serverConn, clientConn);
}
catch (SqlException sx)
{
Console.WriteLine("SQL Exception : " + sx.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private static void Server_Client_Connection_Close(SqlConnection serverConn, SqlConnection clientConn)
{
// Shut down database connections.
serverConn.Close();
serverConn.Dispose();
clientConn.Close();
clientConn.Dispose();
}
private static void Server_Client_Connection(out SqlConnection serverConn, out SqlConnection clientConn)
{
// Connection to on SQL Server database
serverConn = new SqlConnection(@"Persist Security Info=False;User ID=sa;password=password;Initial Catalog=SyncDB;Data Source=.;");
// Connection to SQL client database
clientConn = new SqlConnection(@"Persist Security Info=False;User ID=sa;password=password;Initial Catalog=SyncClientDB;Data Source=.;");
}
public static void Synchronize()
{
try
{
SqlConnection serverConn;
SqlConnection clientConn;
Server_Client_Connection(out serverConn, out clientConn);
// 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);
Server_Client_Connection_Close(serverConn, clientConn);
}
catch (SqlException sx)
{
Console.WriteLine("SQL Exception : " + sx.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void Deprovision()
{
try
{
SqlConnection serverConn;
SqlConnection clientConn;
Server_Client_Connection(out serverConn, out clientConn);
// 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();
Server_Client_Connection_Close(serverConn, clientConn);
}
catch (SqlException sx)
{
Console.WriteLine("SQL Exception : " + sx.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Happy coding!....
If you having any queries please leave a comment...........
Step 17: Select Link to the data source by creating linked table and click on ok.
Step 18: On Machine Data source select your odbc connection and click on ok.
Happy coding!...
