DEV Community

Cover image for Part 2: How to Synchronize relational databases with Dotmim.Sync
Sébastien Pertus
Sébastien Pertus

Posted on • Edited on

Part 2: 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

In Part 1, 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, 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:

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);
}
Enter fullscreen mode Exit fullscreen mode

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:

dotnet new webapi -o DotmimSyncWebServer
Enter fullscreen mode Exit fullscreen mode

Visual Studio Wizard:
Visual Studio Wizard

Once restored, here is our final folder architecture:

Web projects

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

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

dotnet add package Dotmim.Sync.SqlServer
dotnet add package Dotmim.Sync.Web.Server
Enter fullscreen mode Exit fullscreen mode

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)

namespace DotmimSyncWebServer.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class SyncController : ControllerBase
    {
    }
}
Enter fullscreen mode Exit fullscreen mode

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:

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

  "AllowedHosts": "*"
}
Enter fullscreen mode Exit fullscreen mode

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 ourConfigureServices() method in the ./startup.cs file:

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);
}
Enter fullscreen mode Exit fullscreen mode

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:

[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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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

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:

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");

Enter fullscreen mode Exit fullscreen mode

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

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

Latest comments (4)

Collapse
 
sojan1 profile image
sojan1

Dotmim.Sync.SyncException: '[NeedsToUpgradeAsync]..[GetConnectionAsync]..Instance failure.'

do you know anything about this error

Image description

Collapse
 
theryan1 profile image
TheRyan1

Awesome article! How would you pass params from the client to the server if you only wanted to sync rows that meet a condition. The documentation example just does not work at all. Have you tried this before?

Collapse
 
gaurakshay profile image
Akshay Gaur

This is amazing! I wonder if it is possible to add authorization to the Web API so that only authorized requests will be served?

Collapse
 
mimetis profile image
Sébastien Pertus

Yes, You just have to secure your web api endpoint like any ohter web api hosted in ASP.Net core

On the client side, you have access to the HttpClient instance easily, so just add your bearer token to the header and you're done !