public async Task InitializeAndSync()
{
var session = await agent.SynchronizeAsync();
Assert.Equal(10, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
DmTable dmColumnsListServer;
DmTable dmColumnsListClient;
// check if all types are correct
using (var sqlConnection = new SqlConnection(fixture.ServerConnectionString))
{
sqlConnection.Open();
dmColumnsListServer = SqlManagementUtils.ColumnsForTable(sqlConnection, null, "AllColumns");
sqlConnection.Close();
}
using (var sqlConnection = new SqlConnection(fixture.Client1ConnectionString))
{
sqlConnection.Open();
dmColumnsListClient = SqlManagementUtils.ColumnsForTable(sqlConnection, null, "AllColumns");
sqlConnection.Close();
}
// check if all columns are replicated
Assert.Equal(dmColumnsListServer.Rows.Count, dmColumnsListClient.Rows.Count);
// check if all types are correct
foreach (var serverRow in dmColumnsListServer.Rows.OrderBy(r => (int)r["column_id"]))
{
var name = serverRow["name"].ToString();
var ordinal = (int)serverRow["column_id"];
var typeString = serverRow["type"].ToString();
var maxLength = (Int16)serverRow["max_length"];
var precision = (byte)serverRow["precision"];
var scale = (byte)serverRow["scale"];
var isNullable = (bool)serverRow["is_nullable"];
var isIdentity = (bool)serverRow["is_identity"];
var clientRow = dmColumnsListClient.Rows.FirstOrDefault(cr => (int)cr["column_id"] == ordinal);
Assert.NotNull(clientRow);
// exception on numeric, check if it could be decimal
var cTypeString = clientRow["type"].ToString();
if (typeString.ToLowerInvariant() == "numeric" && cTypeString.ToLowerInvariant() == "decimal")
{
cTypeString = "numeric";
}
Assert.Equal(name, clientRow["name"].ToString());
Assert.Equal(ordinal, (int)clientRow["column_id"]);
Assert.Equal(typeString, cTypeString);
Assert.Equal(maxLength, (Int16)clientRow["max_length"]);
Assert.Equal(precision, (byte)clientRow["precision"]);
Assert.Equal(scale, (byte)clientRow["scale"]);
Assert.Equal(isNullable, (bool)clientRow["is_nullable"]);
Assert.Equal(isIdentity, (bool)clientRow["is_identity"]);
}
}
private static async Task SynchronizeAsync(SqlSyncProvider serverProvider, SqliteSyncProvider clientProvider)
{
// Tables involved in the sync process:
var tables = new string[] { "ServiceTickets" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
// Launch the sync process
var s1 = await agent.SynchronizeAsync(tables);
// This first sync did not upload the client rows.
// We only have rows from server that have been downloaded
// The important step here is to have setup the sync (triggers / tracking tables ...)
Console.WriteLine(s1);
// Now we can "mark" original clients rows as "to be uploaded"
await agent.LocalOrchestrator.UpdateUntrackedRowsAsync();
// Then we can make a new synchronize to upload these rows to server
// Launch the sync process
var s2 = await agent.SynchronizeAsync();
Console.WriteLine(s2);
}
public async Task Initialize()
{
var session = await agent.SynchronizeAsync();
Assert.Equal(15, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
}
public async Task Initialize()
{
var session = await agent.SynchronizeAsync();
Assert.Equal(7, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
// check relation has been created on client :
//int foreignKeysCount=0;
//using (var sqlConnection = new SQLiteConnection(fixture.ClientSQLiteConnectionString))
//{
// var script = $@"PRAGMA foreign_key_list('ServiceTickets')";
// using (var sqlCmd = new SQLiteCommand(script, sqlConnection))
// {
// sqlConnection.Open();
// var reader = sqlCmd.ExecuteReader();
// while (reader.Read())
// foreignKeysCount++;
// sqlConnection.Close();
// }
//}
//Assert.Equal(1, foreignKeysCount);
}
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);
}
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 SqlSyncProvider(serverConnectionString);
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
//var clientProvider = new SqliteSyncProvider("adv.db");
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
//var setup = new SyncSetup("ProductCategory", "ProductModel", "Product",
// "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail");
var setup = new SyncSetup("ProductCategory");
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
var remoteOrchestrator = agent.RemoteOrchestrator;
var localOrchestrator = agent.LocalOrchestrator;
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(setup);
var serverScope = await remoteOrchestrator.ProvisionAsync(setup);
setup = new SyncSetup("ProductCategory", "Product");
var schema = await remoteOrchestrator.GetSchemaAsync(setup);
serverScope.Schema = schema;
serverScope.Setup = setup;
await remoteOrchestrator.ProvisionAsync(serverScope, overwrite : true);
var cs = await localOrchestrator.ProvisionAsync(serverScope, overwrite : true);
s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public async Task AddFilterTableAndNormalTable()
{
var serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
var cli2Provider = new SqlSyncProvider(fixture.Client2ConnectionString);
var cli3Provider = new SqlSyncProvider(fixture.Client3ConnectionString);
SyncConfiguration configuration = new SyncConfiguration(new[] { "ServiceTickets" });
// Add a filter
configuration.Filters.Add("ServiceTickets", "CustomerID");
// first agent with parameter for filter
SyncAgent agent2 = new SyncAgent(cli2Provider, serverProvider, configuration);
agent2.Parameters.Add("ServiceTickets", "CustomerID", 1);
// second agent with no parameter
SyncAgent agent3 = new SyncAgent(cli3Provider, serverProvider, configuration);
var session2 = await agent2.SynchronizeAsync();
var session3 = await agent3.SynchronizeAsync();
// Only 4 lines should be downloaded
Assert.Equal(40, session2.TotalChangesDownloaded);
Assert.Equal(50, session3.TotalChangesDownloaded);
session2 = await agent2.SynchronizeAsync();
session3 = await agent3.SynchronizeAsync();
Assert.Equal(0, session2.TotalChangesDownloaded);
Assert.Equal(0, session3.TotalChangesDownloaded);
// Update one server row outside filter
using (var sqlConnection = new SqlConnection(fixture.ServerConnectionString))
{
var textUpdateOneRow = "Update ServiceTickets set Opened=getdate() where CustomerID=10";
using (var sqlCmd = new SqlCommand(textUpdateOneRow, sqlConnection))
{
sqlConnection.Open();
sqlCmd.ExecuteNonQuery();
sqlConnection.Close();
}
}
session2 = await agent2.SynchronizeAsync();
session3 = await agent3.SynchronizeAsync();
Assert.Equal(0, session2.TotalChangesDownloaded);
Assert.Equal(10, session3.TotalChangesDownloaded);
}
private static async Task PreventDeletionAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var tables = new string[] { "Product" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
// First sync to have some rows on client
var s1 = await agent.SynchronizeAsync(tables);
// Write results
Console.WriteLine(s1);
// do not delete product row. it's your choice !
agent.LocalOrchestrator.OnTableChangesApplying(args =>
{
if (args.State == DataRowState.Deleted && args.SchemaTable.TableName == "Product")
{
Console.WriteLine($"Preventing deletion on {args.BatchPartInfos.Sum(bpi => bpi.RowsCount)} rows.");
args.Cancel = true;
}
});
var c = serverProvider.CreateConnection();
var cmd = c.CreateCommand();
cmd.Connection = c;
cmd.CommandText = "DELETE FROM Product WHERE ProductId >= 750 AND ProductId < 760";
c.Open();
cmd.ExecuteNonQuery();
c.Close();
// Second sync
s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
// Third sync
s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
}
public async Task Initialize()
{
// during the sync, I insert datas on both server and clients
agent.RemoteProvider.ScopeSaved += Server_ScopeSaved;
agent.TableChangesApplying += Client_TableChangesApplying;
var session = await agent.SynchronizeAsync();
agent.RemoteProvider.ScopeSaved -= Server_ScopeSaved;
agent.TableChangesApplying -= Client_TableChangesApplying;
Assert.Equal(50, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
}
private async Task TestSyncAgentDeleteWithForeignKeys(
BlogDbContext localDb,
ISyncProvider localSyncProvider,
BlogDbContext remoteDb,
ISyncProvider remoteSyncProvider)
{
var syncAgent = new SyncAgent(localSyncProvider, remoteSyncProvider);
await syncAgent.SynchronizeAsync();
//create a user on remote store
User remoteUser;
remoteDb.Users.Add(remoteUser = new User()
{
Email = "*****@*****.**", Name = "User", Created = DateTime.Now
});
remoteUser.Posts.Add(new Post()
{
Content = $"Content",
Title = $"Post",
Claps = 1,
Stars = 10
});
await remoteDb.SaveChangesAsync();
await syncAgent.SynchronizeAsync();
var localUser = await localDb.Users.FirstOrDefaultAsync(_ => _.Email == "*****@*****.**");
localUser.ShouldNotBeNull();
//delete user on remotedb
remoteDb = remoteDb.Refresh();
remoteDb.Users.Remove(await remoteDb.Users.FirstAsync(_ => _.Email == "*****@*****.**"));
remoteDb.Posts.RemoveRange(await remoteDb.Posts.ToListAsync());
await remoteDb.SaveChangesAsync();
await syncAgent.SynchronizeAsync();
localDb = localDb.Refresh();
localUser = await localDb.Users.FirstOrDefaultAsync(_ => _.Email == "*****@*****.**");
localUser.ShouldBeNull();
(await localDb.Posts.AnyAsync()).ShouldBeFalse();
}
private static async Task ConflictAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(serverConnectionString);
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, tables);
Console.WriteLine("- Initialize the databases with initial data");
// Make a first sync to have everything in place
Console.WriteLine(await agent.SynchronizeAsync(SyncType.Reinitialize));
Console.WriteLine("- Insert data in client and server databases to generate a conflict Insert Client - Insert Server");
var id = new Random(50000).Next();
// Insert a value on client
await Helper.InsertNConflictsCustomerAsync(clientProvider.CreateConnection(), 10, id, "John", "Clientdoe");
// Insert a value on server with same key, to generate a conflict
await Helper.InsertNConflictsCustomerAsync(serverProvider.CreateConnection(), 10, id, "John", "Serverdoe");
do
{
try
{
Console.WriteLine("- Launch synchronization");
var res = await agent.SynchronizeAsync();
Console.WriteLine(res);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public async Task TableWithForeignKeyButNotParentTable()
{
serverProvider = new MySqlSyncProvider(fixture.ServerConnectionString);
clientProvider = new MySqlSyncProvider(fixture.Client2ConnectionString);
agent = new SyncAgent(clientProvider, serverProvider, new[] {
"ProductCategory",
"Product"
});
var session = await agent.SynchronizeAsync();
Assert.Equal(3, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
int fkeysCount = 0;
using (var sqlConnection = new MySqlConnection(fixture.Client2ConnectionString))
{
using (var sqlCmd = new MySqlCommand("SELECT count(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " +
"WHERE TABLE_SCHEMA = @schema AND REFERENCED_TABLE_NAME is not null", sqlConnection))
{
sqlCmd.Parameters.AddWithValue("@schema", fixture.client2DbName);
sqlConnection.Open();
fkeysCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
sqlConnection.Close();
}
}
Assert.Equal(2, fkeysCount);
}
private async void SyncButton_Click(object sender, RoutedEventArgs e)
{
var clientProvider = new SqliteSyncProvider("employees.db");
var proxyClientProvider = new WebProxyClientProvider(
new Uri("http://localhost:58507/api/authsync"));
// adding bearer auth
if (authenticationResult != null && authenticationResult.AccessToken != null)
{
proxyClientProvider.AddCustomHeader("Authorization", authenticationResult.CreateAuthorizationHeader());
}
var agent = new SyncAgent(clientProvider, proxyClientProvider);
agent.SyncProgress += (s, a) => Debug(a.Message + a.PropertiesMessage);
try
{
var r = await agent.SynchronizeAsync();
Debug("TotalChangesDownloaded: " + r.TotalChangesDownloaded);
}
catch (Exception ex)
{
Debug("Error during sync " + ex.Message);
}
}
private static async Task SynchronizeAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
var serverOrchestrator = new WebClientOrchestrator("https://localhost.fiddler:44342/api/sync");
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverOrchestrator);
do
{
try
{
var progress = new SynchronousProgress <ProgressArgs>(args => Console.WriteLine($"{args.PogressPercentageString}:\t{args.Message}"));
// Launch the sync process
var s1 = await agent.SynchronizeAsync(progress);
// Write results
Console.WriteLine(s1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private static async Task SynchronizeAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create a web proxy Orchesrtrator with a custom serializer
var serverProxyOrchestrator = new WebClientOrchestrator("https://localhost:44342/api/sync")
{
SerializerFactory = new CustomMessagePackSerializerFactory(),
Converter = new CustomConverter()
};
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqlSyncProvider(clientConnectionString);
var progress = new SynchronousProgress <ProgressArgs>(args => Console.WriteLine($"{args.Context.SyncStage}:\t{args.Message}"));
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProxyOrchestrator);
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(progress);
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public async Task SyncThroughHttp()
{
using (var server = new KestrellTestServer())
{
var serverHandler = new RequestDelegate(async context =>
{
SqlSyncProvider serverProvider = new SqlSyncProvider(this.fixture.ServerConnectionString);
SyncConfiguration configuration = new SyncConfiguration(this.fixture.Tables);
configuration.DownloadBatchSizeInKB = 500;
WebProxyServerProvider proxyServerProvider = new WebProxyServerProvider(serverProvider);
proxyServerProvider.Configuration = configuration;
await proxyServerProvider.HandleRequestAsync(context);
});
var clientHandler = new ResponseDelegate(async(serviceUri) =>
{
var proxyProvider = new WebProxyClientProvider(new Uri(serviceUri));
var clientProvider = new SqlSyncProvider(this.fixture.Client1ConnectionString);
SyncAgent agent = new SyncAgent(clientProvider, proxyProvider);
var session = await agent.SynchronizeAsync();
Assert.Equal(5, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
});
await server.Run(serverHandler, clientHandler);
}
}
private async void button1_Click(object sender, EventArgs e)
{
// 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 SqlSyncProvider(serverConnectionString);
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqliteSyncProvider("adv.db");
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, tables);
// Launch the sync process
var s1 = await agent.SynchronizeAsync();
// Write results
MessageBox.Show(s1.ToString());
}
public async Task Initialize()
{
serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
clientProvider = new SqlSyncProvider(fixture.Client1ConnectionString);
agent = new SyncAgent(clientProvider, serverProvider, new[] {
"ProductCategory",
"ProductDescription",
"ProductModel",
"ProductModelProductDescription",
"Product"
});
var session = await agent.SynchronizeAsync();
Assert.Equal(7, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
int fkeysCount = 0;
using (var sqlConnection = new SqlConnection(fixture.Client1ConnectionString))
{
using (var sqlCmd = new SqlCommand("select count(*) from sys.foreign_keys", sqlConnection))
{
sqlConnection.Open();
fkeysCount = (int)sqlCmd.ExecuteScalar();
sqlConnection.Close();
}
}
Assert.Equal(5, fkeysCount);
}
public async Task UpdateFromServer(SyncConfiguration conf)
{
Guid guid = Guid.NewGuid();
var updateRowScript =
$@" Declare @id uniqueidentifier;
Select top 1 @id = ServiceTicketID from ServiceTickets;
Update [ServiceTickets] Set [Title] = 'Updated from server {guid.ToString()}' Where ServiceTicketId = @id";
using (var sqlConnection = new SqlConnection(fixture.ServerConnectionString))
{
using (var sqlCmd = new SqlCommand(updateRowScript, sqlConnection))
{
sqlConnection.Open();
sqlCmd.ExecuteNonQuery();
sqlConnection.Close();
}
}
var serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
var clientProvider = new MySqlSyncProvider(fixture.ClientMySqlConnectionString);
//var simpleConfiguration = new SyncConfiguration(Tables);
var agent = new SyncAgent(clientProvider, serverProvider);
agent.Configuration.DownloadBatchSizeInKB = conf.DownloadBatchSizeInKB;
agent.Configuration.UseBulkOperations = conf.UseBulkOperations;
agent.Configuration.SerializationFormat = conf.SerializationFormat;
agent.Configuration.Add(fixture.Tables);
var session = await agent.SynchronizeAsync();
Assert.Equal(1, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
}
public async Task Initialize()
{
try
{
using (var ctx = new AdventureWorksContext())
{
ctx.ProviderType = ProviderType.Sql;
ctx.ConnectionString = this.serverCString;
ctx.useSeeding = true;
ctx.useSchema = true;
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
}
var agent = new SyncAgent(this.clientProvider, this.serverProvider, this.sqlTables);
agent.LocalProvider.OnConnectionOpen(args =>
{
var keyCommand = args.Connection.CreateCommand();
keyCommand.CommandText = "PRAGMA key = 'password';";
keyCommand.ExecuteNonQuery();
});
var s = await agent.SynchronizeAsync();
Assert.Equal(109, s.TotalChangesDownloaded);
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
}
public async Task Sync_One_Row_From_Client()
{
using (var sqlConnection = new SqlConnection(fixture.ClientDatabaseString))
{
var script = @"INSERT [ServiceTickets]
([ServiceTicketID], [Title], [Description], [StatusValue], [EscalationLevel], [Opened], [Closed], [CustomerID])
VALUES
(N'CF3096E5-9057-4B29-8DBB-5D9D996643BD', N'Insert line into client', N'Description client', 1, 0, getdate(), NULL, 1)";
using (var sqlCmd = new SqlCommand(script, sqlConnection))
{
sqlConnection.Open();
sqlCmd.ExecuteNonQuery();
sqlConnection.Close();
}
}
SqlSyncProvider serverProvider = new SqlSyncProvider(fixture.ServerDatabaseString);
SqlSyncProvider clientProvider = new SqlSyncProvider(fixture.ClientDatabaseString);
ServiceConfiguration configuration = new ServiceConfiguration(new string[] { "ServiceTickets" });
SyncAgent agent = new SyncAgent(clientProvider, serverProvider, configuration);
var session = await agent.SynchronizeAsync();
Assert.Equal(0, session.TotalChangesDownloaded);
Assert.Equal(1, session.TotalChangesUploaded);
}
public async Task DeprovisionStoredProcedures()
{
agent = new SyncAgent(clientProvider, serverProvider, fixture.Tables);
// Overwrite configuration to apply the schem on the database, even if we have already made a sync before
agent.DatabaseApplying += (s, e) => e.OverwriteSchema = true;
var session = await agent.SynchronizeAsync();
await clientProvider.DeprovisionAsync(agent.Configuration, SyncProvision.StoredProcedures);
using (var sqlConnection = new SqlConnection(fixture.Client1ConnectionString))
{
sqlConnection.Open();
string commandText = "Select count(*) from sys.tables";
using (var cmd = new SqlCommand(commandText, sqlConnection))
{
int nb = (int)cmd.ExecuteScalar();
Assert.Equal(5, nb);
}
commandText = "Select count(*) from sys.procedures";
using (var cmd = new SqlCommand(commandText, sqlConnection))
{
int nb = (int)cmd.ExecuteScalar();
Assert.Equal(0, nb);
}
sqlConnection.Close();
}
}
private static async Task SynchronizeAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Getting a JWT token
// This sample is NOT SECURE at all
// You should get a Jwt Token from an identity provider like Azure, Google, AWS or other.
var token = GenerateJwtToken("*****@*****.**", "SPERTUS01");
HttpClient httpClient = new HttpClient();
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);
// Adding the HttpClient instance to the web client orchestrator
var serverOrchestrator = new WebRemoteOrchestrator("https://localhost:44342/api/sync", client: httpClient);
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverOrchestrator);
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public async Task DeprovisionAllExceptTables()
{
agent = new SyncAgent(clientProvider, serverProvider, fixture.Tables);
agent.DatabaseApplying += (s, e) => e.OverwriteSchema = true;
var session = await agent.SynchronizeAsync();
await clientProvider.DeprovisionAsync(agent.Configuration,
SyncProvision.Scope | SyncProvision.StoredProcedures | SyncProvision.TrackingTable | SyncProvision.Triggers);
using (var sqlConnection = new SqlConnection(fixture.Client1ConnectionString))
{
sqlConnection.Open();
string commandText = "Select count(*) from sys.tables";
using (var cmd = new SqlCommand(commandText, sqlConnection))
{
int nb = (int)cmd.ExecuteScalar();
Assert.Equal(2, nb);
}
commandText = "Select count(*) from sys.procedures";
using (var cmd = new SqlCommand(commandText, sqlConnection))
{
int nb = (int)cmd.ExecuteScalar();
Assert.Equal(0, nb);
}
commandText = "Select count(*) from sys.triggers";
using (var cmd = new SqlCommand(commandText, sqlConnection))
{
int nb = (int)cmd.ExecuteScalar();
Assert.Equal(0, nb);
}
sqlConnection.Close();
}
}
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);
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// First DownloadOnly provider for Sqlite
var clientSqliteProvider = new SqliteSyncDownloadOnlyProvider("adv.db");
// Second DownloadOnly provider for SqlServer
var clientSqlServerProvider = new SqlSyncDownloadOnlyProvider(clientConnectionString);
do
{
// Creating an agent that will handle all the process
var agentSqlite = new SyncAgent(clientSqliteProvider, serverProvider);
var sqliteResults = await agentSqlite.SynchronizeAsync(tables);
Console.WriteLine(sqliteResults);
var agentSqlServer = new SyncAgent(clientSqlServerProvider, serverProvider);
var sqlServerResults = await agentSqlServer.SynchronizeAsync(tables);
Console.WriteLine(sqlServerResults);
} 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 SqlSyncProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
// Tables involved in the sync process:
var tables = allTables;
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, tables);
// Using the Progress pattern to handle progession during the synchronization
var progress = new Progress <ProgressArgs>(s => Console.WriteLine($"[client]: {s.Context.SyncStage}:\t{s.Message}"));
// Setting configuration options
agent.SetConfiguration(s =>
{
s.ScopeInfoTableName = "tscopeinfo";
s.SerializationFormat = Dotmim.Sync.Enumerations.SerializationFormat.Binary;
s.StoredProceduresPrefix = "s";
s.StoredProceduresSuffix = "";
s.TrackingTablesPrefix = "t";
s.TrackingTablesSuffix = "";
});
agent.SetOptions(opt =>
{
opt.BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "sync");
opt.BatchSize = 100;
opt.CleanMetadatas = true;
opt.UseBulkOperations = true;
opt.UseVerboseErrors = false;
});
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
// Launch the sync process
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");
}
public async Task TableWithForeignKeyButNotParentTable()
{
serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
clientProvider = new SqlSyncProvider(fixture.Client2ConnectionString);
var simpleConfiguration = new SyncConfiguration(new[] {
"ProductCategory",
"Product"
});
agent = new SyncAgent(clientProvider, serverProvider, simpleConfiguration);
var session = await agent.SynchronizeAsync();
Assert.Equal(3, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
int fkeysCount = 0;
using (var sqlConnection = new SqlConnection(fixture.Client2ConnectionString))
{
using (var sqlCmd = new SqlCommand("select count(*) from sys.foreign_keys", sqlConnection))
{
sqlConnection.Open();
fkeysCount = (int)sqlCmd.ExecuteScalar();
sqlConnection.Close();
}
}
Assert.Equal(2, fkeysCount);
}
private static async Task SyncAdvAsync()
{
// Sql Server provider, the master.
var serverProvider = new SqlSyncProvider(@"Server=tcp:127.0.0.1,5433;Initial Catalog=Usavc.Services.AppointmentDb;User Id=sa;Password=Pass@word");
//Server=192.168.5.125;Database=medman;UID=pub; PWD=0c95PYIg;
// Sql Client provider for a Sql Server <=> Sql Server
var clientProvider = new SqlSyncProvider("Server=tcp:127.0.0.1,5433;Initial Catalog=Usavc.Services.AppointmentDb1;User Id=sa;Password=Pass@word");
//Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;
//Server=tcp:127.0.0.1,5433;Initial Catalog=Usavc.Services.AppointmentDb;User Id=sa;Password=Pass@word
// Tables involved in the sync process:
var tables = new string[] { "ReasonCode" };
// Sync orchestrator
var agent = new SyncAgent(clientProvider, serverProvider, tables);
do
{
var s = await agent.SynchronizeAsync(SyncType.Normal);
Console.WriteLine($"Total Changes downloaded : {s.TotalChangesDownloaded}");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
}
static async Task PerformSync()
{
var serverProvider = new SqlSyncProvider(
@"Data Source=.\sqlexpress;
Initial Catalog=AdventureWorks;
Integrated Security=true;");
var clientProvider = new SqliteSyncProvider("advworks.db");
var tablesToSync = new string[] {
"ProductCategory",
"ProductDescription", "ProductModel",
"Product", "ProductModelProductDescription",
"Address", "Customer", "CustomerAddress",
"SalesOrderHeader", "SalesOrderDetail"
};
var agent = new SyncAgent(clientProvider, serverProvider, tablesToSync);
var progress = new Progress <ProgressArgs>(s =>
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}"));
do
{
var context = await agent.SynchronizeAsync(progress);
Console.WriteLine($"Total Changes downloaded: \t{context.TotalChangesDownloaded}");
Console.WriteLine($"Total Changes Uploaded: \t{context.TotalChangesUploaded}");
Console.WriteLine($"Total Changes Conflicts: \t{context.TotalSyncConflicts}");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
}
private static async Task SyncAdvAsync()
{
// Sql Server provider, the master.
var serverProvider = new SqlSyncProvider(
@"Data Source=.;Initial Catalog=AdventureWorks;User Id=sa;Password=Password12!;");
// Sqlite Client provider for a Sql Server <=> Sqlite sync
var clientProvider = new SqliteSyncProvider("advworks2.db");
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory",
"ProductDescription", "ProductModel",
"Product", "ProductModelProductDescription",
"Address", "Customer", "CustomerAddress",
"SalesOrderHeader", "SalesOrderDetail" };
// Sync orchestrator
var agent = new SyncAgent(clientProvider, serverProvider, tables);
do
{
var s = await agent.SynchronizeAsync();
Console.WriteLine($"Total Changes downloaded : {s.TotalChangesDownloaded}");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
}