How to copy MySQL data from one database to another database in .net core?

MySQL backup and restore some times become hack-trick task because of unnecessary lines on code generated when exporting data from mysql workbench in individual file for tables. It becomes harder if there are large number of tables.

Following is a sample console application that will copy data from a source database table and bulk insert to a destination database table. It loops through all tables and perform the data transfer of all tables.

Install MySQLConnection nuget package.

using MySqlConnector;
using System.Data;


string schemaName = "db_dev";
string sourceConnectionString = "server=srchost;user id=admin;password=mypwd;persistsecurityinfo=True;database=db_dev;";
string destinationConnectionString = "server=desthost;user id=myadmin;password=mypass;persistsecurityinfo=True;database=destdb_dev;AllowLoadLocalInfile=true;";


int i = 0;
using (MySqlConnection sourceConnection = new MySqlConnection(sourceConnectionString))
{
    sourceConnection.Open();

    // Get the list of tables in the source database
    DataTable tables = sourceConnection.GetSchema("Tables");
    DataTable filteredRows = tables.Select($"TABLE_SCHEMA = '{schemaName}'").CopyToDataTable();
    foreach (DataRow row in filteredRows.Rows)
    {
        string tableName = row[2].ToString();
        int dataCount = Convert.ToInt32(row[7].ToString());
        i++;
        // Read data from the source table
        string selectQuery = $"SELECT * FROM `{schemaName}`.`{tableName}`";
        MySqlCommand selectCommand = new MySqlCommand(selectQuery, sourceConnection);
        if (dataCount > 0 && i>=0)
            using (MySqlDataReader reader = selectCommand.ExecuteReader())
            {
                var rows = reader.HasRows;
                using (MySqlConnection destinationConnection = new MySqlConnection(destinationConnectionString))
                {
                    destinationConnection.Open();

                    // Prepare the bulk insert command
                    using (MySqlTransaction transaction = destinationConnection.BeginTransaction())
                    {
                        MySqlBulkCopy bulkCopy = new MySqlBulkCopy(destinationConnection, transaction);
                        bulkCopy.DestinationTableName = tableName;
                        try
                        {
                            // Bulk insert data into the destination table
                            bulkCopy.WriteToServer(reader);
                            transaction.Commit();
                            Console.WriteLine($"Data from table {tableName} has been successfully transferred." +i.ToString());
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            Console.WriteLine($"Error transferring data from table {tableName}: {ex.Message}");
                        }
                    }
                }
            }
    }
}


Console.WriteLine("Hello, World!");

 

 

 

 

 

 

 

 

 

How to implement async CRUD methods for an Employee model using Dapper with MySQL

 

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using MySql.Data.MySqlClient;

using Dapper;

public class EmployeeRepository

{

    private readonly string _connectionString;

    public EmployeeRepository(string connectionString)

    {

        _connectionString = connectionString;

    }

    public async Task<int> CreateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "INSERT INTO Employees (FirstName, LastName, Email, PhoneNumber) " +

                           "VALUES (@FirstName, @LastName, @Email, @PhoneNumber);" +

                           "SELECT LAST_INSERT_ID()";

            return await connection.ExecuteScalarAsync<int>(query, employee);

        }

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees";

            return await connection.QueryAsync<Employee>(query);

        }

    }

    public async Task<Employee> GetEmployeeByIdAsync(int id)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees WHERE Id = @id";

            return await connection.QuerySingleOrDefaultAsync<Employee>(query, new { id });

        }

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, " +

                           "Email = @Email, PhoneNumber = @PhoneNumber WHERE Id = @Id";

            int rowsAffected = await connection.ExecuteAsync(query, employee);

            return rowsAffected > 0;

        }

    }

    public async Task<bool> DeleteEmployeeAsync(int id)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "DELETE FROM Employees WHERE Id = @id";

            int rowsAffected = await connection.ExecuteAsync(query, new { id });

            return rowsAffected > 0;

        }

    }

}