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

This is in continuation of the Part-1 solution to transfer SQL Server Database data to MySQL Database using .NET Core. If you have not read the first post, please go through it. To give you an overview, the part-1 solution gets all the SQL server database data and creates a serialized and compressed version of a dataset. The part-2 or final solution downloads the compressed file and populates the MySQL database. Let’s see how this is achieved.

Note: For the demo purpose, the .NET Core console application has code for both Part-1 and Part-2 solution.

First, added two more entries to appsetting.json file. Which are,

• MySQLConnectionString: MySQL database connection string.
• DestinationDirectoryPath: Path to store the downloaded compressed file.

So the complete appsetting.json file looks like,

The following nuget package needs to be installed as that enables .NET Core applications to communicate with MySQL servers.

MySql.Data.MySqlClient

Next, added a new class named MySQLDataImport. This class has the logic to populate the MySQL database from the serialized dataset. This class also needs to access IConfigurationRoot services to read appsetting.json settings.
The constant cBatchCount denotes that the insert statement will be executed in a batch of 2000. We also need to update Program.cs to add MySQLDataImport class to the service collection. I also added code to get the user input and run appropriate service (this is just for demo).

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

• The solution unzips the file and deserializes back to a dataset.
• Then, it loops through each of the dataset tables, creates MySQL compatible INSERT queries and executes them on the MySQL database. Before executing the queries, the solution disables the foreign key constraint check and also truncates the table.
• Once all the data is populated, the code enables the foreign key constraint check.

Now, let’s inspect various methods of MySQLDataImport class. This class has 9 methods.

• Import: This is an entry point for this class. The main() method calls this function to start the data import process. This method calls different private methods to populate the MySQL database.
  
• WriteToConsole: A simple method to write message on the console.

  private void WriteToConsole(string msg)
  {
      Console.WriteLine(msg);
  }
  

  
  
  
  
• DownloadFile: For the demo, the location of Source and destination directory is same but in a real project, it would be different. In a real project, the compressed file would be stored in the cloud or any server. In that case, a URL will be shared. This method will download the file from the provided URL and stores it at the location specified in the appsetting file. Currently, the console app is not using this method as URL is not supplied.
  
• UnZipFile: No rocket science here.
  
• DeserializeDataset: Again self-explanatory.
  
• TransferData: This method loops through the dataset. Inside the loop, calls Populate method to populate the MySQL database. Before starting the loop, it disables the foreign key constraint. This is important as we don’t know in which order the tables are populated in the dataset. It’s possible that a child table is ahead of the parent table in the dataset. If we don’t disable the foreign key constraint, we’ll get the exception. Once all the data is populated, it enables the foreign key constraint check.

• DisableForeignKey: This method enables/disables foreign key constraint on the MySQL database.
  
• Populate: This is the method where the magic happens. It loops through the all the rows and creates INSERT statement and execute them on MySQL database. To avoid any inconsistency, it first truncates the table. The method also calls ConvertDataTypes method to convert SQL column data type to equivalent MySQL data type.
  
  
  
  
  
• ConvertDataTypes: This method converts the SQL data type to equivalent MySQL data type.
  

That’s it. The complete solution is simple and easy to understand. It is tested against SQL database hosted on the cloud having millions of records. It does the job and can save your money. You can modify this tool and accommodate changes as per your requirement. You can find the complete code at Github.

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