How to Synchronize relational databases with Dotmim.Sync
Tags: database, synchronization, sql, dotnet
Published: 2020-02-04 17:41:15

---
title: How to Synchronize relational databases with Dotmim.Sync
published: true
description: Presentation of the Dotmim.Sync framework. Developed on .Net Standard 2.0, this frameworks allows you to sync multiples databases with a central hub database. It works with several data providers, like Sql Server, MySQL or SQLite.
tags: Database, Synchronization, Sql, dotnet
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/nofmnxxe9thwszhw51o6.jpg
---

This post is part of a series about [Dotmim.Sync](https://github.com/Mimetis/Dotmim.Sync) on how to synchronize relations databases between a server hub and multiple client members:
- [Part 1](/mimetis/how-to-synchronize-relational-databases-with-dotmim-sync-5693): Introduction to Dotmim.Sync: Hello Sync !
- [Part 2](/mimetis/how-to-synchronize-relational-databases-with-dotmim-sync-part-2-1gba): 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](https://github.com/Mimetis/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 
-------------|---------------------|--------------------
![](https://dev-to-uploads.s3.amazonaws.com/i/ebitucdjb0szrk0fjg2f.png) | ![](https://dev-to-uploads.s3.amazonaws.com/i/ixn1otl3gj5gzw69qmsu.png) | ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jxavsazmdal2li9kpfhe.png)


# 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](https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql) 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](https://dotnet.microsoft.com/download) with the `Dotmim.Sync` nuget packages installed (depending on the targeted databases)

![Dotmim.Sync nuget packages](https://dev-to-uploads.s3.amazonaws.com/i/9va1g8beb64mi91gb95h.png)

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)

![Sqlite database configured with tracking tables and triggers](https://dev-to-uploads.s3.amazonaws.com/i/4yr3ncahlgpupkly4otx.png)

2) Get all the data from the server and applied them in the SQLite database

![Rows available in SQLite after first sync](https://dev-to-uploads.s3.amazonaws.com/i/4hwtzfmw60fcw5l4it1j.png)

# 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](https://twitter.com/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