public SyncSetup GetFilterSetup()
{
var setup = new SyncSetup(new string[] {
"SalesLT.ProductModel", "SalesLT.ProductCategory", "SalesLT.Product",
"Customer", "Address", "CustomerAddress", "Employee",
"SalesLT.SalesOrderHeader", "SalesLT.SalesOrderDetail"
});
// Vertical Filter on columns
setup.Tables["Customer"].Columns.AddRange(new string[] { "CustomerID", "EmployeeID", "NameStyle", "FirstName", "LastName" });
setup.Tables["Address"].Columns.AddRange(new string[] { "AddressID", "AddressLine1", "City", "PostalCode" });
// Horizontal Filters on where clause
// 1) EASY Way:
setup.Filters.Add("CustomerAddress", "CustomerID");
setup.Filters.Add("SalesOrderHeader", "CustomerID", "SalesLT");
// 2) Same, but decomposed in 3 Steps
var customerFilter = new SetupFilter("Customer");
customerFilter.AddParameter("CustomerID", "Customer", true);
customerFilter.AddWhere("CustomerID", "Customer", "CustomerID");
setup.Filters.Add(customerFilter);
// 3) Create your own filter
// Create a filter on table Address
var addressFilter = new SetupFilter("Address");
addressFilter.AddParameter("CustomerID", "Customer");
addressFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "AddressId", "Address", "AddressId");
addressFilter.AddWhere("CustomerID", "CustomerAddress", "CustomerID");
setup.Filters.Add(addressFilter);
// ----------------------------------------------------
// Create a filter on table SalesLT.SalesOrderDetail
var salesOrderDetailFilter = new SetupFilter("SalesOrderDetail", "SalesLT");
salesOrderDetailFilter.AddParameter("CustomerID", "Customer");
salesOrderDetailFilter.AddJoin(Join.Left, "SalesLT.SalesOrderHeader").On("SalesLT.SalesOrderHeader", "SalesOrderId", "SalesLT.SalesOrderDetail", "SalesOrderId");
salesOrderDetailFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerID", "SalesLT.SalesOrderHeader", "CustomerID");
salesOrderDetailFilter.AddWhere("CustomerID", "CustomerAddress", "CustomerID");
setup.Filters.Add(salesOrderDetailFilter);
// ----------------------------------------------------
// 4) Custom Wheres on Product.
var productFilter = new SetupFilter("Product", "SalesLT");
productFilter.AddCustomWhere("ProductCategoryID IS NOT NULL OR side.sync_row_is_tombstone = 1");
setup.Filters.Add(productFilter);
return(setup);
}
public async Task Scenario_MultiScopes_SameTables_DifferentFilters(SyncOptions options)
{
// This test works only if we have the same exact provider on both sides
// create client orchestrator that is the same as server
var clientDatabaseName = HelperDatabase.GetRandomName("tcpfilt_cli_");
var clientProvider = this.CreateProvider(this.ServerType, clientDatabaseName);
// create a client database
await this.CreateDatabaseAsync(Server.ProviderType, clientDatabaseName, true);
// Get the correct names for ProductCategory and Product
var productCategoryTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.ProductCategory" : "ProductCategory";
var productTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.Product" : "Product";
// create a server schema with seeding
await this.EnsureDatabaseSchemaAndSeedAsync(this.Server, true, UseFallbackSchema);
// Step 1: Create a default scope and Sync clients
// Note we are not including the [Attribute With Space] column
var setup = new SyncSetup(productCategoryTableName, productTableName);
setup.Tables[productCategoryTableName].Columns.AddRange(
new string[] { "ProductCategoryId", "Name", "rowguid", "ModifiedDate" });
var schemaName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT" : null;
// Add filters
var productFilter = new SetupFilter("Product", schemaName);
productFilter.AddParameter("ProductCategoryID", "Product", schemaName);
productFilter.AddWhere("ProductCategoryID", "Product", "ProductCategoryID", schemaName);
var productCategoryFilter = new SetupFilter("ProductCategory", schemaName);
productCategoryFilter.AddParameter("ProductCategoryID", "ProductCategory", schemaName);
productCategoryFilter.AddWhere("ProductCategoryID", "ProductCategory", "ProductCategoryID", schemaName);
setup.Filters.Add(productCategoryFilter);
setup.Filters.Add(productFilter);
// ------------------------------------------------
var paramMountb = new SyncParameters(("ProductCategoryID", "MOUNTB"));
var paramRoadfr = new SyncParameters(("ProductCategoryID", "ROADFR"));
// create agent with filtered tables and parameter
var agent = new SyncAgent(clientProvider, Server.Provider, options);
var rTourb = await agent.SynchronizeAsync("Mountb", setup, paramMountb);
var rRoadfr = await agent.SynchronizeAsync("Roadfr", setup, paramRoadfr);
Assert.Equal(8, rTourb.TotalChangesDownloaded);
Assert.Equal(8, rTourb.TotalChangesApplied);
Assert.Equal(3, rRoadfr.TotalChangesDownloaded);
Assert.Equal(3, rRoadfr.TotalChangesApplied);
}
public void SetupFilter_Compare_TwoSetupFilters_With_Where_ShouldBe_Different()
{
var filter1 = new SetupFilter();
var filter2 = new SetupFilter();
filter1.AddParameter("ProductId", DbType.Guid);
filter2.AddParameter("ProductId", DbType.Guid);
filter1.AddWhere("ProductId", "Product", "ProductId", "dbo");
filter2.AddWhere("ProductId", "Product", "ProductId", "SalesLT");
Assert.NotEqual(filter1, filter2);
Assert.False(filter1.EqualsByProperties(filter2));
filter1 = new SetupFilter();
filter2 = new SetupFilter();
filter1.AddParameter("ProductId", DbType.Guid);
filter2.AddParameter("ProductId", DbType.Guid);
filter1.AddWhere("ProductId1", "Product", "ProductId");
filter2.AddWhere("ProductId2", "Product", "ProductId");
Assert.NotEqual(filter1, filter2);
Assert.False(filter1.EqualsByProperties(filter2));
filter1 = new SetupFilter();
filter2 = new SetupFilter();
filter1.AddParameter("ProductId", DbType.Guid);
filter2.AddParameter("ProductId", DbType.Guid);
filter1.AddWhere("ProductId", "Product1", "ProductId", "");
filter2.AddWhere("ProductId", "Product2", "ProductId");
Assert.NotEqual(filter1, filter2);
Assert.False(filter1.EqualsByProperties(filter2));
filter1 = new SetupFilter();
filter2 = new SetupFilter();
filter1.AddParameter("ProductId1", DbType.Guid);
filter2.AddParameter("ProductId2", DbType.Guid);
filter1.AddWhere("ProductId", "Product", "ProductId1");
filter2.AddWhere("ProductId", "Product", "ProductId2");
Assert.NotEqual(filter1, filter2);
Assert.False(filter1.EqualsByProperties(filter2));
}
public void SetupFilter_Compare_TwoSetupFilters_With_Where_ShouldBe_Equals()
{
var filter1 = new SetupFilter();
var filter2 = new SetupFilter();
filter1.AddParameter("ProductId", DbType.Guid);
filter2.AddParameter("ProductId", DbType.Guid);
filter1.AddWhere("ProductId", "Product", "ProductId", "dbo");
filter2.AddWhere("ProductId", "Product", "ProductId", "dbo");
Assert.Equal(filter1, filter2);
Assert.True(filter1.EqualsByProperties(filter2));
filter1 = new SetupFilter();
filter2 = new SetupFilter();
filter1.AddParameter("ProductId", DbType.Guid);
filter2.AddParameter("ProductId", DbType.Guid);
filter1.AddWhere("ProductId", "Product", "ProductId");
filter2.AddWhere("ProductId", "Product", "ProductId");
Assert.Equal(filter1, filter2);
Assert.True(filter1.EqualsByProperties(filter2));
filter1 = new SetupFilter();
filter2 = new SetupFilter();
filter1.AddParameter("ProductId", DbType.Guid);
filter2.AddParameter("ProductId", DbType.Guid);
filter1.AddWhere("ProductId", "Product", "ProductId", "");
filter2.AddWhere("ProductId", "Product", "ProductId");
Assert.Equal(filter1, filter2);
Assert.True(filter1.EqualsByProperties(filter2));
filter1 = new SetupFilter();
filter2 = new SetupFilter();
filter1.AddParameter("ProductId", DbType.Guid);
filter2.AddParameter("ProductId", DbType.Guid);
filter1.AddWhere("ProductId", "Product", "ProductId");
filter2.AddWhere("ProductId", "Product", "ProductId", "");
Assert.Equal(filter1, filter2);
Assert.True(filter1.EqualsByProperties(filter2));
}
private static async Task SynchronizeAsync()
{
// 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 setup = new SyncSetup("ProductCategory", "Product");
// Shortcut to create a filter directly from your SyncSetup instance
// We are filtering all the product categories, by the ID (a GUID)
setup.Filters.Add("ProductCategory", "ProductCategoryID");
// For the second table (Product) We can also create the filter manually.
// The next 4 lines are equivalent to : setup.Filters.Add("Product", "ProductCategoryID");
var productFilter = new SetupFilter("Product");
// Add a column as parameter. This column will be automaticaly added in the tracking table
productFilter.AddParameter("ProductCategoryID", "Product");
// add the side where expression, mapping the parameter to the column
productFilter.AddWhere("ProductCategoryID", "Product", "ProductCategoryID");
// add this filter to setup
setup.Filters.Add(productFilter);
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
var parameters = new SyncParameters(("ProductCategoryID", new Guid("10A7C342-CA82-48D4-8A38-46A2EB089B74")));
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(setup, parameters);
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public void SyncSetup_Compare_TwoSetup_With_Filters_ShouldBe_Different()
{
SyncSetup setup1 = new SyncSetup(new string[] { "Customer", "Address", "ProductCategory", "Employee" });
SyncSetup 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.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
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.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
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.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
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.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
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.NotEqual(setup1, setup2);
Assert.False(setup1.Equals(setup2));
}
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.Equal(setup1, setup2);
Assert.True(setup1.Equals(setup2));
}
private static async Task SynchronizeAsync()
{
// 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 clientProvider = new SqliteSyncProvider("advfiltered.db");
var setup = new SyncSetup("ProductCategory",
"ProductModel", "Product",
"Address", "Customer", "CustomerAddress",
"SalesOrderHeader", "SalesOrderDetail");
// ----------------------------------------------------
// Horizontal Filter: On rows. Removing rows from source
// ----------------------------------------------------
// Over all filter : "we Want only customer from specific city and specific postal code"
// First level table : Address
// Second level tables : CustomerAddress
// Third level tables : Customer, SalesOrderHeader
// Fourth level tables : SalesOrderDetail
// Create a filter on table Address on City Washington
// Optional : Sub filter on PostalCode, for testing purpose
var addressFilter = new SetupFilter("Address");
// For each filter, you have to provider all the input parameters
// A parameter could be a parameter mapped to an existing colum :
// That way you don't have to specify any type, length and so on ...
// We can specify if a null value can be passed as parameter value :
// That way ALL addresses will be fetched
// A default value can be passed as well, but works only on SQL Server (MySql is a damn ... thing)
addressFilter.AddParameter("City", "Address", true);
// Or a parameter could be a random parameter bound to anything.
// In that case, you have to specify everything
// (This parameter COULD BE bound to a column, like City,
// but for the example, we go for a custom parameter)
addressFilter.AddParameter("postal", DbType.String, true, null, 20);
// Then you map each parameter on wich table / column the "where" clause should be applied
addressFilter.AddWhere("City", "Address", "City");
addressFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(addressFilter);
var addressCustomerFilter = new SetupFilter("CustomerAddress");
addressCustomerFilter.AddParameter("City", "Address", true);
addressCustomerFilter.AddParameter("postal", DbType.String, true, null, 20);
// You can join table to go from your table up (or down) to your filter table
addressCustomerFilter.AddJoin(Join.Left, "Address")
.On("CustomerAddress", "AddressId", "Address", "AddressId");
// And then add your where clauses
addressCustomerFilter.AddWhere("City", "Address", "City");
addressCustomerFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(addressCustomerFilter);
var customerFilter = new SetupFilter("Customer");
customerFilter.AddParameter("City", "Address", true);
customerFilter.AddParameter("postal", DbType.String, true, null, 20);
customerFilter.AddJoin(Join.Left, "CustomerAddress")
.On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
customerFilter.AddJoin(Join.Left, "Address")
.On("CustomerAddress", "AddressId", "Address", "AddressId");
customerFilter.AddWhere("City", "Address", "City");
customerFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(customerFilter);
var orderHeaderFilter = new SetupFilter("SalesOrderHeader");
orderHeaderFilter.AddParameter("City", "Address", true);
orderHeaderFilter.AddParameter("postal", DbType.String, true, null, 20);
orderHeaderFilter.AddJoin(Join.Left, "CustomerAddress")
.On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderHeaderFilter.AddJoin(Join.Left, "Address")
.On("CustomerAddress", "AddressId", "Address", "AddressId");
orderHeaderFilter.AddWhere("City", "Address", "City");
orderHeaderFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(orderHeaderFilter);
var orderDetailsFilter = new SetupFilter("SalesOrderDetail");
orderDetailsFilter.AddParameter("City", "Address", true);
orderDetailsFilter.AddParameter("postal", DbType.String, true, null, 20);
orderDetailsFilter.AddJoin(Join.Left, "SalesOrderHeader")
.On("SalesOrderHeader", "SalesOrderID", "SalesOrderDetail", "SalesOrderID");
orderDetailsFilter.AddJoin(Join.Left, "CustomerAddress")
.On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderDetailsFilter.AddJoin(Join.Left, "Address")
.On("CustomerAddress", "AddressId", "Address", "AddressId");
orderDetailsFilter.AddWhere("City", "Address", "City");
orderDetailsFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(orderDetailsFilter);
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
var parameters = new SyncParameters
{
{ "City", "Toronto" },
// Because I've specified that "postal" could be null,
// I can set the value to DBNull.Value (and the get all postal code in Toronto city)
{ "postal", "M4B 1V5" }
};
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(setup, parameters);
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public async Task RemoteOrchestrator_CreateSnapshot_WithParameters_CheckBatchInfo()
{
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, true);
await ctx.Database.EnsureCreatedAsync();
// snapshot directory
var snapshotDirctoryName = HelperDatabase.GetRandomName();
var snapshotDirectory = Path.Combine(Environment.CurrentDirectory, snapshotDirctoryName);
var options = new SyncOptions
{
SnapshotsDirectory = snapshotDirectory,
BatchSize = 200
};
var setup = new SyncSetup(Tables);
var provider = new SqlSyncProvider(cs);
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);
var orchestrator = new RemoteOrchestrator(provider, options, setup);
SyncParameters parameters = new SyncParameters();
var p1 = new SyncParameter("CompanyName", "A Bike Store");
parameters.Add(p1);
var bi = await orchestrator.CreateSnapshotAsync(parameters);
var finalDirectoryFullName = Path.Combine(snapshotDirectory, SyncOptions.DefaultScopeName);
Assert.NotNull(bi);
Assert.Equal(finalDirectoryFullName, bi.DirectoryRoot);
Assert.Equal("CompanyName_ABikeStore", bi.DirectoryName);
Assert.Single(bi.BatchPartsInfo);
Assert.Equal(17, bi.BatchPartsInfo[0].Tables.Length);
Assert.True(bi.BatchPartsInfo[0].IsLastBatch);
// Check summary.json exists.
var summaryFile = Path.Combine(bi.GetDirectoryFullPath(), "summary.json");
var summaryString = new StreamReader(summaryFile).ReadToEnd();
var summaryObject = JObject.Parse(summaryString);
Assert.NotNull(summaryObject);
string summaryDirname = (string)summaryObject["dirname"];
Assert.NotNull(summaryDirname);
Assert.Equal("CompanyName_ABikeStore", summaryDirname);
string summaryDir = (string)summaryObject["dir"];
Assert.NotNull(summaryDir);
Assert.Equal(finalDirectoryFullName, summaryDir);
Assert.Single(summaryObject["parts"]);
Assert.NotNull(summaryObject["parts"][0]["file"]);
Assert.NotNull(summaryObject["parts"][0]["index"]);
Assert.Equal(0, (int)summaryObject["parts"][0]["index"]);
Assert.NotNull(summaryObject["parts"][0]["last"]);
Assert.True((bool)summaryObject["parts"][0]["last"]);
Assert.Equal(17, summaryObject["parts"][0]["tables"].Count());
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));
}
/// <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");
}
/// <summary>
/// Launch a simple sync, over TCP network, each sql server (client and server are reachable through TCP cp
/// </summary>
/// <returns></returns>
private static async Task SynchronizeAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncChangeTrackingProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
//var clientProvider = new SqliteSyncProvider("client2.db");
// specific Setup with only 2 tables, and one filtered
var setup = new SyncSetup(allTables);
// ----------------------------------------------------
// Vertical Filter: On columns. Removing columns from source
// ----------------------------------------------------
// Add a table with less columns
setup.Tables["Product"]
.Columns.AddRange(new string[] { "ProductId", "Name", "ProductCategoryID", "ProductNumber", "StandardCost", "ListPrice", "SellStartDate", "rowguid", "ModifiedDate" });
// ----------------------------------------------------
// Horizontal Filter: On rows. Removing rows from source
// ----------------------------------------------------
// Over all filter : "we Want only customer from specific city and specific postal code"
// First level table : Address
// Second level tables : CustomerAddress
// Third level tables : Customer, SalesOrderHeader
// Fourth level tables : SalesOrderDetail
// Create a filter on table Address on City Washington
// Optional : Sub filter on PostalCode, for testing purpose
var addressFilter = new SetupFilter("Address");
// For each filter, you have to provider all the input parameters
// A parameter could be a parameter mapped to an existing colum : That way you don't have to specify any type, length and so on ...
// We can specify if a null value can be passed as parameter value : That way ALL addresses will be fetched
// A default value can be passed as well, but works only on SQL Server (MySql is a damn shity thing)
addressFilter.AddParameter("City", "Address", true);
// Or a parameter could be a random parameter bound to anything. In that case, you have to specify everything
// (This parameter COULD BE bound to a column, like City, but for the example, we go for a custom parameter)
addressFilter.AddParameter("postal", DbType.String, true, null, 20);
// Then you map each parameter on wich table / column the "where" clause should be applied
addressFilter.AddWhere("City", "Address", "City");
addressFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(addressFilter);
var addressCustomerFilter = new SetupFilter("CustomerAddress");
addressCustomerFilter.AddParameter("City", "Address", true);
addressCustomerFilter.AddParameter("postal", DbType.String, true, null, 20);
// You can join table to go from your table up (or down) to your filter table
addressCustomerFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
// And then add your where clauses
addressCustomerFilter.AddWhere("City", "Address", "City");
addressCustomerFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(addressCustomerFilter);
var customerFilter = new SetupFilter("Customer");
customerFilter.AddParameter("City", "Address", true);
customerFilter.AddParameter("postal", DbType.String, true, null, 20);
customerFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "Customer", "CustomerId");
customerFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
customerFilter.AddWhere("City", "Address", "City");
customerFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(customerFilter);
var orderHeaderFilter = new SetupFilter("SalesOrderHeader");
orderHeaderFilter.AddParameter("City", "Address", true);
orderHeaderFilter.AddParameter("postal", DbType.String, true, null, 20);
orderHeaderFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderHeaderFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
orderHeaderFilter.AddWhere("City", "Address", "City");
orderHeaderFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(orderHeaderFilter);
var orderDetailsFilter = new SetupFilter("SalesOrderDetail");
orderDetailsFilter.AddParameter("City", "Address", true);
orderDetailsFilter.AddParameter("postal", DbType.String, true, null, 20);
orderDetailsFilter.AddJoin(Join.Left, "SalesOrderHeader").On("SalesOrderHeader", "SalesOrderID", "SalesOrderHeader", "SalesOrderID");
orderDetailsFilter.AddJoin(Join.Left, "CustomerAddress").On("CustomerAddress", "CustomerId", "SalesOrderHeader", "CustomerId");
orderDetailsFilter.AddJoin(Join.Left, "Address").On("CustomerAddress", "AddressId", "Address", "AddressId");
orderDetailsFilter.AddWhere("City", "Address", "City");
orderDetailsFilter.AddWhere("PostalCode", "Address", "postal");
setup.Filters.Add(orderDetailsFilter);
// ----------------------------------------------------
// Add pref suf
setup.StoredProceduresPrefix = "s";
setup.StoredProceduresSuffix = "";
setup.TrackingTablesPrefix = "t";
setup.TrackingTablesSuffix = "";
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, setup);
// Using the Progress pattern to handle progession during the synchronization
var progress = new SynchronousProgress <ProgressArgs>(s =>
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}");
Console.ResetColor();
});
var remoteProgress = new SynchronousProgress <ProgressArgs>(s =>
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}");
Console.ResetColor();
});
agent.AddRemoteProgress(remoteProgress);
//agent.Options.BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "sync");
agent.Options.BatchSize = 1000;
agent.Options.CleanMetadatas = true;
agent.Options.UseBulkOperations = true;
agent.Options.DisableConstraintsOnApplyChanges = false;
//agent.Options.ConflictResolutionPolicy = ConflictResolutionPolicy.ServerWins;
//agent.Options.UseVerboseErrors = false;
//agent.Options.ScopeInfoTableName = "tscopeinfo";
//agent.OnApplyChangesFailed(acf =>
//{
// // Check conflict is correctly set
// var localRow = acf.Conflict.LocalRow;
// var remoteRow = acf.Conflict.RemoteRow;
// // Merge row
// acf.Resolution = ConflictResolution.MergeRow;
// acf.FinalRow["Name"] = "Prout";
//});
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
// Launch the sync process
if (!agent.Parameters.Contains("City"))
{
agent.Parameters.Add("City", "Toronto");
}
if (!agent.Parameters.Contains("postal"))
{
agent.Parameters.Add("postal", DBNull.Value);
}
var s1 = await agent.SynchronizeAsync(progress);
// Write results
Console.WriteLine(s1);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
//Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}