How to Synchronize relational databases with Dotmim.Sync

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

Comments

[anumters]

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.

[Rami Gamal]

Could I use it to sync two SQLite databases?

[Pradeep Ekanayaka]

Hi, Sebastian, i tried to sync two MySQL databases, but failed,
any sample codes for 2 MySQL database sync
C#

[Hunar A.Ahmad]

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?

[Sébastien Pertus]

of course !