/// <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(); } } }