PopulateMySQL.cs

private void Populate(DataTable dt, string tableName)
{
    if (dt.Rows.Count == 0) return;
    try
    {
        string truncateQuery = "Truncate Table `" + tableName + "`;"; //First truncate the table
        using (MySqlConnection connection = new MySqlConnection(_connectionString))
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                connection.Open();
                command.Connection = connection;
                command.CommandText = truncateQuery.ToString();
                command.ExecuteNonQuery();
            }
        }

        StringBuilder sb = new StringBuilder($"INSERT INTO `" + tableName + "` (");
        StringBuilder sbValues = new StringBuilder();
        StringBuilder sbQuery = new StringBuilder();
        foreach (DataColumn column in dt.Columns)
        {
            sb.Append("`").Append(column.ColumnName).Append("`,");
        }
        sb.Remove(sb.Length - 1, 1);
        sb.Append(") VALUES ");
        int cnt = 0;
        foreach (DataRow row in dt.Rows)
        {
            cnt++;
            sbValues.Append("(");
            foreach (var col in row.ItemArray)
            {
                sbValues.Append(ConvertDataTypes(col)).Append(",");
            }
            sbValues.Remove(sbValues.Length - 1, 1);
            sbValues.Append("),");
            if (cnt == cBatchCount)
            {
                if (sbValues.Length > 0)
                {
                    sbValues.Remove(sbValues.Length - 1, 1);
                    sbQuery.Append(sb);
                    sbQuery.Append(sbValues);
                    using (MySqlConnection connection = new MySqlConnection(_connectionString))
                    {
                        using (MySqlCommand command = new MySqlCommand())
                        {
                            connection.Open();
                            command.Connection = connection;
                            command.CommandText = sbQuery.ToString();
                            command.ExecuteNonQuery();
                        }
                    }
                }
                sbQuery.Clear();
                sbValues.Clear();
                cnt = 0;
            }
        }
        if (sbValues.Length > 0)
        {
            sbValues.Remove(sbValues.Length - 1, 1);
            sb.Append(sbValues);
        }
        if (sbValues.Length > 0)
        {
            using (MySqlConnection connection = new MySqlConnection(_connectionString))
            {
                using (MySqlCommand command = new MySqlCommand())
                {
                    connection.Open();
                    command.Connection = connection;
                    command.CommandText = sb.ToString();
                    command.ExecuteNonQuery();
                }
            }
        }
    }
    catch (Exception ex)
    {
        WriteToConsole("Error: InsertData()" + "\n" + ex.ToString());
        throw ex;
    }
}