private static async Task SynchronizeAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
// First provider is using the Sql change tracking feature. Don't forget to enable it on your database until running this code !
// For instance, use this SQL statement on your server database : ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON)
// Otherwise, if you don't want to use Change Tracking feature, just change 'SqlSyncChangeTrackingProvider' to 'SqlSyncProvider'
var serverProvider = new SqlSyncChangeTrackingProvider(serverConnectionString);
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, tables);
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task SynchronizeAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
// First provider is using the Sql change tracking feature. Don't forget to enable it on your database until running this code !
// For instance, use this SQL statement on your server database : ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON)
// Otherwise, if you don't want to use Change Tracking feature, just change 'SqlSyncChangeTrackingProvider' to 'SqlSyncProvider'
var serverProvider = new SqlSyncChangeTrackingProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, tables);
agent.Options.BatchSize = 20;
// Using the IProgress<T> pattern to handle progession dring the synchronization
// Be careful, Progress<T> is not synchronous. Use SynchronousProgress<T> instead !
var progress = new SynchronousProgress <ProgressArgs>(args => Console.WriteLine($"{args.PogressPercentageString}:\t{args.Message}"));
// --------------------------------------------
// Using Interceptors
// --------------------------------------------
// CancellationTokenSource is used to cancel a sync process in the next example
var cts = new CancellationTokenSource();
// Intercept a table changes selecting
// Because the changes are not yet selected, we can easily interrupt the process with the cancellation token
agent.LocalOrchestrator.OnTableChangesSelecting(args =>
{
Console.WriteLine($"-------- Getting changes from table {args.Table.GetFullName()} ...");
if (args.Table.TableName == "Table_That_Should_Not_Be_Sync")
{
cts.Cancel();
}
});
// Intercept a table changes applying with a particular state [Upsert] or [Deleted]
// The rows included in the args.Changes table will be applied right after.
agent.LocalOrchestrator.OnTableChangesBatchApplying(args =>
{
Console.WriteLine($"-------- Applying changes {args.State} to table {args.Changes.GetFullName()} ...");
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
});
// Intercept a table changes selected.
// The rows included in the args.Changes have been selected from the datasource and will be sent to the server
agent.LocalOrchestrator.OnTableChangesSelected(args =>
{
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
});
// ------------------------
// Because we are in a TCP mode, we can hook the server side events
// In an HTTP mode, the code below won't work
// ------------------------
agent.RemoteOrchestrator.OnTableChangesSelecting(args =>
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"-------- Getting changes from table {args.Table.GetFullName()} ...");
Console.ResetColor();
});
agent.RemoteOrchestrator.OnTableChangesSelected(args =>
{
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
Console.ForegroundColor = ConsoleColor.Yellow;
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
Console.ResetColor();
});
agent.RemoteOrchestrator.OnTableChangesBatchApplying(args =>
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"-------- Applying changes {args.State} to table {args.Changes.GetFullName()} ...");
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
Console.ResetColor();
});
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(SyncType.Normal, cts.Token, progress);
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task SynchronizeMultiScopesAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncChangeTrackingProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
// Create 2 tables list (one for each scope)
string[] productScopeTables = new string[] { "ProductCategory", "ProductModel", "Product" };
string[] customersScopeTables = new string[] { "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// Create 2 sync setup with named scope
var setupProducts = new SyncSetup(productScopeTables, "productScope");
var setupCustomers = new SyncSetup(customersScopeTables, "customerScope");
// Create 2 agents, one for each scope
var agentProducts = new SyncAgent(clientProvider, serverProvider, setupProducts);
var agentCustomers = new SyncAgent(clientProvider, serverProvider, setupCustomers);
// Using the Progress pattern to handle progession during the synchronization
// We can use the same progress for each agent
var progress = new SynchronousProgress <ProgressArgs>(s =>
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}");
Console.ResetColor();
});
var remoteProgress = new SynchronousProgress <ProgressArgs>(s =>
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}");
Console.ResetColor();
});
// Spying what's going on the server side
agentProducts.AddRemoteProgress(remoteProgress);
agentCustomers.AddRemoteProgress(remoteProgress);
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
Console.WriteLine("Hit 1 for sync Products. Hit 2 for sync customers and sales");
var k = Console.ReadKey().Key;
if (k == ConsoleKey.D1)
{
Console.WriteLine("Sync Products:");
var s1 = await agentProducts.SynchronizeAsync(progress);
Console.WriteLine(s1);
}
else
{
Console.WriteLine("Sync Customers and Sales:");
var s1 = await agentCustomers.SynchronizeAsync(progress);
Console.WriteLine(s1);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task SynchronizeAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
// First provider is using the Sql change tracking feature. Don't forget to enable it on your database until running this code !
// For instance, use this SQL statement on your server database : ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON)
// Otherwise, if you don't want to use Change Tracking feature, just change 'SqlSyncChangeTrackingProvider' to 'SqlSyncProvider'
var serverProvider = new SqlSyncChangeTrackingProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var setup = new SyncSetup("ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail");
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
agent.Options.BatchSize = 20;
// Using the IProgress<T> pattern to handle progession dring the synchronization
// Be careful, Progress<T> is not synchronous. Use SynchronousProgress<T> instead !
var progress = new SynchronousProgress <ProgressArgs>(args => Console.WriteLine($"{args.ProgressPercentage:p}:\t{args.Message}"));
// --------------------------------------------
// Using Interceptors
// --------------------------------------------
// CancellationTokenSource is used to cancel a sync process in the next example
var cts = new CancellationTokenSource();
// Intercept a table changes selecting
// Because the changes are not yet selected, we can easily interrupt the process with the cancellation token
agent.LocalOrchestrator.OnTableChangesSelecting(args =>
{
Console.WriteLine($"-------- Getting changes from table {args.SchemaTable.GetFullName()} ...");
if (args.SchemaTable.TableName == "Table_That_Should_Not_Be_Sync")
{
cts.Cancel();
}
});
// Row has been selected from datasource.
// You can change the synrow before the row is serialized on the disk.
agent.LocalOrchestrator.OnRowsChangesSelected(args =>
{
Console.Write(".");
});
// Tables changes have been selected
// we can have all the batch part infos generated
agent.RemoteOrchestrator.OnTableChangesSelected(tcsa =>
{
Console.WriteLine($"Table {tcsa.SchemaTable.GetFullName()}: " +
$"Files generated count:{tcsa.BatchPartInfos.Count()}. " +
$"Rows Count:{tcsa.TableChangesSelected.TotalChanges}");
});
// This event is raised when a table is applying some rows, available on the disk
agent.LocalOrchestrator.OnTableChangesApplying(args =>
{
Console.WriteLine($"Table {args.SchemaTable.GetFullName()}: " +
$"Applying changes from {args.BatchPartInfos.Count()} files. " +
$"{args.BatchPartInfos.Sum(bpi => bpi.RowsCount)} rows.");
});
// This event is raised for each batch rows (maybe 1 or more in each batch)
// that will be applied on the datasource
// You can change something to the rows before they are applied here
agent.LocalOrchestrator.OnRowsChangesApplying(args =>
{
foreach (var syncRow in args.SyncRows)
{
Console.Write(".");
}
});
// This event is raised once all rows for a table have been applied
agent.LocalOrchestrator.OnTableChangesApplied(args =>
{
Console.WriteLine();
Console.WriteLine($"Table applied: ");
});
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(SyncOptions.DefaultScopeName, setup, SyncType.Normal, null, cts.Token, progress);
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
/// <summary>
/// Launch a simple sync, over TCP network, each sql server (client and server are reachable through TCP cp
/// </summary>
/// <returns></returns>
private static async Task SynchronizeAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncChangeTrackingProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
//var clientProvider = new SqliteSyncProvider("client2.db");
// specific Setup with only 2 tables, and one filtered
var setup = new SyncSetup(allTables);
// ----------------------------------------------------
// Vertical Filter: On columns. Removing columns from source
// ----------------------------------------------------
// Add a table with less columns
setup.Tables["Product"]
.Columns.AddRange(new string[] { "ProductId", "Name", "ProductCategoryID", "ProductNumber", "StandardCost", "ListPrice", "SellStartDate", "rowguid", "ModifiedDate" });
// ----------------------------------------------------
// Horizontal Filter: On rows. Removing rows from source
// ----------------------------------------------------
// Over all filter : "we Want only customer from specific city and specific postal code"
// First level table : Address
// Second level tables : CustomerAddress
// Third level tables : Customer, SalesOrderHeader
// Fourth level tables : SalesOrderDetail
// Create a filter on table Address on City Washington
// Optional : Sub filter on PostalCode, for testing purpose
var addressFilter = new SetupFilter("Address");
// For each filter, you have to provider all the input parameters
// A parameter could be a parameter mapped to an existing colum : That way you don't have to specify any type, length and so on ...
// We can specify if a null value can be passed as parameter value : That way ALL addresses will be fetched
// A default value can be passed as well, but works only on SQL Server (MySql is a damn shity thing)
addressFilter.AddParameter("City", "Address", true);
// Or a parameter could be a random parameter bound to anything. In that case, you have to specify everything
// (This parameter COULD BE bound to a column, like City, but for the example, we go for a custom parameter)
addressFilter.AddParameter("postal", DbType.String, true, null, 20);
// Then you map each parameter on wich table / column the "where" clause should be applied
addressFilter.AddWhere("City", "Address", "City");
addressFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(addressFilter);
var addressCustomerFilter = new SetupFilter("CustomerAddress");
addressCustomerFilter.AddParameter("City", "Address", true);
addressCustomerFilter.AddParameter("postal", DbType.String, true, null, 20);
// You can join table to go from your table up (or down) to your filter table
addressCustomerFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
// And then add your where clauses
addressCustomerFilter.AddWhere("City", "Address", "City");
addressCustomerFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(addressCustomerFilter);
var customerFilter = new SetupFilter("Customer");
customerFilter.AddParameter("City", "Address", true);
customerFilter.AddParameter("postal", DbType.String, true, null, 20);
customerFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
customerFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
customerFilter.AddWhere("City", "Address", "City");
customerFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(customerFilter);
var orderHeaderFilter = new SetupFilter("SalesOrderHeader");
orderHeaderFilter.AddParameter("City", "Address", true);
orderHeaderFilter.AddParameter("postal", DbType.String, true, null, 20);
orderHeaderFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderHeaderFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
orderHeaderFilter.AddWhere("City", "Address", "City");
orderHeaderFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(orderHeaderFilter);
var orderDetailsFilter = new SetupFilter("SalesOrderDetail");
orderDetailsFilter.AddParameter("City", "Address", true);
orderDetailsFilter.AddParameter("postal", DbType.String, true, null, 20);
orderDetailsFilter.AddJoin(Join.Left, "SalesOrderHeader").On("SalesOrderHeader", "SalesOrderID", "SalesOrderHeader", "SalesOrderID");
orderDetailsFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderDetailsFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
orderDetailsFilter.AddWhere("City", "Address", "City");
orderDetailsFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(orderDetailsFilter);
// ----------------------------------------------------
// Add pref suf
setup.StoredProceduresPrefix = "s";
setup.StoredProceduresSuffix = "";
setup.TrackingTablesPrefix = "t";
setup.TrackingTablesSuffix = "";
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, setup);
// Using the Progress pattern to handle progession during the synchronization
var progress = new SynchronousProgress <ProgressArgs>(s =>
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}");
Console.ResetColor();
});
var remoteProgress = new SynchronousProgress <ProgressArgs>(s =>
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}");
Console.ResetColor();
});
agent.AddRemoteProgress(remoteProgress);
//agent.Options.BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "sync");
agent.Options.BatchSize = 1000;
agent.Options.CleanMetadatas = true;
agent.Options.UseBulkOperations = true;
agent.Options.DisableConstraintsOnApplyChanges = false;
//agent.Options.ConflictResolutionPolicy = ConflictResolutionPolicy.ServerWins;
//agent.Options.UseVerboseErrors = false;
//agent.Options.ScopeInfoTableName = "tscopeinfo";
//agent.OnApplyChangesFailed(acf =>
//{
// // Check conflict is correctly set
// var localRow = acf.Conflict.LocalRow;
// var remoteRow = acf.Conflict.RemoteRow;
// // Merge row
// acf.Resolution = ConflictResolution.MergeRow;
// acf.FinalRow["Name"] = "Prout";
//});
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
// Launch the sync process
if (!agent.Parameters.Contains("City"))
{
agent.Parameters.Add("City", "Toronto");
}
if (!agent.Parameters.Contains("postal"))
{
agent.Parameters.Add("postal", DBNull.Value);
}
var s1 = await agent.SynchronizeAsync(progress);
// Write results
Console.WriteLine(s1);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
//Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
static void Main(string[] args)
{
var serverSideConnStr = "Data Source=192.168.137.1;Initial Catalog=BtlCore_HangZhouLiLong;user id=sa;password=P@ssw0rd;MultipleActiveResultSets=True;Connect Timeout=120;persist security info=True;";
var clientSideConnStr = "Data Source=192.168.137.1;Initial Catalog=BtlCore_HangZhouLiLong_Middle;user id=sa;password=P@ssw0rd;MultipleActiveResultSets=True;Connect Timeout=120;persist security info=True;";
// First provider on the server side, is using the Sql change tracking feature.
var serverProvider = new SqlSyncChangeTrackingProvider(serverSideConnStr);
// IF you want to try with a MySql Database, use the [MySqlSyncProvider] instead
// var serverProvider = new MySqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncChangeTrackingProvider(clientSideConnStr);
// Tables involved in the sync process:
var tables = new string[] { "TraceCatalogs" };
var syncSetup = new SyncSetup(tables);
syncSetup.Tables["TraceCatalogs"].SyncDirection = SyncDirection.DownloadOnly;
var clientOptions = new SyncOptions {
BatchSize = 500
};
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, clientOptions, tables);
// Intercept a table changes selecting stage.
agent.LocalOrchestrator.OnTableChangesSelecting(args =>
{
Console.WriteLine($"-- Getting changes from table {args.Table.GetFullName()} ...");
});
// Intercept a table changes applying stage, with a particular state [Upsert] or [Deleted]
// The rows included in the args.Changes table will be applied right after.
agent.LocalOrchestrator.OnTableChangesBatchApplying(args =>
{
Console.WriteLine($"-- Applying changes {args.State} to table {args.Changes.GetFullName()}");
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
if (args.State == System.Data.DataRowState.Deleted)
{
args.Cancel = true;
}
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
});
do
{
// Launch the sync process
var s1 = agent.SynchronizeAsync().Result;
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
