This post is part of a series about Dotmim.Sync on how to synchronize relations databases between a server hub and multiple client members:
- Part 1: Introduction to Dotmim.Sync: Hello Sync !
- Part 2: Protecting your hub behind a web API, through ASP.Net Core Web Api
You say ... Dotmim... Sync ?
Back in the days, Microsoft released a full C# framework to allow databases synchronization, called the Sync Framework.
Today, (part of) this framework is open source but it lacks a lot of features to meet current mobile development needs, and a solid cross platform compatibility (and to be honest, a better ease of implementation without any code generation and pre-work)
The Dotmim.Sync framework is inspired from this old framework, without all the complications, glitches or code generation and adding some cool features.
The Dotmim.Sync
framework works on all platforms supporting .Net Standard 2.0 librairies.
You can use it in a .Net Core application running on Windows or Linux, or you can use it embedded in your Xamarin application for iOS or Android.
Many features are available through this framework:
- Multi databases support (SQL Server, MYSQL, SQLite)
- Conflict resolution
- Snapshot initialization
- Filters
- Custom serializers
- Mode TCP and HTTP through ASP.NET Core Web API
- Sync Direction (Bidirectional, DownloadOnly, UploadOnly)
- SQL Server only: Support of change tracking / bulk operations with TVP
And more to come :)
Multi Databases | Cross Plaform | .Net Standard 2.0 |
---|---|---|
Hello Sync !
One of the main objectives of the Dotmim.Sync
framework is to be able to write your first synchronization with less than 5 lines of code.
Let's do it.
What you need so far for your first "Hello Sync" project:
- A SQL Server / MySQL database up and running : Use this AdventureWorks script if you need something for testing purpose (and all the samples will be based on this database)
- A blank console application on top of .Net Core with the
Dotmim.Sync
nuget packages installed (depending on the targeted databases)
We're going to create a sync between a server database hosted on SQL Server and a SQLite client database.
The Dotmim.Sync
framework is built on database providers.
For instance, if you need to sync a SQL Server database, you will have to install the SqlSyncProvider
provider.
For MySql, use the MySqlSyncProvider
provider and of course for SQLite, the SqliteSyncProvider
provider.
Each provider will manage the communication between the Dotmim.Sync
core components and your database.
More providers will be (hopefully) developed later (like Oracle or PostgreSQL)
Now you have everything installed, let's go coding !
We need:
- A
SqlSyncProvider
to communicate with the server databse. - A
SqliteSyncProvider
to communicate with the client database. - A
SyncSetup
containing the tables you want to synchronize. - A
SyncAgent
that will orchestrate the whole sync processus.
csharp
var serverProvider = new SqlSyncProvider(GetDatabaseConnectionString("AdventureWorks"));
var clientProvider = new SqliteSyncProvider("advworks.db");
var setup = new SyncSetup("ProductCategory", "ProductModel", "Product", "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail");
var agent = new SyncAgent(clientProvider, serverProvider);
var syncContext = await agent.SynchronizeAsync(setup);
Console.WriteLine(syncContext);
As you can see, your SyncAgent
instance has references on both server side and client side, plus the tables list.
Once initialized, just called the asynchronous method SynchronizeAsync()
and you're done !
The result from this first run should looks like this:
bash
Synchronization done.
Total changes downloaded: 2752
Total changes uploaded: 0
Total conflicts: 0
Total duration :0:0:4.280
Done
First synchronization done, with 5 lines of code :)
Note: The SQLite database did not exists before the sync process happened.
During the first sync, the Dotmim.Sync
has:
1) Created the SQLite database, get the schema, created all the tables, then created all the required components (One tracking table per "synced" table, three triggers on each table)
2) Get all the data from the server and applied them in the SQLite database
Hello Sync V2
The next versin of this sample will use some nice features to get some feedbacks from your client database.
As SynchronizeAsync()
is an async method, we can use a IProgress<T>
to get feedbacks during the active sync:
csharp
var progress = new SynchronousProgress<ProgressArgs>(s => Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}"));
var syncContext = await agent.SynchronizeAsync(progress);
And because we want to run the sync again and again, we can make a nice do while
loop, with a little try catch
to ensure the application won't crash for some reasons :)
Here is the full code:
csharp
private static async Task SynchronizeAdventureWorksAsync()
{
var serverProvider = new SqlSyncProvider(GetDatabaseConnectionString("AdventureWorks"));
var clientProvider = new SqliteSyncProvider("advworks.db");
var setup = new SyncSetup("ProductCategory", "ProductModel", "Product", "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail");
var agent = new SyncAgent(clientProvider, serverProvider);
var progress = new SynchronousProgress<ProgressArgs>(s =>
Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}"));
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
var syncContext = await agent.SynchronizeAsync(setu, progress);
Console.WriteLine(syncContext);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
}
And now, before launching again, let's add a new ProductCategory
in the server database, and update a product
:
sql
Use AdventureWorks;
Update Product set Name = 'HL Road Frame - Black, 58 V2' where ProductId = 680;
Insert into ProductCategory (Name) Values ('Mono wheels');
Then launch again the sync process, you should have this result:
bash
Sync Start
BeginSession: 18:11:24.281
ScopeLoading: 18:11:24.410 Id:932af0e6-1083-4ec4-8b39-40453e3bc586 LastSync:04/02/2020 16:49:35 LastSyncDuration:42806712
TableChangesSelected: 18:11:24.732 ProductCategory Upserts:0 Deletes:0 TotalChanges:0
TableChangesSelected: 18:11:24.732 ProductModel Upserts:0 Deletes:0 TotalChanges:0
TableChangesSelected: 18:11:24.734 Product Upserts:0 Deletes:0 TotalChanges:0
TableChangesSelected: 18:11:24.735 Address Upserts:0 Deletes:0 TotalChanges:0
TableChangesSelected: 18:11:24.736 Customer Upserts:0 Deletes:0 TotalChanges:0
TableChangesSelected: 18:11:24.737 CustomerAddress Upserts:0 Deletes:0 TotalChanges:0
TableChangesSelected: 18:11:24.738 SalesOrderHeader Upserts:0 Deletes:0 TotalChanges:0
TableChangesSelected: 18:11:24.739 SalesOrderDetail Upserts:0 Deletes:0 TotalChanges:0
TableChangesApplied: 18:11:25.415 ProductCategory State:Modified Applied:1 Failed:0
TableChangesApplied: 18:11:25.418 Product State:Modified Applied:1 Failed:0
DatabaseChangesApplied: 18:11:25.421 Changes applied on database main: Applied: 2 Failed: 0
ScopeSaved: 18:11:25.433 Id:932af0e6-1083-4ec4-8b39-40453e3bc586 LastSync:04/02/2020 17:11:25 LastSyncDuration:11541150
EndSession: 18:11:25.434
Synchronization done.
Total changes downloaded: 2
Total changes uploaded: 0
Total conflicts: 0
Total duration :0:0:1.154
Based on this running application, you can now test some inserts or deletes in both databases, and see the results in your console !
If you need more information on the Dotmim.Sync
framework, do not hesitate to reach me out on twitter @sebpertus
The full documentation is available here : https://mimetis.github.io/Dotmim.Sync
The source code is hosted on Github here : https://github.com/Mimetis/Dotmim.Sync
Happy sync !
Seb
Top comments (4)
Dear Sebastian,
Great framework, great presentation.
I want to synch data (transactions) from multiple tables that occurred on different dates; say July 5th, July 6th, July 7th, July 8th, July 9th, etc, from the hub into the client database. However, I want to synch the data according to the date of occurrence. In most cases, I want to synch the data for each date process it, check and confirm that everything is fine before, syncing the data for the next date.
Checking and confirmation is done manually.
Please, any help on how I can achieve this business need using Dotmim.Sync framework is appreciated.
Hi, Sebastian, i tried to sync two MySQL databases, but failed,
any sample codes for 2 MySQL database sync
C#
Dear Sebastian, Can I use this library with WinForms to sync two SQL Server Databases, one of them is localdb and the other one is on the server to make my application works in online/offline mode?
of course !