在现代软件开发中，数据库的迁移是一项常见的任务，尤其是在项目需要从MySQL迁移到SQL Server时。本文将详细介绍一个使用C#编写的数据库迁移工具，它的主要功能是将MySQL数据库中的表结构和数据迁移到SQL Server中。该工具支持获取MySQL表的详细架构信息、创建SQL Server表及其主键和注释支持，并批量迁移数据。

代码实现

Nuget 安装MySql.Data

以下是完整的数据库迁移工具的代码：

C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace AppMySQLToSqlserver
{
    public class DatabaseMigrator
    {
        private string mysqlConnectionString;
        private string sqlServerConnectionString;

        public DatabaseMigrator(string mysqlConn, string sqlServerConn)
        {
            mysqlConnectionString = mysqlConn;
            sqlServerConnectionString = sqlServerConn;
        }

        /// <summary>  
        /// 获取MySQL表的详细架构信息  
        /// </summary>  
        private DataTable GetMySqlTableDetailSchema(string tableName)
        {
            using (MySqlConnection connection = new MySqlConnection(mysqlConnectionString))
            {
                connection.Open();

                string schemaQuery = $@"  
        SELECT   
            COLUMN_NAME AS `Field`,   
            COLUMN_TYPE AS `Type`,   
            CASE WHEN IS_NULLABLE = 'YES' THEN 'YES' ELSE 'NO' END AS `Null`,   
            COLUMN_KEY AS `Column_Key`,  
            COLUMN_DEFAULT AS `Default`,  
            EXTRA AS `Extra`,  
            COLUMN_COMMENT AS `Comment`  
        FROM   
            INFORMATION_SCHEMA.COLUMNS  
        WHERE   
            TABLE_SCHEMA = DATABASE()   
            AND TABLE_NAME = '{tableName}'  
        ORDER BY   
            ORDINAL_POSITION";

                using (MySqlCommand cmd = new MySqlCommand(schemaQuery, connection))
                {
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                    {
                        DataTable schemaTable = new DataTable();
                        adapter.Fill(schemaTable);
                        return schemaTable;
                    }
                }
            }
        }

        /// <summary>  
        /// 在SQL Server创建表，包括主键和注释支持  
        /// </summary>  
        private void CreateSqlServerTable(string tableName, DataTable schema)
        {
            using (SqlConnection connection = new SqlConnection(sqlServerConnectionString))
            {
                connection.Open();

                StringBuilder createTableSql = new StringBuilder($"CREATE TABLE [{tableName}] (\n");
                string primaryKeyColumn = "";

                foreach (DataRow row in schema.Rows)
                {
                    string columnName = row["Field"].ToString();
                    string dataType = ConvertMySqlTypeToSqlServerType(row["Type"].ToString());
                    string nullable = row["Null"].ToString() == "YES" ? "NULL" : "NOT NULL";
                    string defaultValue ="";
                    if (row["Default"] != DBNull.Value)
                    {
                        string defaultStr = row["Default"].ToString();
                        if (defaultStr != "#" && !string.IsNullOrWhiteSpace(defaultStr.Replace("#", "")))
                        {
                            if (dataType.Contains("NVARCHAR") || dataType.Contains("NCHAR") || dataType.Contains("TEXT"))
                            {
                                defaultStr = defaultStr.Replace("#", "");
                                if (!defaultStr.StartsWith("N'") && !defaultStr.StartsWith("'"))
                                    defaultValue = $"DEFAULT N'{defaultStr}'";
                                else
                                    defaultValue = $"DEFAULT {defaultStr}";
                            }
                            else
                            {
                                defaultStr = defaultStr.Replace("#", "");
                                defaultValue = $"DEFAULT {defaultStr}";
                            }
                        }
                    }

                    string comment = row["Comment"] != DBNull.Value ? row["Comment"].ToString() : "";
                    string columnKey = row["Column_Key"].ToString();

                    // 识别主键  
                    if (columnKey == "PRI")
                    {
                        primaryKeyColumn = columnName;
                        nullable = "NOT NULL";
                    }

                    createTableSql.Append($"    [{columnName}] {dataType} {nullable} {defaultValue},\n");
                }

                // 添加主键约束  
                if (!string.IsNullOrEmpty(primaryKeyColumn))
                {
                    createTableSql.Append($"    CONSTRAINT [PK_{tableName}] PRIMARY KEY CLUSTERED ([{primaryKeyColumn}] ASC)\n");
                }

                createTableSql.Append(");\n");

                // 添加表注释（SQL Server使用扩展属性）  
                string addTableCommentSql = $@"  
                EXEC sp_addextendedproperty   
                    @name = N'MS_Description',   
                    @value = N'从MySQL迁移的表',   
                    @level0type = N'SCHEMA',   
                    @level0name = N'dbo',   
                    @level1type = N'TABLE',   
                    @level1name = N'{tableName}';  
            ";

                // 添加列注释  
                StringBuilder columnCommentsSql = new StringBuilder();
                foreach (DataRow row in schema.Rows)
                {
                    string columnName = row["Field"].ToString();
                    string comment = row["Comment"] != DBNull.Value ? row["Comment"].ToString() : "";

                    if (!string.IsNullOrEmpty(comment))
                    {
                        columnCommentsSql.Append($@"  
                        EXEC sp_addextendedproperty   
                            @name = N'MS_Description',   
                            @value = N'{comment}',   
                            @level0type = N'SCHEMA',   
                            @level0name = N'dbo',   
                            @level1type = N'TABLE',   
                            @level1name = N'{tableName}',   
                            @level2type = N'COLUMN',   
                            @level2name = N'{columnName}';  
                    ");
                    }
                }

                // 执行建表语句  
                using (SqlCommand cmd = new SqlCommand(createTableSql.ToString() + addTableCommentSql + columnCommentsSql.ToString(), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>  
        /// 获取字段长度  
        /// </summary>  
        private int GetColumnLength(string mysqlType)
        {
            int start = mysqlType.IndexOf('(');
            int end = mysqlType.IndexOf(')');

            if (start != -1 && end != -1)
            {
                string lengthStr = mysqlType.Substring(start + 1, end - start - 1);
                return int.Parse(lengthStr);
            }

            return 255; // 默认长度  
        }

        /// <summary>  
        /// 数据类型转换（增强版）  
        /// </summary>  
        private string ConvertMySqlTypeToSqlServerType(string mysqlType)
        {
            mysqlType = mysqlType.ToLower();

            if (mysqlType.Contains("int") && mysqlType.Contains("unsigned"))
                return "BIGINT";
            else if (mysqlType.Contains("int"))
                return "INT";
            else if (mysqlType.Contains("varchar"))
            {
                if (GetColumnLength(mysqlType) > 4000)
                {
                    return "NVARCHAR(MAX)";
                }
                else
                {
                    return $"NVARCHAR({GetColumnLength(mysqlType)})";
                }
            }
            else if (mysqlType.Contains("char"))
                return $"NCHAR({GetColumnLength(mysqlType)})";
            else if (mysqlType.Contains("text"))
                return "NVARCHAR(MAX)";
            else if (mysqlType.Contains("datetime") || mysqlType.Contains("timestamp"))
                return "DATETIME2";
            else if (mysqlType.Contains("decimal"))
                return GetDecimalType(mysqlType);
            else if (mysqlType.Contains("float"))
                return "FLOAT";
            else if (mysqlType.Contains("double"))
                return "REAL";
            else if (mysqlType.Contains("blob"))
                return "VARBINARY(MAX)";
            else
                return "NVARCHAR(255)";
        }

        /// <summary>  
        /// 处理精确的Decimal类型  
        /// </summary>  
        private string GetDecimalType(string mysqlType)
        {
            // 解析MySQL的DECIMAL(M,D)  
            int start = mysqlType.IndexOf('(');
            int end = mysqlType.IndexOf(')');

            if (start != -1 && end != -1)
            {
                string[] parts = mysqlType.Substring(start + 1, end - start - 1).Split(',');
                if (parts.Length == 2)
                {
                    int precision = int.Parse(parts[0]);
                    int scale = int.Parse(parts[1]);
                    return $"DECIMAL({Math.Min(precision, 38)},{scale})";
                }
            }

            return "DECIMAL(18,2)";
        }

        // 其他方法保持不变  
        // MigrateTable, MigrateTableData 等方法  

        public void MigrateDatabase(List<string> tablesToMigrate)
        {
            foreach (string table in tablesToMigrate)
            {
                MigrateTable(table);
            }
        }

        /// <summary>  
        /// 迁移表数据  
        /// </summary>  
        private void MigrateTableData(string tableName, DataTable schema)
        {
            using (MySqlConnection mysqlConn = new MySqlConnection(mysqlConnectionString))
            using (SqlConnection sqlServerConn = new SqlConnection(sqlServerConnectionString))
            {
                mysqlConn.Open();
                sqlServerConn.Open();

                // 读取MySQL数据  
                string selectQuery = $"SELECT * FROM {tableName}";
                using (MySqlCommand mysqlCmd = new MySqlCommand(selectQuery, mysqlConn))
                using (MySqlDataReader reader = mysqlCmd.ExecuteReader())
                {
                    // 批量插入SQL Server  
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlServerConn))
                    {
                        bulkCopy.DestinationTableName = tableName;

                        // 映射列名  
                        foreach (DataRow row in schema.Rows)
                        {
                            string columnName = row["Field"].ToString();
                            bulkCopy.ColumnMappings.Add(columnName, columnName);
                        }

                        bulkCopy.WriteToServer(reader);
                    }
                }
            }
        }


        public void MigrateTable(string tableName)
        {
            try
            {
                // 表结构获取方法  
                DataTable tableSchema = GetMySqlTableDetailSchema(tableName);

                // 创建SQL Server表（现在包含主键和注释）  
                CreateSqlServerTable(tableName, tableSchema);

                // 迁移数据  
                MigrateTableData(tableName, tableSchema);

                Console.WriteLine($"表 {tableName} 迁移成功");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"迁移表 {tableName} 时发生错误: {ex.Message}");
            }
        }
    }
}

Program.cs

C#
namespace AppMySQLToSqlserver
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string mysqlConn = System.Configuration.ConfigurationManager.AppSettings["MySQLConn"];
            string sqlServerConn = System.Configuration.ConfigurationManager.AppSettings["SQLServerConn"];

            DatabaseMigrator migrator = new DatabaseMigrator(mysqlConn, sqlServerConn);

            // 迁移指定的表  
            List<string> tablesToMigrate = new List<string>
            {
                "base_menu",
            };

            migrator.MigrateDatabase(tablesToMigrate);
        }
    }
}

结论

上述代码实现了一个简单而有效的工具，用于将MySQL数据库的数据和结构轻松迁移到SQL Server。通过这种方法，用户可以快速将数据从一个系统转移到另一个系统，而无需手动干预。对于需要进行跨平台数据库迁移的开发者和系统管理员来说，这是一个非常有用的工具。如果您对该工具有进一步的需求或者想要了解更多功能，例如迁移数据的策略或异常处理机制，请随时与我联系。