Part 2: How to Synchronize relational databases with Dotmim.Sync
Tags: database, synchronization, sql, dotnet
Published: 2020-02-19 18:42:27

---
title: Part 2: How to Synchronize relational databases with Dotmim.Sync
published: true
description: We are continuing our journey to a complete project using Dotmim.Sync to implement a complete sync process between multiples clients database and one server hub
tags: #database #synchronization #sql #dotnet
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/6oqhvxmdey08ilvr0el2.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


In [Part 1](/mimetis/how-to-synchronize-relational-databases-with-dotmim-sync-5693), we saw the most straightforward way to synchronize a server hub database with any kind of client database.
This first sample was based on a simple `tcp` connection between the server and the client.
Obviously, in a real world scenario, you don't expose your hub database directly.

Rather than exposing your database *to the world*, it could be better to protect it via a web proxy, usually a Web API.

To sum up, in [Part 1](/mimetis/how-to-synchronize-relational-databases-with-dotmim-sync-5693), we saw how to use the `SqlSyncProvider` and `SqliteSyncProvider` providers to communicate with our databases (client and server), then we saw how to use a `SyncAgent` instance to launch a direct synchronization over `tcp`.

Today we are going to see how we can use the `WebRemoteOrchestrator` proxy from the client side and the `WebServerAgent` from the server side, to allow a smooth sync process from any client and our server, through a web api, managed by a simple **ASP.Net Core Web Api** project.

As a reminder, here is the **console** application we've created. 
It's our base project:

``` cs
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(setup, progress:progress);
            Console.WriteLine(syncContext);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    } while (Console.ReadKey().Key != ConsoleKey.Escape);
}
```

Now, we're going to separate the *server side* and the *client side*.

# Server Side

First of all, we need to create a **Web API** project to support our server hub proxy.
You have multiples choices to create a Web API project within **.Net Core**:
- Using the CLI
- Using Visual Studio
- Using Visual Studio Code

You have a complete tutorial available here : https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-web-api

## Create the Web Api project

For this sample, we can use eiter the command lines or the Visual Studio wizard:

Command line:
``` bash
dotnet new webapi -o DotmimSyncWebServer
``` 

Visual Studio Wizard:
![Visual Studio Wizard](https://dev-to-uploads.s3.amazonaws.com/i/u3fg9sdz1p5s4fcoeql2.png)


Once restored, here is our final folder architecture:

![Web projects](https://dev-to-uploads.s3.amazonaws.com/i/p7gdbvhv5zjz49x4jicf.png)

## Install the Dotmim.Sync packages

Now, we need to add all the `Dotmim.Sync` components we need on the server side:
- A `SqlSyncProvider` for handling the communication between our **Sql Server** hub instance and the exposed web API.
- A `WebRemoteOrchestrator` proxy that will encapsulate all the call to the sync provider, using a `Serializer` (`Json` as default) to send and receive messages through `http`.

Using nuget, here are our final web project dependencies:

![Web server packages](https://dev-to-uploads.s3.amazonaws.com/i/h7n9114hiv3m0tmtgis0.png)

Obviously, you can add these nuget packages, through the console, if you're using **Visual Studio Code**:

``` bash
dotnet add package Dotmim.Sync.SqlServer
dotnet add package Dotmim.Sync.Web.Server
```

## Create a new empty sync controller

Then, we can create an empty controller called `SyncController` inside the `./Controllers` folder (you can delete the generated WeatherForecast controller if you want)

``` cs
namespace DotmimSyncWebServer.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class SyncController : ControllerBase
    {
    }
}
```

## Add the connection string to config file

Of course, we need to connect to the database, and we don't want to hard code the connection string. 
We can add this value to the `appsettings.json` file, already available in your root folder:

``` json
{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(localdb)\\mssqllocaldb; Initial Catalog=AdventureWorks; Integrated Security=true;"
  },

  "AllowedHosts": "*"
}
```

## Using Dependency injection to add the Sql Provider

We are using a default **ASP.Net Core** web template. So far, dependency injection is something really important and used a lot.
We will use the same approach to create our sync process.

The Server side is responsible of designing your sync schema. 
Once defined, this schema will be propagated to any client requesting it.

Here is our`ConfigureServices()` method in the `./startup.cs` file:

``` cs
public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    // [Required] Mandatory to be able to handle multiple sessions
    services.AddDistributedMemoryCache();
    services.AddSession(options => options.IdleTimeout = TimeSpan.FromMinutes(30));

    // [Required] Get a connection string for your server data source
    var connectionString = Configuration.GetSection("ConnectionStrings")["DefaultConnection"];

    // [Optional] Set the web server Options
    var options = new WebServerOptions()
    {
        BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "server"),
    };


    // [Required] Create the setup used for your sync process
    var tables = new string[] {"ProductCategory", "ProductModel", "Product",
            "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };

    // [Optional] Defines the schema prefix and suffix for all generated objects
    var setup = new SyncSetup(tables)
    {
        // optional :
        StoredProceduresPrefix = "server",
        StoredProceduresSuffix = "",
        TrackingTablesPrefix = "server",
        TrackingTablesSuffix = ""
    };

    // [Required] add a SqlSyncProvider acting as the server hub
    services.AddSyncServer<SqlSyncProvider>(connectionString, setup, options);
}
```

Notice that:
- We need a session system to handle multiple clients. That's why we're adding `services.AddSession()`.
- Only *tables* and *connection string* are mandatory. Everything else is optional.
- We're using the `AddSyncServer()` method to inject our provider in the Dependency Injection container.

## Adding handler to our Sync controller

The sync controller is really easy to implement, since everything is already handle under the cover.

We just need to:
- Create a `Post` method.
- Get our `WebServerAgent` instance from the dependency injection container, and then call the `HandleRequestAsync()` method.
- *[Optional]*: Create a `Get` method to show a default page when you call the `/sync` web page through your browser.

So far, here is the final `SyncController` code source:

``` cs
[Route("api/[controller]")]
[ApiController]
public class SyncController : ControllerBase
{
    private WebServerAgent webServerAgent;

    // Injected thanks to Dependency Injection
    public SyncController(WebServerAgent webServerAgent) => this.webServerAgent = webServerAgent;

    /// <summary>
    /// [Optional] This Get request is just here to show a default page
    /// </summary>
    [HttpGet]
    public async Task Get()
    {
        await this.HttpContext.WriteHelloAsync(webServerAgent);
    }

    /// <summary>
    /// [Required] This Post request is called by the all Dotmim.Sync.Web.Client apis
    /// </summary>
    [HttpPost]
    public async Task Post()
    {
        await webServerAgent.HandleRequestAsync(this.HttpContext);
    }
}
```
That's all you need !

If you launch the `DotmimSyncWebServer` project and point to the `/api/sync` web page, you should have this kind of page:


![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/xh8xb7rnxq1cmtlyd21c.png)

*Note*: As you can see, a lot of confidential information are shown here. 
- This information disappears when you go into production and your environment variable is set to production: `"ASPNETCORE_ENVIRONMENT": "Production"`
- You can disable it by removing the `public async Task Get()` method from your sync controller

# Client Side

The client side is pretty the same as our starter project.
We just need to:
- Add a the `Dotmim.Sync.Web.Client` package to be able to use the `WebRemoteOrchestrator` remote proxy.
- Change our server provider from `SqlServerProvider` to `WebRemoteOrchestrator`
- *[Optional]* : Remove our dependency to `Dotmim.Sync.SqlServer` since we are not using it anymore.


Here is the final result:

``` cs
var serverOrchestrator = new WebRemoteOrchestrator("https://localhost:44358/api/sync");
var clientProvider = new SqliteSyncProvider("advworks.db");;

// Using the IProgress<T> pattern to handle progession dring the synchronization
var progress = new SynchronousProgress<ProgressArgs>(s => Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}"));

// 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(progress);
    // Write results
    Console.WriteLine(s1);

} while (Console.ReadKey().Key != ConsoleKey.Escape);

Console.WriteLine("End");

```

As you can see here, no tables are specified from the client side. Everything related to the schema is handled by the server side.

Don't forget to run your web server api, before launching your sync.
Eventually, you should have something like this:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/b1wf9r7ojhv3ncwx1iub.png)


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