Transfer SQL Server Database data to MySQL Database using .NET Core – Part 1

Transferring one database data to other databases is not challenging when source and destination database type is same. It becomes a nightmare when source and destination database types are different. To save you from this nightmare, there are plenty of products available for data transfer between different types of databases. But, these tools come with a very high price. I also got into a similar problem and thought of buying one of the products. As you know in a typical software company, buying a licensed product is a time-taking process that requires lots of approvals and justifications. While the process was on, an idea struck and got the solution to transfer SQL Server Database data to MySQL Database using .NET Core.

Problem

We are building a multi-tenant application, where a tenant is having a central web application, a mobile application and a desktop application (runs on Linux). The desktop application when setup, it needs to download the data from the central database server. The central database server is SQL and desktop application database is MySQL. Here, creation of database structure is not a headache as “Entity Framework Core code first” takes care of it.

Solution

Transferring data over the internet was not an ideal choice as the cloud database is of medium size. There were also other important reasons like,

• Loss of internet connectivity
• Payload size
• Performance
• Loss of data and leaving database in an incomplete state
• Required a detailed audit log

These reasons were enough for us to look for an offline solution. It has to be an offline solution and need a way to download all the data in one go and then transfer to the desktop application for populating destination database. The implemented solution has 2 parts.

• Part-1: On the server run a tool to download all the SQL Server data in a dataset, serialize and compress it and save at a physical location.
• Part-2: Pass the compressed file to the desktop application. The desktop application decompresses it, converts into a dataset, loops through the dataset tables, create MySQL compatible insert statements (while transforming the Column data types) and execute those insert statements in batches to populate MySQL database.

The solution is tested against millions of records and it just works great. This solution works fine for my requirements, and I guess it will work for others also (but no guarantee). The best part is, you can download the code and include the missing part as per your requirements. In this post, I’ll cover the first part only.

As the actual development code can’t be shared, I create a replica of the same for sharing. It’s a .NET Core based console application. The following nuget packages need to be added to support DI in the console application.

"Microsoft.Extensions.DependencyInjection"
"Microsoft.Extensions.Configuration.Json"
"Microsoft.Extensions.Configuration.FileExtensions"
"Microsoft.Extensions.Configuration"

These packages are required to configure Dependency Injection and reading the configuration settings from appsetting file.
The DI container is not available out of the box for the .NET Core console application.

Next, add the appsetting.json file with the following entries.

Next, add a new class named DataExtractor. This class has the logic to get the data from SQL Server database and store it in a dataset. This class needs to access IConfigurationRoot services to read appsetting.json settings.

public DataExtractor(IConfigurationRoot config)
{ }

public void ExtractData()
{ }

Next, configure the services for DI and configuration.

class Program
{
    static void Main(string[] args)
    {
        var services = new ServiceCollection();
        ConfigureServices(services);
        var provider = services.BuildServiceProvider();
        Console.WriteLine("** Welcome to Data Transfer tool **");
        try
        {
             var deService = provider.GetRequiredService<DataExtractor>();
             deService.ExtractData(); //Execute the class.   
             Console.WriteLine("** Process Completed.. Press any key to exit. **");
        }
        catch (Exception ex)
        {
            Console.WriteLine("** Exception Occured.. **");
            Console.WriteLine(ex.ToString());
        }
        Console.ReadLine();
    }

    private static void ConfigureServices(IServiceCollection serviceCollection)
    {
        serviceCollection.AddTransient<DataExtractor>();  //Add application
        var configuration = new ConfigurationBuilder() //Build configuration
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsetting.json", false)
            .Build();

        serviceCollection.AddSingleton<IConfigurationRoot>(configuration);
    }
}

The above code does the following things:

• We need to add our dependencies to the container collection, and the dependencies can have a lifetime of Scoped, Transient or Singleton. Read this article to find out more about these lifetime values. The ConfigureServices method does the same.
• Here, the ConfigurationBuilder object sets an absolute path scoped to the project directory and add the appsettings.json file to IConfigurationRoot object, and returns IConfigurationRoot object.
• Once the ServiceCollection object is configured, we can request an IServiceProvider (Dependency Management Container) from the ServiceCollection object in order to manually resolve DataExtractor class and execute ExtractData() method to kick off the app. See Main() method code.

Once this is done, we can inject the IConfigurationRoot service in DataExtractor class and read the appsetting values.

Before we go into the code of DataExtractor class, let me first outline the implemented solution.

• The solution uses ADO.NET to connect to SQL database and gets all the data and keep in a dataset.
• It uses XML serialization to serialize the dataset and stores at a physical location. The location can be either cloud or server. The code also applies gzip compression to reduce the file size and save the compressed version.
• This compressed file will be used to populate the MySQL database. It is covered in part-2 post.

Now, let’s take a closer look at various methods of DataExtractor.cs class. This class has 4 methods,

• ExtractData(): This is an entry point for this class. The main() method calls this function to start the process. This method calls different private methods to generate a dataset and serialize it and store it at the location specified in the appsetting file.
  
• GetTableList(): This method gets a list of all the tables from the target database and returns the same. The code is very familiar and self-explanatory if you have used ADO.NET ever.

• FetchData(): This method fetches the data for a specified table and returns the same. The caller method (ExtractData) adds the returned datatable to a dataset.
  
• PrepareZip(): This method uses gzip compression to compress the XML file. Once the compressed version is created, the original XML file is deleted.
  

That’s it. This class has very simple and self-explanatory code.

You can modify the queries to exclude some tables or columns as sometimes you don’t want to replicate everything. Of course, the source database design should denote which tables and columns to exclude.

Summary

To conclude, this post talks about the first part of the solution for transferring the SQL server database data to a MySQL database. It also shows how to configure DI and access configuration in .NET Core console application. The DataExtractor class fetches the data from SQL database, serialize and compress it and then store it at a physical location. In the next part, we’ll see how to use this XML file to populate MySQL database.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

PS: If you found this content valuable and want to return the favour, then