C#实现将Mysql数据迁移到SQL数据库

最新推荐文章于 2024-06-03 08:50:22 发布

AI程序猿

最新推荐文章于 2024-06-03 08:50:22 发布

阅读量351

点赞数 1

文章标签：数据库 c# mysql

本文链接：https://blog.csdn.net/qq_33790894/article/details/134460523

版权

前提源数据库及目标数据库需要表结构一致

using System;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace MysqlToSQL
{

    public partial class Form1 : Form
    {
        private static string mysqlConnString = "";
        private static string sqlConnString = "";
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (txb_mysql_connstring.Text.Trim() == "" || txb_sql_connstring.Text.Trim() == "")
            {
                MessageBox.Show("请填写连接字符串!");
                return;
            }

            mysqlConnString = txb_mysql_connstring.Text;
            sqlConnString = txb_sql_connstring.Text;
            listBox1.Items.Clear();
            txb_sql.Text = "";
            using (MySqlConnection mysqlconn = new MySqlConnection(mysqlConnString))
            {
                mysqlconn.Open();
                string query = "Show Tables";
                using (MySqlCommand comm = new MySqlCommand(query, mysqlconn))
                {
                    using (MySqlDataReader reader = comm.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string tableName = reader.GetString(0);
                            listBox1.Items.Add(tableName);
                        }
                    }
                }
            }

        }
        static void ClearSQLServerTable(string TableName)
        {
            using (SqlConnection sqlconn = new SqlConnection(sqlConnString))
            {
                sqlconn.Open();
                using (SqlCommand sqlCommand = new SqlCommand("TRUNCATE TABLE " + TableName,sqlconn))
                {
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }
        static void CopyDataFromSQLToSQLServer(string TableName)
        {
            using (MySqlConnection mysqlconn = new MySqlConnection(mysqlConnString))
            using (SqlConnection sqlconn= new SqlConnection(sqlConnString))
            {
                mysqlconn.Open();
                sqlconn.Open();
                using (MySqlCommand mysqlcomm = new MySqlCommand("select * from " + TableName, mysqlconn))
                using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(mysqlcomm))
                {
                    using (MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter))
                    {
                        using (var dataTable = new System.Data.DataTable())
                        {
                            dataAdapter.Fill(dataTable);
                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn))
                            {
                                bulkCopy.DestinationTableName= TableName;
                                bulkCopy.WriteToServer(dataTable);
                            }
                        }
                    }
                }
            }
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem != null)
            {
                txb_sql.Text = listBox1.SelectedItem.ToString();
            }
            else
            {
                txb_sql.Text = "";
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (txb_sql.Text.Trim() == "")
            {
                MessageBox.Show("Select a Table!");
                return;
            }
            try
            {
                ClearSQLServerTable(txb_sql.Text.Trim());
                CopyDataFromSQLToSQLServer(txb_sql.Text.Trim());
                MessageBox.Show(txb_sql.Text + " Copy Done!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}