CODEWITHSUNDEEP
visual-studioasp.net-core
Corentin
Corentin

Reputation: 325

Best solution to synchronize 2 databases with .NetCore 2

I want to synchronise data from several tables from one database DB1 to my .NetCore app's database DB2 everyday.

I'm new to .Net Core. I have some skills with Java EE apps and Spring framework.

First Try :-)

My first idea was to do that with SOAP WebServices so I create a WCF projet in my solution following this example : https://www.youtube.com/watch?v=plRPBT3h3S8

Solution's content (in Visual Studio) :

Problems :

Second try :-)

My second idea was to do link the Service.svc (in WCF project 3) to project 1 with the WCF Web Service Reference Provider following this example : https://learn.microsoft.com/en-us/dotnet/core/additional-tools/wcf-web-service-reference-guide

Problems :

Questions

  1. Is one of these ideas are good to synchronize my database ?
  2. I heard about "Service Fabric". Could it be another solution to solve it ?
  3. Do you know other proper way to solve it with .NET Core ?

Thanks.

Upvotes: 0

Views: 5843

Answers (5)

César Qüeb
César Qüeb

Reputation: 121

Please, review following .NET project (open source) that allows synchronize databases using .NET with C#.

.NET Framework for syncing relational databases using C#

Please, visit Dotmin.Sync for more info.

Easy code to perform a synchronization task:

 // Sql Server provider, the "server" or "hub".
SqlSyncProvider serverProvider = new SqlSyncProvider(
    @"Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=true;");

// Sqlite Client provider acting as the "client"
SqliteSyncProvider clientProvider = new SqliteSyncProvider("advworks.db");

// Tables involved in the sync process:
var tables = new string[] {"ProductCategory", "ProductDescription", "ProductModel", "Product", "ProductModelProductDescription",
                           "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };

// Sync agent
SyncAgent agent = new SyncAgent(clientProvider, serverProvider, tables);

do
{
    var result = await agent.SynchronizeAsync();
    Console.WriteLine(result);

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

Regards

Upvotes: 3

user2290774
user2290774

Reputation: 9

I use this library which synchronizes multiple databases.

https://www.nuget.org/packages/SyncData_dotNET/

    // Tables involved in the sync process:
    var tables = new string[] { "anagrafica" };

    string clientConnectionString = @"Data Source=" + server + "; Database=" + database + "; User ID=" + username + "; Password=" + password + ";";
    string serverConnectionString = @"Data Source=" + server + "; Database=" + database + "; User ID=" + username + "; Password=" + password + ";";

    // Sqlite Client provider for a MySql <=> Sqlite sync
    SQLiteConnection clientProvider = new SQLiteConnection(clientConnectionString);

    // MySql Server provider, the master.
    MySqlConnection serverProvider = new MySqlConnection(serverConnectionString);

    // Launch the sync process
    var result = await Sync.Synchronize(clientProvider, serverProvider, tables);

Upvotes: 0

Corentin
Corentin

Reputation: 325

I finally resolved this situation using an API REST following this tutorial :

https://learn.microsoft.com/fr-fr/aspnet/core/tutorials/first-web-api?view=aspnetcore-2.2&tabs=visual-studio

And I have developed a console application that request this API (post request with an HttpClient) to update my Application's database.

This program will be called by a planned task (CRON) to synchronise database everyday.

Upvotes: 0

GlennSills
GlennSills

Reputation: 4177

I think the design of the databases will have a much greater impact on the difficulty of this effort than anything else. If it is easy to identify rows in Db1 that aren't in DB2 and visa versa the code is trivial. I wouldn't bother with a multi-tiered application - keep things as simple as possible. Just write a console application. Later on you can schedule it with the task manager on Windows Server, or cron on Linux.

Upvotes: 1

Vova Bilyachat
Vova Bilyachat

Reputation: 19494

Well I dont see any best solution it really depends. But there are some options.

You dont need to load any of your old .net framework code in .net core for db sync, all you need to do connect from .net core to database to load data. Yes you could have code duplication but at least your solution will be in .net core where you control whole process. In addition If you would use EF be careful how you load data and manipulate memory.

Second option which I would use is SSDT project. You will write data transformation process loading from any source you wish, I assume it even supports text files. Also it can connect to different database providers so you can load from MySql for instance and write to SqlServer (In one of my projects I've done Postgres to mssql). Benefit of this solution that you can do easily different flows error handling.

And last one could be simple stored procedure if you have databases on same server you could create new user which will have read access to DB1 and read/write to DB2, then using insert/from or update/from statements.

Here is no silver bullet so you need to try your self because there is many factors.

Upvotes: 1

Related Questions