/// <summary>
/// Create a localorchestrator, and get changes that should be sent to server
/// </summary>
private static async Task GetServerChangesToSendToClientAsync()
{
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Make a first sync to be sure everything is in place
var agent = new SyncAgent(clientProvider, serverProvider, Config.GetClientOptions(), Config.GetSetup());
// Making a first sync, will initialize everything we need
await agent.SynchronizeAsync();
// Get the orchestrators (you can create a new instance as well)
var localOrchestrator = agent.LocalOrchestrator;
var remoteOrchestrator = agent.RemoteOrchestrator;
// Create a productcategory item
await Helper.InsertOneProductCategoryAsync(serverProvider.CreateConnection(), "New Product Category 2");
await Helper.InsertOneProductCategoryAsync(serverProvider.CreateConnection(), "New Product Category 3");
await Helper.InsertOneCustomerAsync(serverProvider.CreateConnection(), "John", "Doe");
await Helper.InsertOneCustomerAsync(serverProvider.CreateConnection(), "Sébastien", "Pertus");
// Get client scope
var clientScope = await localOrchestrator.GetClientScopeAsync();
// Simulate a full get changes (initialization step)
// clientScope.IsNewScope = true;
// Get changes to be populated to the server
var changes = await remoteOrchestrator.GetChangesAsync(clientScope : clientScope, progress : Config.GetProgress());
// enumerate changes retrieved
foreach (var tableChanges in changes.ServerChangesSelected.TableChangesSelected)
{
var enumerableOfTables = changes.ServerBatchInfo.GetTableAsync(tableChanges.TableName, tableChanges.SchemaName);
var enumeratorOfTable = enumerableOfTables.GetAsyncEnumerator();
while (await enumeratorOfTable.MoveNextAsync())
{
var table = enumeratorOfTable.Current;
Console.ForegroundColor = ConsoleColor.White;
Console.WriteLine($"Changes for table {table.GetFullName()}");
Console.ResetColor();
foreach (var row in table.Rows)
{
Console.WriteLine(row);
}
}
}
}
/// <summary>
/// Create the tables in each database
/// Add some datas in each database
/// Performs an optional DeprovisionAsync, to be sure we are starting from scratch
/// </summary>
private static async Task SetupDatabasesAsync(SqlSyncProvider serverProvider, SqliteSyncProvider clientProvider)
{
// Add some datas in both
var serverConnection = serverProvider.CreateConnection();
await Helper.CreateSqlServerServiceTicketsTableAsync(serverConnection);
var clientConnection = clientProvider.CreateConnection();
await Helper.CreateSqliteServiceTicketsTableAsync(clientConnection);
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
// Be sure we don't have an already existing sync setup. (from previous run)
await agent.LocalOrchestrator.DropAllAsync();
await agent.RemoteOrchestrator.DropAllAsync();
// Be sure we don't have existing rows (from previous run)
await Helper.DropRowsAsync(serverConnection);
await Helper.DropRowsAsync(clientConnection);
// Add rows
await Helper.AddRowsAsync(serverConnection);
await Helper.AddRowsAsync(clientConnection);
}
/// <summary>
/// Create a localorchestrator, and get changes that should be sent to server
/// </summary>
private static async Task GetServerChangesToSendToClientAsync()
{
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
var setup = Config.GetSetup();
// Make a first sync to be sure everything is in place
var agent = new SyncAgent(clientProvider, serverProvider, Config.GetClientOptions());
// Making a first sync, will initialize everything we need
await agent.SynchronizeAsync(setup);
// Get the orchestrators (you can create a new instance as well)
var localOrchestrator = agent.LocalOrchestrator;
var remoteOrchestrator = agent.RemoteOrchestrator;
// Create a productcategory item
await Helper.InsertOneProductCategoryAsync(serverProvider.CreateConnection(), "New Product Category 4");
await Helper.InsertOneProductCategoryAsync(serverProvider.CreateConnection(), "New Product Category 5");
await Helper.InsertOneCustomerAsync(serverProvider.CreateConnection(), "Jane", "Doe");
await Helper.InsertOneCustomerAsync(serverProvider.CreateConnection(), "Lisa", "Doe");
// Get client scope
var clientScope = await localOrchestrator.GetClientScopeInfoAsync();
// Get changes to be populated to the server
var changes = await remoteOrchestrator.GetChangesAsync(clientScope : clientScope, progress : Config.GetProgress());
// enumerate changes retrieved
foreach (var tableChanges in changes.ServerChangesSelected.TableChangesSelected)
{
var syncTable = await remoteOrchestrator.LoadTableFromBatchInfoAsync(changes.ServerBatchInfo, tableChanges.TableName, tableChanges.SchemaName);
Console.ForegroundColor = ConsoleColor.White;
Console.WriteLine($"Changes for table {syncTable.TableName}. Rows:{syncTable.Rows.Count}");
Console.ResetColor();
foreach (var row in syncTable.Rows)
{
Console.WriteLine(row);
}
}
}
private static async Task PreventDeletionAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var tables = new string[] { "Product" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
// First sync to have some rows on client
var s1 = await agent.SynchronizeAsync(tables);
// Write results
Console.WriteLine(s1);
// do not delete product row. it's your choice !
agent.LocalOrchestrator.OnTableChangesApplying(args =>
{
if (args.State == DataRowState.Deleted && args.SchemaTable.TableName == "Product")
{
Console.WriteLine($"Preventing deletion on {args.BatchPartInfos.Sum(bpi => bpi.RowsCount)} rows.");
args.Cancel = true;
}
});
var c = serverProvider.CreateConnection();
var cmd = c.CreateCommand();
cmd.Connection = c;
cmd.CommandText = "DELETE FROM Product WHERE ProductId >= 750 AND ProductId < 760";
c.Open();
cmd.ExecuteNonQuery();
c.Close();
// Second sync
s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
// Third sync
s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
}
private static async Task ConflictAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(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);
Console.WriteLine("- Initialize the databases with initial data");
// Make a first sync to have everything in place
Console.WriteLine(await agent.SynchronizeAsync(SyncType.Reinitialize));
Console.WriteLine("- Insert data in client and server databases to generate a conflict Insert Client - Insert Server");
var id = new Random(50000).Next();
// Insert a value on client
await Helper.InsertNConflictsCustomerAsync(clientProvider.CreateConnection(), 10, id, "John", "Clientdoe");
// Insert a value on server with same key, to generate a conflict
await Helper.InsertNConflictsCustomerAsync(serverProvider.CreateConnection(), 10, id, "John", "Serverdoe");
do
{
try
{
Console.WriteLine("- Launch synchronization");
var res = await agent.SynchronizeAsync();
Console.WriteLine(res);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task SynchronizeThenDeprovisionThenProvisionAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Create standard Setup and Options
var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress" });
var options = new SyncOptions();
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, options, setup);
// Using the Progress pattern to handle progession during the synchronization
var progress = new SynchronousProgress <ProgressArgs>(args => Console.WriteLine($"{args.ProgressPercentage:p}:\t{args.Message}"));
// First sync to have a starting point
var s1 = await agent.SynchronizeAsync(progress);
Console.WriteLine(s1);
// -----------------------------------------------------------------
// Migrating a table by adding a new column
// -----------------------------------------------------------------
// Adding a new column called CreatedDate to Address table, on the server, and on the client.
await Helper.AddNewColumnToAddressAsync(serverProvider.CreateConnection());
await Helper.AddNewColumnToAddressAsync(clientProvider.CreateConnection());
// -----------------------------------------------------------------
// Server side
// -----------------------------------------------------------------
// Creating a setup regarding only the table Address
var setupAddress = new SyncSetup(new string[] { "Address" });
// Create a server orchestrator used to Deprovision and Provision only table Address
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setupAddress);
// Unprovision the old Address triggers / stored proc.
// We can conserve the Address tracking table, since we just add a column,
// that is not a primary key used in the tracking table
// That way, we are preserving historical data
await remoteOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the new Address triggers / stored proc again,
// This provision method will fetch the address schema from the database,
// so it will contains all the columns, including the new Address column added
await remoteOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// -----------------------------------------------------------------
// Client side
// -----------------------------------------------------------------
// Now go for local orchestrator
var localOrchestrator = new LocalOrchestrator(clientProvider, options, setupAddress);
// Unprovision the Address triggers / stored proc. We can conserve tracking table, since we just add a column, that is not a primary key used in the tracking table
// In this case, we will
await localOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the Address triggers / stored proc again,
// This provision method will fetch the address schema from the database, so it will contains all the columns, including the new one added
await localOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Now test a new sync, everything should work as expected.
do
{
// Console.Clear();
Console.WriteLine("Sync Start");
try
{
var s2 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s2);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task OutDatedAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(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);
Console.WriteLine("- Initialize the databases with initial data");
// Make a first sync to have everything in place
Console.WriteLine(await agent.SynchronizeAsync(SyncType.Reinitialize));
// Call a server delete metadata to update the last valid timestamp value in scope_info_server table
var dmc = await agent.RemoteOrchestrator.DeleteMetadatasAsync();
Console.WriteLine("- Insert data in client database and then generate an out dated scenario");
// Insert a value on client
await Helper.InsertOneCustomerAsync(clientProvider.CreateConnection(), "John", "Doe");
// Simulate an outdated situation in the local database
await Helper.SimulateOutDateScenarioAsync(clientProvider.CreateConnection(), dmc.TimestampLimit - 1);
// Action when outdate occurs
agent.LocalOrchestrator.OnOutdated(oa =>
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("local database is too old to synchronize with the server.");
Console.ResetColor();
Console.WriteLine("Do you want to synchronize anyway, and potentially lost data ? ");
Console.Write("Enter a value ('r' for reinitialize or 'ru' for reinitialize with upload): ");
var answer = Console.ReadLine();
if (answer.ToLowerInvariant() == "r")
{
oa.Action = OutdatedAction.Reinitialize;
}
else if (answer.ToLowerInvariant() == "ru")
{
oa.Action = OutdatedAction.ReinitializeWithUpload;
}
});
do
{
try
{
Console.WriteLine("- Launch synchronization");
var res = await agent.SynchronizeAsync();
Console.WriteLine(res);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task SynchronizeThenDeprovisionThenProvisionAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName));
// Create standard Setup and Options
var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress" });
var options = new SyncOptions();
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, options, setup);
// Using the Progress pattern to handle progession during the synchronization
var progress = new SynchronousProgress <ProgressArgs>(s => Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}"));
// First sync to have a starting point
var s1 = await agent.SynchronizeAsync(progress);
Console.WriteLine(s1);
// -----------------------------------------------------------------
// Migrating a table by adding a new column
// -----------------------------------------------------------------
// Adding a new column called CreatedDate to Address table, on the server, and on the client.
await AddNewColumnToAddressAsync(serverProvider.CreateConnection());
await AddNewColumnToAddressAsync(clientProvider.CreateConnection());
// -----------------------------------------------------------------
// Server side
// -----------------------------------------------------------------
// Creating a setup regarding only the table Address
var setupAddress = new SyncSetup(new string[] { "Address" });
// Create a server orchestrator used to Deprovision and Provision only table Address
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setupAddress);
// Unprovision the Address triggers / stored proc.
// We can conserve the Address tracking table, since we just add a column,
// that is not a primary key used in the tracking table
// That way, we are preserving historical data
await remoteOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the Address triggers / stored proc again,
// This provision method will fetch the address schema from the database,
// so it will contains all the columns, including the new Address column added
await remoteOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Now we need the full setup to get the full schema.
// Setup includes [Address] [Customer] and [CustomerAddress]
remoteOrchestrator.Setup = setup;
var newSchema = await remoteOrchestrator.GetSchemaAsync();
// Now we need to save this new schema to the serverscope table
// get the server scope again
var serverScope = await remoteOrchestrator.GetServerScopeAsync();
// affect good values
serverScope.Setup = setup;
serverScope.Schema = newSchema;
// save it
await remoteOrchestrator.WriteServerScopeAsync(serverScope);
// -----------------------------------------------------------------
// Client side
// -----------------------------------------------------------------
// Now go for local orchestrator
var localOrchestrator = new LocalOrchestrator(clientProvider, options, setupAddress);
// Unprovision the Address triggers / stored proc. We can conserve tracking table, since we just add a column, that is not a primary key used in the tracking table
// In this case, we will
await localOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the Address triggers / stored proc again,
// This provision method will fetch the address schema from the database, so it will contains all the columns, including the new one added
await localOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Now we need to save this to clientscope
// get the server scope again
var clientScope = await localOrchestrator.GetClientScopeAsync();
// At this point, if you need the schema and you are not able to create a RemoteOrchestrator,
// You can create a WebClientOrchestrator and get the schema as well
// var proxyClientProvider = new WebClientOrchestrator("https://localhost:44369/api/Sync");
// var newSchema = proxyClientProvider.GetSchemaAsync();
// affect good values
clientScope.Setup = setup;
clientScope.Schema = newSchema;
// save it
await localOrchestrator.WriteClientScopeAsync(clientScope);
// Now test a new sync, everything should work as expected.
do
{
// Console.Clear();
Console.WriteLine("Sync Start");
try
{
var s2 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s2);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task SpyWhenSyncAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
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);
// First sync to initialize everything
var s1 = await agent.SynchronizeAsync(tables);
// Write results
Console.WriteLine(s1);
// Make some changes on the server side
var sc = serverProvider.CreateConnection();
await Helper.InsertOneCustomerAsync(sc, "John", "Doe");
await Helper.InsertOneProductCategoryAsync(sc, Guid.NewGuid(), "Shoes 2020" + Path.GetRandomFileName());
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113141);
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113142);
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113143);
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113144);
// Get local orchestrator to get some info during sync
var localOrchestrator = agent.LocalOrchestrator;
localOrchestrator.OnDatabaseChangesSelecting(args =>
{
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"Getting changes from local database:");
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"BatchDirectory: {args.BatchDirectory}. BatchSize: {args.BatchSize}.");
});
localOrchestrator.OnTableChangesSelecting(args =>
{
});
// Just before applying something locally, at the database level
localOrchestrator.OnDatabaseChangesApplying(async args =>
{
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"Applying changes to the local database:");
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"Last timestamp used to compare local rows : {args.ApplyChanges.LastTimestamp}");
Console.WriteLine("List of ALL rows to be sync locally:");
foreach (var table in args.ApplyChanges.Schema.Tables)
{
var syncTable = await localOrchestrator.LoadTableFromBatchInfoAsync(args.ApplyChanges.BatchInfo, table.TableName, table.SchemaName);
Console.WriteLine($"Changes for table {table.TableName}. Rows:{syncTable.Rows.Count}");
foreach (var row in syncTable.Rows)
{
Console.WriteLine(row);
}
Console.WriteLine();
}
});
// Just before applying changes locally, at the table level
localOrchestrator.OnTableChangesApplying(async args =>
{
if (args.BatchPartInfos != null)
{
var syncTable = await localOrchestrator.LoadTableFromBatchInfoAsync(
args.BatchInfo, args.SchemaTable.TableName, args.SchemaTable.SchemaName, args.State);
if (syncTable.HasRows)
{
Console.WriteLine($"- --------------------------------------------");
Console.WriteLine($"- Applying [{args.State}] changes to Table {args.SchemaTable.GetFullName()}");
Console.WriteLine($"Changes for table {args.SchemaTable.TableName}. Rows:{syncTable.Rows.Count}");
foreach (var row in syncTable.Rows)
{
Console.WriteLine(row);
}
}
}
});
localOrchestrator.OnRowsChangesApplying(async args =>
{
Console.WriteLine($"- --------------------------------------------");
Console.WriteLine($"- In memory rows that are going to be Applied");
foreach (var row in args.SyncRows)
{
Console.WriteLine(row);
}
Console.WriteLine();
});
// Once changes are applied
localOrchestrator.OnTableChangesApplied(args =>
{
Console.WriteLine($"- Applied [{args.TableChangesApplied.State}] to table [{args.TableChangesApplied.TableName}]: Applied:{args.TableChangesApplied.Applied}. Failed:{args.TableChangesApplied.Failed}. Conflicts:{args.TableChangesApplied.ResolvedConflicts}. ");
});
// Just before applying something locally, at the database level
localOrchestrator.OnDatabaseChangesApplied(args =>
{
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"Changes applied to the local database:");
Console.WriteLine($"--------------------------------------------");
Console.WriteLine(args.ChangesApplied);
});
// Launch the sync process
var s2 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s2);
}
private static async Task ConflictAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(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[] { "Customer" };
var setup = new SyncSetup(tables);
setup.Tables["Customer"].Columns.AddRange(new string[] { "CustomerID", "FirstName", "LastName" });
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
Console.WriteLine("- Initialize the databases with initial data");
// Make a first sync to have everything in place
Console.WriteLine(await agent.SynchronizeAsync(setup));
do
{
try
{
Console.WriteLine("-----------------------------------");
Console.WriteLine("- Insert data in client and server databases to generate a conflict Insert Client - Insert Server");
var id = new Random((int)DateTime.Now.Ticks).Next();
// Insert a value on client
await Helper.InsertOneConflictCustomerAsync(
clientProvider.CreateConnection(), id, "John", "Clientdoe");
// Insert a value on server with same key, to generate a conflict
await Helper.InsertOneConflictCustomerAsync(
serverProvider.CreateConnection(), id, "John", "Serverdoe");
agent.OnApplyChangesFailed(acfa =>
{
Console.WriteLine("______________________________");
Console.WriteLine("Handling conflict:");
Console.WriteLine($"Server row : {acfa.Conflict.RemoteRow}");
Console.WriteLine($"Client row : {acfa.Conflict.LocalRow}");
Console.WriteLine("Please use which one is the winner of the conflict:");
Console.WriteLine("* 1: Server Wins");
Console.WriteLine("* 2: Client Wins");
Console.WriteLine("* 3: Merge Row");
var choose = Console.ReadLine();
if (choose == "1")
{
acfa.Resolution = ConflictResolution.ServerWins;
}
else if (choose == "2")
{
acfa.Resolution = ConflictResolution.ClientWins;
}
else
{
acfa.Resolution = ConflictResolution.MergeRow;
acfa.FinalRow["LastName"] = "MergedDoe";
}
});
Console.WriteLine("- Launch synchronization");
var res = await agent.SynchronizeAsync();
Console.WriteLine(res);
var clientRow = await Helper.GetCustomerAsync(clientProvider.CreateConnection(), id);
Console.WriteLine("Client row:");
Console.WriteLine(clientRow);
var serverRow = await Helper.GetCustomerAsync(serverProvider.CreateConnection(), id);
Console.WriteLine("Server row:");
Console.WriteLine(serverRow);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public void BuilderTable_CreateTrackingTable()
{
var provider = new SqlSyncProvider(clientConnectionString);
using (var connection = provider.CreateConnection())
{
var options = DbBuilderOption.CreateOrUseExistingSchema;
var builder = provider.GetDatabaseBuilder(set.Tables["Products"], options);
var tableBuilder = builder.CreateTrackingTableBuilder(connection);
tableBuilder.TableDescription = builder.TableDescription;
connection.Open();
// Check if we need to create the tables
tableBuilder.CreateTable();
tableBuilder.CreatePk();
tableBuilder.CreateIndex();
connection.Close();
}
// Check result
using (var connection = new SqlConnection(clientConnectionString))
{
var table = set.Tables["Products"];
// Check Columns
var commandColumn = $"Select col.name as name, col.column_id, typ.name as [type], col.max_length, col.precision, col.scale, col.is_nullable, col.is_identity from sys.columns as col " +
$"Inner join sys.tables as tbl on tbl.object_id = col.object_id " +
$"Inner Join sys.systypes typ on typ.xusertype = col.system_type_id " +
$"Where tbl.name = @tableName " +
$"Order by col.column_id";
ObjectNameParser tableNameParser = new ObjectNameParser(table.TableName + "_tracking");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (SqlCommand sqlCommand = new SqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
connection.Open();
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
connection.Close();
}
// Check columns number
Assert.Equal(10, dmTable.Rows.Count);
var rows = dmTable.Rows.OrderBy(r => (int)r["column_id"]).ToList();
var c = rows[0];
var name = c["name"].ToString();
Assert.Equal("Id", name);
c = rows[1];
name = c["name"].ToString();
Assert.Equal("name", name);
c = rows[2];
name = c["name"].ToString();
Assert.Equal("salary", name);
c = rows[3];
name = c["name"].ToString();
Assert.Equal("create_scope_id", name);
c = rows[4];
name = c["name"].ToString();
Assert.Equal("update_scope_id", name);
c = rows[5];
name = c["name"].ToString();
Assert.Equal("create_timestamp", name);
c = rows[6];
name = c["name"].ToString();
Assert.Equal("update_timestamp", name);
c = rows[7];
name = c["name"].ToString();
Assert.Equal("timestamp", name);
c = rows[8];
name = c["name"].ToString();
Assert.Equal("sync_row_is_tombstone", name);
c = rows[9];
name = c["name"].ToString();
Assert.Equal("last_change_datetime", name);
}
}
public void BuilderTable_CreateTable()
{
var provider = new SqlSyncProvider(clientConnectionString);
using (var connection = provider.CreateConnection())
{
var options = DbBuilderOption.CreateOrUseExistingSchema;
var builder = provider.GetDatabaseBuilder(set.Tables["Products"], options);
var tableBuilder = builder.CreateTableBuilder(connection);
tableBuilder.TableDescription = builder.TableDescription;
connection.Open();
// Check if we need to create the tables
if (tableBuilder.NeedToCreateTable(options))
{
tableBuilder.CreateTable();
tableBuilder.CreatePrimaryKey();
tableBuilder.CreateForeignKeyConstraints();
}
connection.Close();
}
// Check result
using (var connection = new SqlConnection(clientConnectionString))
{
var table = set.Tables["Products"];
// Check Columns
var commandColumn = $"Select col.name as name, col.column_id, typ.name as [type], col.max_length, col.precision, col.scale, col.is_nullable, col.is_identity from sys.columns as col " +
$"Inner join sys.tables as tbl on tbl.object_id = col.object_id " +
$"Inner Join sys.systypes typ on typ.xusertype = col.system_type_id " +
$"Where tbl.name = @tableName " +
$"Order by col.column_id";
ObjectNameParser tableNameParser = new ObjectNameParser(table.TableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (SqlCommand sqlCommand = new SqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
connection.Open();
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
connection.Close();
}
// Check columns number
Assert.Equal(4, dmTable.Rows.Count);
var rows = dmTable.Rows.OrderBy(r => (int)r["column_id"]).ToList();
var c = rows[0];
var name = c["name"].ToString();
var ordinal = (int)c["column_id"];
var typeString = c["type"].ToString();
var maxLength = (Int16)c["max_length"];
var precision = (byte)c["precision"];
var scale = (byte)c["scale"];
var isNullable = (bool)c["is_nullable"];
var isIdentity = (bool)c["is_identity"];
Assert.Equal("Id", name);
Assert.False(isNullable);
Assert.True(isIdentity);
c = rows[1];
name = c["name"].ToString();
ordinal = (int)c["column_id"];
typeString = c["type"].ToString();
maxLength = (Int16)c["max_length"];
precision = (byte)c["precision"];
scale = (byte)c["scale"];
isNullable = (bool)c["is_nullable"];
isIdentity = (bool)c["is_identity"];
Assert.Equal("clientId", name);
Assert.True(isNullable);
Assert.False(isIdentity);
c = rows[2];
name = c["name"].ToString();
ordinal = (int)c["column_id"];
typeString = c["type"].ToString();
maxLength = (Int16)c["max_length"];
precision = (byte)c["precision"];
scale = (byte)c["scale"];
isNullable = (bool)c["is_nullable"];
isIdentity = (bool)c["is_identity"];
Assert.Equal("name", name);
Assert.False(isNullable);
Assert.Equal(300, maxLength);
Assert.Equal("nvarchar", typeString);
c = rows[3];
name = c["name"].ToString();
ordinal = (int)c["column_id"];
typeString = c["type"].ToString();
maxLength = (Int16)c["max_length"];
precision = (byte)c["precision"];
scale = (byte)c["scale"];
isNullable = (bool)c["is_nullable"];
isIdentity = (bool)c["is_identity"];
Assert.Equal("salary", name);
Assert.False(isNullable);
Assert.Equal(6, precision);
Assert.Equal(2, scale);
}
}
private static async Task SpyWhenSyncAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
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);
// First sync to initialize everything
var s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
// Make some changes on the server side
var sc = serverProvider.CreateConnection();
await Helper.InsertOneCustomerAsync(sc, "John", "Doe");
await Helper.InsertOneProductCategoryAsync(sc, "Shoes 2020");
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113141);
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113142);
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113143);
await Helper.DeleteOneSalesDetailOrderAsync(sc, 113144);
// Get local orchestrator to get some info during sync
var localOrchestrator = agent.LocalOrchestrator;
// Just before applying something locally, at the database level
localOrchestrator.OnDatabaseChangesApplying(args =>
{
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"Applying changes to the local database:");
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"Policy applied : {args.ApplyChanges.Policy}");
Console.WriteLine($"Last timestamp used to compare local rows : {args.ApplyChanges.LastTimestamp}");
Console.WriteLine("List of ALL rows to be sync locally:");
foreach (var table in args.ApplyChanges.Setup.Tables)
{
foreach (var tablePart in args.ApplyChanges.Changes.GetTable(table.TableName, table.SchemaName))
{
if (tablePart == null || !tablePart.HasRows)
{
continue;
}
foreach (var row in tablePart.Rows)
{
Console.WriteLine(row);
}
}
}
});
// Just before applying changes locally, at the table level
localOrchestrator.OnTableChangesApplying(args =>
{
if (args.Changes != null && args.Changes.HasRows)
{
Console.WriteLine($"- --------------------------------------------");
Console.WriteLine($"- Applying [{args.State}] changes to Table {args.Changes.GetFullName()}");
foreach (var row in args.Changes.Rows)
{
Console.WriteLine($"- {row}");
}
}
});
// Once changes are applied
localOrchestrator.OnTableChangesApplied(args =>
{
Console.WriteLine($"- Applied [{args.TableChangesApplied.State}] to table [{args.TableChangesApplied.TableName}]: Applied:{args.TableChangesApplied.Applied}. Failed:{args.TableChangesApplied.Failed}. Conflicts:{args.TableChangesApplied.ResolvedConflicts}. ");
});
// Just before applying something locally, at the database level
localOrchestrator.OnDatabaseChangesApplied(args =>
{
Console.WriteLine($"--------------------------------------------");
Console.WriteLine($"Changes applied to the local database:");
Console.WriteLine($"--------------------------------------------");
Console.WriteLine(args.ChangesApplied);
});
// Launch the sync process
var s2 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s2);
}
public static async Task SyncHttpThroughKestellAsync()
{
// server provider
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
// ----------------------------------
// Client side
// ----------------------------------
var clientOptions = new SyncOptions {
BatchSize = 500
};
var proxyClientProvider = new WebClientOrchestrator();
// ----------------------------------
// Web Server side
// ----------------------------------
var setup = new SyncSetup(allTables)
{
ScopeName = "all_tables_scope",
StoredProceduresPrefix = "s",
StoredProceduresSuffix = "",
TrackingTablesPrefix = "t",
TrackingTablesSuffix = "",
TriggersPrefix = "",
TriggersSuffix = "t"
};
// snapshot directory
var directory = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.UserProfile), "Snapshots");
// ----------------------------------
// Create a snapshot
// ----------------------------------
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"Creating snapshot");
var remoteOrchestrator = new RemoteOrchestrator(serverProvider);
await remoteOrchestrator.CreateSnapshotAsync(new SyncContext(), setup, directory, 500, CancellationToken.None);
Console.WriteLine($"Done.");
Console.ResetColor();
// ----------------------------------
// Insert a value after snapshot created
// ----------------------------------
using (var c = serverProvider.CreateConnection())
{
var command = c.CreateCommand();
command.CommandText = "INSERT INTO [dbo].[ProductCategory] ([Name]) VALUES ('Bikes revolution');";
c.Open();
command.ExecuteNonQuery();
c.Close();
}
var webServerOptions = new WebServerOptions {
SnapshotsDirectory = directory
};
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, proxyClientProvider);
agent.Options = clientOptions;
var configureServices = new Action <IServiceCollection>(services =>
{
services.AddSyncServer <SqlSyncProvider>(serverProvider.ConnectionString, setup, webServerOptions);
});
var serverHandler = new RequestDelegate(async context =>
{
var webProxyServer = context.RequestServices.GetService(typeof(WebProxyServerOrchestrator)) as WebProxyServerOrchestrator;
await webProxyServer.HandleRequestAsync(context);
});
using (var server = new KestrellTestServer(configureServices))
{
var clientHandler = new ResponseDelegate(async(serviceUri) =>
{
proxyClientProvider.ServiceUri = serviceUri;
do
{
Console.Clear();
Console.WriteLine("Web sync start");
try
{
var progress = new SynchronousProgress <ProgressArgs>(pa => Console.WriteLine($"{pa.Context.SyncStage}\t {pa.Message}"));
var s = await agent.SynchronizeAsync(progress);
Console.WriteLine(s);
}
catch (SyncException e)
{
Console.WriteLine(e.ToString());
}
catch (Exception e)
{
Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
}
Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
});
await server.Run(serverHandler, clientHandler);
}
}
private static async Task CreateSnapshotAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
var remoteOrchestrator = new RemoteOrchestrator(serverProvider);
// specific Setup with only 2 tables, and one filtered
var setup = new SyncSetup(allTables);
// snapshot directory
var directory = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.UserProfile), "Snapshots");
await remoteOrchestrator.CreateSnapshotAsync(new SyncContext(), setup, directory, 500, CancellationToken.None);
// client provider
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
// Insert a value after snapshot created
using (var c = serverProvider.CreateConnection())
{
var command = c.CreateCommand();
command.CommandText = "INSERT INTO [dbo].[ProductCategory] ([Name]) VALUES ('Bikes revolution');";
c.Open();
command.ExecuteNonQuery();
c.Close();
}
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, setup);
var syncOptions = new SyncOptions {
SnapshotsDirectory = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.UserProfile), "Snapshots")
};
// Setting the snapshots directory for client
agent.Options.SnapshotsDirectory = directory;
// 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();
});
//// Launch the sync process
//if (!agent.Parameters.Contains("City"))
// agent.Parameters.Add("City", "Toronto");
//if (!agent.Parameters.Contains("postal"))
// agent.Parameters.Add("postal", "NULL");
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
var s1 = await agent.SynchronizeAsync(progress);
Console.WriteLine(s1);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static void TestCreateTrackingTable(SqlSyncProvider provider)
{
DmSet set = new DmSet();
DmTable clientsTable = new DmTable("Clients");
DmTable productsTable = new DmTable("Products");
// orders matters !!
set.Tables.Add(clientsTable);
set.Tables.Add(productsTable);
DmColumn id = new DmColumn <Int32>("Id");
id.AllowDBNull = false;
id.AutoIncrement = true;
productsTable.Columns.Add(id);
DmColumn fkClientId = new DmColumn <Guid>("clientId");
fkClientId.AllowDBNull = true;
productsTable.Columns.Add(fkClientId);
DmColumn name = new DmColumn <string>("name");
name.AllowDBNull = true;
name.DbType = System.Data.DbType.StringFixedLength;
name.MaxLength = 150;
productsTable.Columns.Add(name);
DmColumn salary = new DmColumn <Decimal>("salary");
salary.AllowDBNull = false;
salary.DbType = System.Data.DbType.VarNumeric;
salary.Precision = 6;
salary.Scale = 2;
productsTable.Columns.Add(salary);
productsTable.PrimaryKey = new DmKey(new DmColumn[] { id, name, salary });
DmColumn clientId = new DmColumn <Guid>("Id");
clientId.AllowDBNull = false;
clientsTable.Columns.Add(clientId);
DmColumn clientName = new DmColumn <string>("Name");
clientsTable.Columns.Add(clientName);
clientsTable.PrimaryKey = new DmKey(clientId);
// ForeignKey
DmRelation fkClientRelation = new DmRelation("FK_Products_Clients", clientId, fkClientId);
productsTable.AddForeignKey(fkClientRelation);
DbConnection connection = null;
try
{
using (connection = provider.CreateConnection())
{
foreach (var table in set.Tables)
{
var builder = provider.GetDatabaseBuilder(table, DbBuilderOption.CreateOrUseExistingSchema);
if (table.TableName == "Clients")
{
builder.AddFilterColumn("Id");
}
if (table.TableName == "Products")
{
builder.AddFilterColumn("clientId");
}
builder.Apply(connection);
Console.WriteLine(builder.Script(connection));
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
return;
}
finally
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
