I am developing an ASP.NET MVC application using Entity Framework in C#. I have a situation in which I have to make two databases with the same schema. One PC at a time is using the application and updating the local database on the PC.
Now I want that local database to update another database that is placed on my remote (live) server. That update will take place after a trigger (I don't want it to be automatically synchronized nor syncronized after a specific time interval).
I searched the internet and found solutions like this one as well as some others.
Mostly people are using Replication Management Objects (RMO). I need to know a few of things:
You can search for .NET Sync Framework With this framework you can create 3 methods,
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 is helpful 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.