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");
}
public SqlBuilderTrackingTable(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup)
{
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.sqlDbMetadata = new NpgsqlDbMetadata();
}
public SqlBuilderTrigger(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, DbConnection connection, DbTransaction transaction = null)
{
this.connection = connection as NpgsqlConnection;
this.transaction = transaction as NpgsqlTransaction;
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.sqlObjectNames = new NpgsqlObjectNames(this.tableDescription, this.setup);
}
public async Task Table_Drop_One_Cancel()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
// Overwrite existing table with this new one
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var schema = new SyncSet();
schema.Tables.Add(table);
var localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var scopeInfo = await localOrchestrator.GetClientScopeInfoAsync();
scopeInfo.Setup = setup;
scopeInfo.Schema = schema;
await localOrchestrator.SaveClientScopeInfoAsync(scopeInfo);
// Call create a first time to have an existing table
var isCreated = await localOrchestrator.CreateTableAsync(scopeInfo, table.TableName, table.SchemaName);
Assert.True(isCreated);
// Ensuring we have a clean new instance
localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var onCreating = false;
var onCreated = false;
var onDropping = false;
var onDropped = false;
localOrchestrator.OnTableCreating(ttca => onCreating = true);
localOrchestrator.OnTableCreated(ttca => onCreated = true);
localOrchestrator.OnTableDropped(ttca => onDropped = true);
localOrchestrator.OnTableDropping(ttca =>
{
ttca.Cancel = true;
onDropping = true;
});
var isDropped = await localOrchestrator.DropTableAsync(scopeInfo, table.TableName, table.SchemaName);
Assert.True(onDropping);
Assert.False(isDropped);
Assert.False(onDropped);
Assert.False(onCreating);
Assert.False(onCreated);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var stable = await SqlManagementUtils.GetTableAsync(c, null, "Product", "SalesLT").ConfigureAwait(false);
Assert.Single(stable.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Trigger_Drop_One_Cancel()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.True(isCreated);
// Ensuring we have a clean new instance
remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTriggerCreating(tca => onCreating++);
remoteOrchestrator.OnTriggerCreated(tca => onCreated++);
remoteOrchestrator.OnTriggerDropping(tca =>
{
tca.Cancel = true;
onDropping++;
});
remoteOrchestrator.OnTriggerDropped(tca => onDropped++);
var isDropped = await remoteOrchestrator.DropTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.False(isDropped);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_insert_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public void SyncSetup_Compare_TwoSetup_With_Filters_ShouldBe_Equals()
{
SyncSetup setup1 = new SyncSetup(new string[] { "Customer", "Product", "ProductCategory", "Employee" });
SyncSetup setup2 = new SyncSetup(new string[] { "Customer", "Product", "ProductCategory", "Employee" });
setup1.Filters.Add("Customer", "CompanyName");
var addressCustomerFilter = new SetupFilter("CustomerAddress");
addressCustomerFilter.AddParameter("CompanyName", "Customer");
addressCustomerFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressCustomerFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup1.Filters.Add(addressCustomerFilter);
var addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CompanyName", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup1.Filters.Add(addressFilter);
var orderHeaderFilter = new SetupFilter("SalesOrderHeader");
orderHeaderFilter.AddParameter("CompanyName", "Customer");
orderHeaderFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderHeaderFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
orderHeaderFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup1.Filters.Add(orderHeaderFilter);
var orderDetailsFilter = new SetupFilter("SalesOrderDetail");
orderDetailsFilter.AddParameter("CompanyName", "Customer");
orderDetailsFilter.AddJoin(Join.Left, "SalesOrderHeader").On("SalesOrderDetail", "SalesOrderID", "SalesOrderHeader", "SalesOrderID");
orderDetailsFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderDetailsFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
orderDetailsFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup1.Filters.Add(orderDetailsFilter);
setup2.Filters.Add("Customer", "CompanyName");
var addressCustomerFilter2 = new SetupFilter("CustomerAddress");
addressCustomerFilter2.AddParameter("CompanyName", "Customer");
addressCustomerFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressCustomerFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
setup2.Filters.Add(addressCustomerFilter2);
var addressFilter2 = new SetupFilter("Address");
addressFilter2.AddParameter("CompanyName", "Customer");
addressFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
setup2.Filters.Add(addressFilter2);
var orderHeaderFilter2 = new SetupFilter("SalesOrderHeader");
orderHeaderFilter2.AddParameter("CompanyName", "Customer");
orderHeaderFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderHeaderFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
orderHeaderFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
setup2.Filters.Add(orderHeaderFilter2);
var orderDetailsFilter2 = new SetupFilter("SalesOrderDetail");
orderDetailsFilter2.AddParameter("CompanyName", "Customer");
orderDetailsFilter2.AddJoin(Join.Left, "SalesOrderHeader").On("SalesOrderDetail", "SalesOrderID", "SalesOrderHeader", "SalesOrderID");
orderDetailsFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderDetailsFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
orderDetailsFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
setup2.Filters.Add(orderDetailsFilter2);
Assert.True(setup1.EqualsByProperties(setup2));
}
public async Task Table_Create_All()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.ProductCategory", "SalesLT.ProductModel", "SalesLT.Product", "Posts" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var serverScope = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
// new empty db
dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
sqlProvider = new SqlSyncProvider(cs);
localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTableCreating(ttca => onCreating++);
localOrchestrator.OnTableCreated(ttca => onCreated++);
localOrchestrator.OnTableDropping(ttca => onDropping++);
localOrchestrator.OnTableDropped(ttca => onDropped++);
var scopeInfo = await localOrchestrator.GetClientScopeInfoAsync();
scopeInfo.Setup = serverScope.Setup;
scopeInfo.Schema = serverScope.Schema;
await localOrchestrator.SaveClientScopeInfoAsync(scopeInfo);
await localOrchestrator.CreateTablesAsync(scopeInfo);
Assert.Equal(4, onCreating);
Assert.Equal(4, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
onCreating = 0;
onCreated = 0;
onDropping = 0;
onDropped = 0;
await localOrchestrator.CreateTablesAsync(scopeInfo);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
await localOrchestrator.CreateTablesAsync(scopeInfo, true);
Assert.Equal(4, onCreating);
Assert.Equal(4, onCreated);
Assert.Equal(4, onDropping);
Assert.Equal(4, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public SqliteTableBuilder(SyncTable tableDescription, ParserName tableName, ParserName trackingTableName, SyncSetup setup) : base(tableDescription, tableName, trackingTableName, setup)
{
this.sqliteObjectNames = new SqliteObjectNames(tableDescription, this.TableName, this.TrackingTableName, setup);
this.sqliteDbMetadata = new SqliteDbMetadata();
}
public SqlTableBuilder(SyncTable tableDescription, SyncSetup setup) : base(tableDescription, setup)
{
this.ObjectNames = new SqlObjectNames(tableDescription, this.TableName, this.TrackingTableName, setup);
}
/// <summary>
/// Test a client syncing through a web api
/// </summary>
private static async Task SyncThroughWebApiAsync()
{
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
var handler = new HttpClientHandler {
AutomaticDecompression = DecompressionMethods.GZip
};
var client = new HttpClient(handler)
{
Timeout = TimeSpan.FromMinutes(5)
};
var proxyClientProvider = new WebClientOrchestrator("http://localhost:52288/api/Sync", null, null, client);
// ----------------------------------
// Client side
// ----------------------------------
var clientOptions = new SyncOptions
{
ScopeInfoTableName = "client_scopeinfo",
BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "sync_client"),
BatchSize = 50,
CleanMetadatas = true,
UseBulkOperations = true,
UseVerboseErrors = false,
};
var clientSetup = new SyncSetup
{
StoredProceduresPrefix = "cli",
StoredProceduresSuffix = "",
TrackingTablesPrefix = "cli",
TrackingTablesSuffix = "",
TriggersPrefix = "",
TriggersSuffix = "",
};
var agent = new SyncAgent(clientProvider, proxyClientProvider, clientSetup, clientOptions);
Console.WriteLine("Press a key to start (be sure web api is running ...)");
Console.ReadKey();
do
{
Console.Clear();
Console.WriteLine("Web sync start");
try
{
var progress = new SynchronousProgress <ProgressArgs>(pa => Console.WriteLine($"{pa.Context.SessionId} - {pa.Context.SyncStage}\t {pa.Message}"));
var s = await agent.SynchronizeAsync(progress);
Console.WriteLine(s);
}
catch (SyncException e)
{
Console.WriteLine(e.Message);
}
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);
Console.WriteLine("End");
}
public override DbSyncAdapter GetSyncAdapter(SyncTable tableDescription, ParserName tableName, ParserName trackingTableName, SyncSetup setup, string scopeName) => new SqlSyncAdapter(tableDescription, tableName, trackingTableName, setup, scopeName);
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);
}
}
/// <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 SqlSyncProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
//var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
var clientProvider = new SqliteSyncProvider("clientX.db");
//var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" });
var setup = new SyncSetup(allTables);
setup.Filters.Add("Customer", "CompanyName");
var addressCustomerFilter = new SetupFilter("CustomerAddress");
addressCustomerFilter.AddParameter("CompanyName", "Customer");
addressCustomerFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressCustomerFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup.Filters.Add(addressCustomerFilter);
var addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CompanyName", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup.Filters.Add(addressFilter);
var orderHeaderFilter = new SetupFilter("SalesOrderHeader");
orderHeaderFilter.AddParameter("CompanyName", "Customer");
orderHeaderFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderHeaderFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
orderHeaderFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup.Filters.Add(orderHeaderFilter);
var orderDetailsFilter = new SetupFilter("SalesOrderDetail");
orderDetailsFilter.AddParameter("CompanyName", "Customer");
orderDetailsFilter.AddJoin(Join.Left, "SalesOrderHeader").On("SalesOrderDetail", "SalesOrderID", "SalesOrderHeader", "SalesOrderID");
orderDetailsFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderDetailsFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
orderDetailsFilter.AddWhere("CompanyName", "Customer", "CompanyName");
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 = false;
agent.Options.DisableConstraintsOnApplyChanges = false;
agent.Options.ConflictResolutionPolicy = ConflictResolutionPolicy.ClientWins;
//agent.Options.UseVerboseErrors = false;
//agent.Options.ScopeInfoTableName = "tscopeinfo";
var myRijndael = new RijndaelManaged();
myRijndael.GenerateKey();
myRijndael.GenerateIV();
//agent.RemoteOrchestrator.OnSerializingSet(ssa =>
//{
// // Create an encryptor to perform the stream transform.
// var encryptor = myRijndael.CreateEncryptor(myRijndael.Key, myRijndael.IV);
// using (var msEncrypt = new MemoryStream())
// {
// using (var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
// {
// using (var swEncrypt = new StreamWriter(csEncrypt))
// {
// //Write all data to the stream.
// var strSet = JsonConvert.SerializeObject(ssa.Set);
// swEncrypt.Write(strSet);
// }
// ssa.Data = msEncrypt.ToArray();
// }
// }
//});
//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("CompanyName"))
{
agent.Parameters.Add("CompanyName", "Professional Sales and Service");
}
//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");
}
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");
}
/// <summary> /// Create table name and tracking name, accordingly to the setup /// </summary> public abstract (ParserName tableName, ParserName trackingName) GetParsers(SyncTable tableDescription, SyncSetup setup);
public override (ParserName tableName, ParserName trackingName) GetParsers(SyncTable tableDescription, SyncSetup setup)
{
var originalTableName = ParserName.Parse(tableDescription);
var pref = setup.TrackingTablesPrefix;
var suf = setup.TrackingTablesSuffix;
// be sure, at least, we have a suffix if we have empty values.
// othewise, we have the same name for both table and tracking table
if (string.IsNullOrEmpty(pref) && string.IsNullOrEmpty(suf))
{
suf = "_tracking";
}
var trakingTableNameString = $"{pref}{originalTableName.ObjectName}{suf}";
if (!string.IsNullOrEmpty(originalTableName.SchemaName))
{
trakingTableNameString = $"{originalTableName.SchemaName}.{trakingTableNameString}";
}
var trackingTableName = ParserName.Parse(trakingTableNameString);
return(originalTableName, trackingTableName);
}
public void SyncSetup_Compare_TwoSetup_Properties_ShouldBe_Different()
{
var setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
var setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.StoredProceduresPrefix = "sp1";
setup2.StoredProceduresPrefix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.StoredProceduresPrefix = null;
setup2.StoredProceduresPrefix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.StoredProceduresSuffix = "sp1";
setup2.StoredProceduresSuffix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.StoredProceduresSuffix = null;
setup2.StoredProceduresSuffix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TriggersPrefix = "sp1";
setup2.TriggersPrefix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TriggersPrefix = "sp1";
setup2.TriggersPrefix = null;
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TriggersSuffix = "sp1";
setup2.TriggersSuffix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TriggersSuffix = "sp1";
setup2.TriggersSuffix = null;
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TrackingTablesPrefix = "sp1";
setup2.TrackingTablesPrefix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TrackingTablesPrefix = null;
setup2.TrackingTablesPrefix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TrackingTablesSuffix = "sp1";
setup2.TrackingTablesSuffix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
setup1 = new SyncSetup(new string[] { "Employee", "ProductCategory", "Product" });
setup2 = new SyncSetup(new string[] { "Product", "ProductCategory", "Employee" });
setup1.TrackingTablesSuffix = null;
setup2.TrackingTablesSuffix = "sp";
Assert.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
}
public async Task LocalOrchestrator_Scope()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(this.Tables);
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var scopeTableCreating = 0;
var scopeTableCreated = 0;
var scopeLoading = 0;
var scopeLoaded = 0;
var scopeSaving = 0;
var scopeSaved = 0;
localOrchestrator.OnScopeSaving(ssa =>
{
Assert.NotNull(ssa.Command);
scopeSaving++;
});
localOrchestrator.OnScopeSaved(ssa => scopeSaved++);
localOrchestrator.OnScopeTableCreating(stca =>
{
Assert.NotNull(stca.Command);
scopeTableCreating++;
});
localOrchestrator.OnScopeTableCreated(stca =>
{
scopeTableCreated++;
});
localOrchestrator.OnScopeLoading(args =>
{
Assert.NotNull(args.Command);
Assert.Equal(SyncStage.ScopeLoading, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.Equal(scopeName, args.ScopeName);
Assert.NotNull(args.Connection);
Assert.NotNull(args.Transaction);
Assert.Equal(ConnectionState.Open, args.Connection.State);
Assert.Same(args.Connection, args.Transaction.Connection);
scopeLoading++;
});
localOrchestrator.OnScopeLoaded(args =>
{
Assert.Equal(SyncStage.ScopeLoading, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.ScopeInfo);
Assert.Equal(scopeName, args.ScopeInfo.Name);
Assert.NotNull(args.Connection);
Assert.NotNull(args.Transaction);
scopeLoaded++;
});
var localScopeInfo = await localOrchestrator.GetClientScopeAsync();
Assert.Equal(1, scopeTableCreating);
Assert.Equal(1, scopeTableCreated);
Assert.Equal(1, scopeLoading);
Assert.Equal(1, scopeLoaded);
Assert.Equal(1, scopeSaving);
Assert.Equal(1, scopeSaved);
scopeTableCreating = 0;
scopeTableCreated = 0;
scopeLoading = 0;
scopeLoaded = 0;
scopeSaving = 0;
scopeSaved = 0;
localScopeInfo.Version = "2.0";
await localOrchestrator.SaveClientScopeAsync(localScopeInfo);
Assert.Equal(0, scopeTableCreating);
Assert.Equal(0, scopeTableCreated);
Assert.Equal(0, scopeLoading);
Assert.Equal(0, scopeLoaded);
Assert.Equal(1, scopeSaving);
Assert.Equal(1, scopeSaved);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public void SyncSetup_Compare_TwoSetup_With_Filters_ShouldBe_Different()
{
// Check Setup shoul be differents when tables names count is not same
SyncSetup setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory" });
SyncSetup setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
Assert.False(setup1.EqualsByProperties(setup2));
// Check Setup should be differents when tables names are differents
setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee1" });
setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee2" });
Assert.False(setup1.EqualsByProperties(setup2));
// Check when Setup Filter names are differente (Customer1 and Customer2)
setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup1.Filters.Add("Customer1", "CompanyName");
var addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CompanyName", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup1.Filters.Add(addressFilter);
setup2.Filters.Add("Customer2", "CompanyName");
var addressFilter2 = new SetupFilter("Address");
addressFilter2.AddParameter("CompanyName", "Customer");
addressFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
setup2.Filters.Add(addressFilter2);
Assert.False(setup1.EqualsByProperties(setup2));
// 2) Check when Setup Filter names are differente (Address1 and Address2)
setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup1.Filters.Add("Customer", "CompanyName");
addressFilter = new SetupFilter("Address1");
addressFilter.AddParameter("CompanyName", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup1.Filters.Add(addressFilter);
setup2.Filters.Add("Customer", "CompanyName");
addressFilter2 = new SetupFilter("Address2");
addressFilter2.AddParameter("CompanyName", "Customer");
addressFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
setup2.Filters.Add(addressFilter2);
Assert.False(setup1.EqualsByProperties(setup2));
// 3) Check when Setup Parameter names are differente (CompanyName1 and CompanyName2)
setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup1.Filters.Add("Customer", "CompanyName");
addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CompanyName1", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName", "Customer", "CompanyName1");
setup1.Filters.Add(addressFilter);
setup2.Filters.Add("Customer", "CompanyName");
addressFilter2 = new SetupFilter("Address");
addressFilter2.AddParameter("CompanyName2", "Customer");
addressFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter2.AddWhere("CompanyName", "Customer", "CompanyName2");
setup2.Filters.Add(addressFilter2);
Assert.False(setup1.EqualsByProperties(setup2));
// 4) Check when Setup Joins names are differente (CustomerAddress1 and CustomerAddress2)
setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup1.Filters.Add("Customer", "CompanyName");
addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CompanyName", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress1").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName", "Customer", "CompanyName");
setup1.Filters.Add(addressFilter);
setup2.Filters.Add("Customer", "CompanyName");
addressFilter2 = new SetupFilter("Address");
addressFilter2.AddParameter("CompanyName", "Customer");
addressFilter2.AddJoin(Join.Left, "CustomerAddress2").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
setup2.Filters.Add(addressFilter2);
Assert.False(setup1.EqualsByProperties(setup2));
// 5) Check when Setup Where names are differente (CompanyName1 and CompanyName2)
setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup1.Filters.Add("Customer", "CompanyName");
addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CompanyName", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName1", "Customer", "CompanyName");
setup1.Filters.Add(addressFilter);
setup2.Filters.Add("Customer", "CompanyName");
addressFilter2 = new SetupFilter("Address");
addressFilter2.AddParameter("CompanyName", "Customer");
addressFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter2.AddWhere("CompanyName2", "Customer", "CompanyName");
setup2.Filters.Add(addressFilter2);
Assert.False(setup1.EqualsByProperties(setup2));
// 6) Check CustomWhere differences
setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup2 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
setup1.Filters.Add("Customer", "CompanyName");
addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CompanyName", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter.AddWhere("CompanyName", "Customer", "CompanyName");
addressFilter.AddCustomWhere("ID = @ID2");
setup1.Filters.Add(addressFilter);
setup2.Filters.Add("Customer", "CompanyName");
addressFilter2 = new SetupFilter("Address");
addressFilter2.AddParameter("CompanyName", "Customer");
addressFilter2.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter2.AddJoin(Join.Left, "Customer").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
addressFilter2.AddWhere("CompanyName", "Customer", "CompanyName");
addressFilter2.AddCustomWhere("ID = @ID1");
setup2.Filters.Add(addressFilter2);
Assert.False(setup1.EqualsByProperties(setup2));
}
public SqlChangeTrackingSyncAdapter(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, string scopeName)
: base(tableDescription, tableName, trackingName, setup, scopeName)
{
}
public async Task Table_Create_One()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var schema = new SyncSet();
schema.Tables.Add(table);
var localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var scopeInfo = await localOrchestrator.GetClientScopeInfoAsync();
scopeInfo.Setup = setup;
scopeInfo.Schema = schema;
await localOrchestrator.SaveClientScopeInfoAsync(scopeInfo);
var onCreating = false;
var onCreated = false;
localOrchestrator.OnTableCreating(ttca =>
{
var addingID = Environment.NewLine + $"ALTER TABLE {ttca.TableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
localOrchestrator.OnTableCreated(ttca =>
{
onCreated = true;
});
var isCreated = await localOrchestrator.CreateTableAsync(scopeInfo, table.TableName, table.SchemaName);
Assert.True(isCreated);
Assert.True(onCreating);
Assert.True(onCreated);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "Product", "SalesLT").ConfigureAwait(false);
Assert.Equal(4, cols.Rows.Count);
Assert.NotNull(cols.Rows.FirstOrDefault(r => r["name"].ToString() == "internal_id"));
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public static IServiceCollection AddSyncServer <TProvider>(this IServiceCollection serviceCollection, string connectionString, SyncSetup setup, WebServerOptions options = null) where TProvider : CoreProvider, new() => serviceCollection.AddSyncServer(typeof(TProvider), connectionString, setup, options);
/// <summary>
/// Construct a DbBuilder
/// </summary>
public DbTableBuilder(SyncTable tableDescription, ParserName tableName, ParserName trackingTableName, SyncSetup setup)
{
this.TableDescription = tableDescription;
this.Setup = setup;
this.TableName = tableName;
this.TrackingTableName = trackingTableName;
}
public override DbTableBuilder GetTableBuilder(SyncTable tableDescription, SyncSetup setup) => new MyTableSqlBuilder(tableDescription, setup);
public async Task Trigger_Create_One_Overwrite()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTriggerCreating(tca => onCreating++);
remoteOrchestrator.OnTriggerCreated(tca => onCreated++);
remoteOrchestrator.OnTriggerDropping(tca => onDropping++);
remoteOrchestrator.OnTriggerDropped(tca => onDropped++);
var isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
onCreating = 0;
onCreated = 0;
onDropping = 0;
onDropped = 0;
isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.False(isCreated);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert, true);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(1, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
/// <summary>
/// Default ctor. Using default options and schema
/// </summary>
/// <param name="provider"></param>
public WebServerOrchestrator(CoreProvider provider, SyncOptions options, WebServerOptions webServerOptions, SyncSetup setup, IMemoryCache cache = null, string scopeName = SyncOptions.DefaultScopeName)
: base(provider, options, setup, scopeName)
{
this.WebServerOptions = webServerOptions ?? throw new ArgumentNullException(nameof(webServerOptions));
this.Cache = cache ?? new MemoryCache(new MemoryCacheOptions());
}
public async Task RemoteOrchestrator_GetChanges_ShouldReturnNewRowsInserted()
{
var dbNameSrv = HelperDatabase.GetRandomName("tcp_lo_srv");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameSrv, true);
var dbNameCli = HelperDatabase.GetRandomName("tcp_lo_cli");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameCli, true);
var csServer = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameSrv);
var serverProvider = new SqlSyncProvider(csServer);
var csClient = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameCli);
var clientProvider = new SqlSyncProvider(csClient);
await new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider), true, false).Database.EnsureCreatedAsync();
await new AdventureWorksContext((dbNameCli, ProviderType.Sql, clientProvider), true, false).Database.EnsureCreatedAsync();
var scopeName = "scopesnap1";
var syncOptions = new SyncOptions();
var setup = new SyncSetup();
// Make a first sync to be sure everything is in place
var agent = new SyncAgent(clientProvider, serverProvider, this.Tables, scopeName);
// Making a first sync, will initialize everything we need
await agent.SynchronizeAsync();
// Get the orchestrators
var localOrchestrator = agent.LocalOrchestrator;
var remoteOrchestrator = agent.RemoteOrchestrator;
// Server side : Create a product category and a product
// Create a productcategory item
// Create a new product on server
var productId = Guid.NewGuid();
var productName = HelperDatabase.GetRandomName();
var productNumber = productName.ToUpperInvariant().Substring(0, 10);
var productCategoryName = HelperDatabase.GetRandomName();
var productCategoryId = productCategoryName.ToUpperInvariant().Substring(0, 6);
using (var ctx = new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider)))
{
var pc = new ProductCategory {
ProductCategoryId = productCategoryId, Name = productCategoryName
};
ctx.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber
};
ctx.Add(product);
await ctx.SaveChangesAsync();
}
// Get client scope
var clientScope = await localOrchestrator.GetClientScopeAsync();
// Get changes to be populated to the server
var changes = await remoteOrchestrator.GetChangesAsync(clientScope);
Assert.NotNull(changes.ServerBatchInfo);
Assert.NotNull(changes.ServerChangesSelected);
Assert.Equal(2, changes.ServerChangesSelected.TableChangesSelected.Count);
Assert.Contains("Product", changes.ServerChangesSelected.TableChangesSelected.Select(tcs => tcs.TableName).ToList());
Assert.Contains("ProductCategory", changes.ServerChangesSelected.TableChangesSelected.Select(tcs => tcs.TableName).ToList());
var productTable = changes.ServerBatchInfo.InMemoryData.Tables["Product", "SalesLT"];
var productRowName = productTable.Rows[0]["Name"];
Assert.Equal(productName, productRowName);
var productCategoryTable = changes.ServerBatchInfo.InMemoryData.Tables["ProductCategory", "SalesLT"];
var productCategoryRowName = productCategoryTable.Rows[0]["Name"];
Assert.Equal(productCategoryName, productCategoryRowName);
}
public SqlBuilderTrigger(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup)
{
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.sqlObjectNames = new SqlObjectNames(this.tableDescription, tableName, trackingName, this.setup);
}
public MariaDBDownloadOnlySyncAdapter(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, string scopeName, int bulkBatchMaxLinesCount)
: base(tableDescription, tableName, trackingName, setup, scopeName)
{
this.tableName = tableName;
this.bulkBatchMaxLinesCount = bulkBatchMaxLinesCount;
}
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");
}
