【MySql】数据库同步工具

写了个MySql数据库的同步数据的小工具，支持创建数据库，创建表，根据过滤条件同步表数据！

同步数据用到的是 MySqlBulkCopy

界面如下：

MySqlDBHelper

using KBS_Sync_Database.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KBS_Sync_Database.Helper
{
    public class MySqlDBHelper
    {
        private readonly string _connectionConfig = string.Empty;

        public MySqlDBHelper(string connectionConfig)
        {
            _connectionConfig = connectionConfig;
        }

        private MySqlConnection connection;

        public MySqlConnection Connection
        {
            get
            {
                try
                {
                    MySqlConnection myConn = new MySqlConnection(_connectionConfig);
                    string connectionString = myConn.ConnectionString;
                    if (connection == null)
                    {
                        connection = new MySqlConnection(connectionString);
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Broken)
                    {
                        connection.Close();
                        connection.Open();
                    }
                    return connection;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public List<TableModel> GetTables(string databaseName)
        {
            try
            {
                var sql = @"SELECT
                    TABLE_NAME,
                    COLUMN_NAME,
                    ORDINAL_POSITION,
                    COLUMN_DEFAULT,
                    IS_NULLABLE,
                    COLUMN_TYPE,
                    COLUMN_COMMENT,
                    COLUMN_KEY
                    FROM information_schema.columns
                    WHERE table_schema = '{0}'
                    ORDER BY TABLE_NAME,ORDINAL_POSITION";

                sql = string.Format(sql, databaseName);
                var dataTable = GetDataSet(sql);

                var tableModels = new List<TableModel>();
                foreach (DataRow row in dataTable.Rows)
                {
                    var tableName = row.Field<string>("TABLE_NAME");

                    var tableModel = tableModels.SingleOrDefault(p => p.TableName == tableName);
                    if (tableModel is null)
                    {
                        tableModel = new TableModel()
                        {
                            TableName = tableName,
                            TableSchema = databaseName,
                            Where = string.Empty
                        };
                        tableModels.Add(tableModel);
                    }

                    tableModel.Fields.Add(
                        new FieldModel()
                        {
                            ColumnName = row["COLUMN_NAME"].ToString(),
                            OrdinalPosition = row["ORDINAL_POSITION"].ToString(),
                            ColumnDefault = row["COLUMN_DEFAULT"].ToString(),
                            IsNullAble = row["IS_NULLABLE"].ToString(),
                            ColumnType = row["COLUMN_TYPE"].ToString(),

                            ColumnKey = row["COLUMN_KEY"].ToString(),
                            ColumnComment = row["COLUMN_COMMENT"].ToString(),
                        });
                }
                return tableModels;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        internal List<string> GetTableNames()
        {
            List<string> tables = new List<string>();
            var configs = _connectionConfig.Split(';');
            var databaseName = configs.FirstOrDefault(p => p.StartsWith("database="))?.Replace("database=", string.Empty);
            var sql = $"SELECT table_name FROM information_schema.columns WHERE table_schema = '{databaseName}' GROUP BY table_name;";
            var table = GetDataSet(sql);
            foreach (DataRow row in table.Rows)
            {
                tables.Add(row[0].ToString());
            }
            return tables;
        }

        public int ExecuteCommand(string safeSql)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(safeSql, Connection);
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int ExecuteCommand(string sql, params MySqlParameter[] values)
        {
            MySqlCommand cmd = new MySqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }

        public int GetScalar(string safeSql)
        {
            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public int GetScalar(string sql, params MySqlParameter[] values)
        {
            MySqlCommand cmd = new MySqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public MySqlDataReader GetReader(string safeSql)
        {
            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);
            MySqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public MySqlDataReader GetReader(string sql, params MySqlParameter[] values)
        {
            MySqlCommand cmd = new MySqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            MySqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public DataTable GetDataSet(string safeSql)
        {
            try
            {
                DataSet ds = new DataSet();
                MySqlCommand cmd = new MySqlCommand(safeSql, Connection);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public MySqlDataAdapter GetAdapter(string safeSql)
        {
            DataSet ds = new DataSet();
            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            return da;
        }

        public DataTable GetDataSet(string sql, params MySqlParameter[] values)
        {
            try
            {
                DataSet ds = new DataSet();
                MySqlCommand cmd = new MySqlCommand(sql, Connection);
                cmd.Parameters.AddRange(values);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool SqlBulkCopyInsert(out string msg, string strTableName, DataTable dtData)
        {
            try
            {
                msg = string.Empty;
                MySqlBulkCopy sqlRevdBulkCopy = new MySqlBulkCopy(Connection)
                {
                    DestinationTableName = strTableName, //数据库中对应的表名
                    NotifyAfter = dtData.Rows.Count,      //有几行数据
                    BulkCopyTimeout = 60 * 5,
                };
                sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库
                return true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
            }
        }

        public bool TableExists(string database, string tableName)
        {
            var sql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TableName";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter(){ ParameterName="@TABLE_SCHEMA", Value= database},
                new MySqlParameter(){ ParameterName="@TableName", Value= tableName},
            };
            return GetScalar(sql, parameters) > 0;
        }
    }
}

FrmMain

using KBS_Sync_Database.Helper;
using KBS_Sync_Database.Models;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NLog;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace KBS_Sync_Database
{
    public partial class FrmMain : Form
    {
        private Logger _log;

        public FrmMain()
        {
            InitializeComponent();
        }

        private DatabaseModel _readDatabaseInfo { get; set; }
        private DatabaseModel _writerDatabaseInfo { get; set; }

        private void Form1_Load(object sender, EventArgs e)
        {
            var config = new NLog.Config.LoggingConfiguration();
            var logfile = new NLog.Targets.FileTarget("logfile") { FileName = "logs/${shortdate}.log", Layout = "[${longdate}]${message}" };
            config.AddRule(LogLevel.Debug, LogLevel.Fatal, logfile);
            NLog.LogManager.Configuration = config;

            _log = NLog.LogManager.GetCurrentClassLogger();
            Log(Color.Green, "程序启动成功！");

            try
            {
                var settingString = ReaderSetting();
                if (!string.IsNullOrWhiteSpace(settingString))
                {
                    var json = JObject.Parse(settingString);
                    this.txtReadConf.Text = json["readConf"]?.ToString();
                    this.txtWriterConf.Text = json["writerConf"]?.ToString();
                    this.txtWhereConf.Text = json["whereConf"]?.ToString();
                }
            }
            catch (Exception ex)
            {
                _log.Error($"读取配置文件错误：{ex.Message}");
            }
        }

        private void btnRead_Click(object sender, EventArgs e)
        {
            try
            {
                _readDatabaseInfo = new DatabaseModel(this.txtReadConf.Text);
                MySqlDBHelper readMySqlDBHelper = new MySqlDBHelper(_readDatabaseInfo.ToString());

                if (readMySqlDBHelper.Connection.State == ConnectionState.Open)
                {
                    Log(Color.Green, "【读取数据库】连接成功！");
                }

                _readDatabaseInfo.TableModels = readMySqlDBHelper.GetTables(_readDatabaseInfo.Database);

                lbReadDatabase.Items.Clear();
                foreach (var table in _readDatabaseInfo.TableModels)
                {
                    lbReadDatabase.Items.Add(table.TableName);
                }
                Log(Color.Green, $"【读取数据库】共有【{_readDatabaseInfo.TableModels.Count()}】张表！");
            }
            catch (Exception ex)
            {
                Log(Color.Red, $"错误：{ex.Message}");
            }
        }

        private void btnWriter_Click(object sender, EventArgs e)
        {
            try
            {
                _writerDatabaseInfo = new DatabaseModel(this.txtWriterConf.Text);
                MySqlDBHelper writerMySqlDBHelper = new MySqlDBHelper(_writerDatabaseInfo.ToString());

                if (writerMySqlDBHelper.Connection.State == ConnectionState.Open)
                {
                    Log(Color.Green, "【写入数据库】连接成功！");
                }

                _writerDatabaseInfo.TableModels = writerMySqlDBHelper.GetTables(_writerDatabaseInfo.Database);

                lbWriterDatabase.Items.Clear();
                foreach (var table in _writerDatabaseInfo.TableModels)
                {
                    lbWriterDatabase.Items.Add(table.TableName);
                }
                Log(Color.Green, $"【写入数据库】共有【{_writerDatabaseInfo.TableModels.Count()}】张表！");
            }
            catch (Exception ex)
            {
                Log(Color.Red, $"错误：{ex.Message}");
            }
        }

        private int GetIndexOf(string str)
        {
            var operators = new List<string>() { ">=", "<=", "<>", " like ", "=", "not in", " in" };
            foreach (var oper in operators)
            {
                if (str.Trim().ToLower().IndexOf(oper) != -1)
                {
                    return str.IndexOf(oper);
                }
            }
            Log(Color.Red, $"过滤条件：{str} 不被支持！");
            return 0;
        }

        private void btnSync_Click(object sender, EventArgs e)
        {
            try
            {
                messagesList.Items.Clear();
                if (lbReadDatabase.SelectedItems.Count == 0)
                {
                    Log(Color.Red, $"没有选择任何需要同步的表");
                    return;
                }
                if (Check() == false)
                {
                    return;
                }
                MySqlDBHelper readMySqlDBHelper = new MySqlDBHelper(this.txtReadConf.Text);
                MySqlDBHelper writerMySqlDBHelper = new MySqlDBHelper(this.txtWriterConf.Text + " ;AllowLoadLocalInfile = true;");

                foreach (var selectedItem in lbReadDatabase.SelectedItems)
                {
                    var sql = $"SELECT * FROM {selectedItem}";
                    var dataTable = readMySqlDBHelper.GetDataSet(sql);
                    var tableName = GetTableName(selectedItem.ToString());

                    Log(Color.Blue, $"开始同步【{tableName}】表的 【{dataTable.Rows.Count}】 条数据！");
                    if (dataTable.Rows.Count != 0)
                    {
                        var idList = new List<string>();
                        foreach (DataRow row in dataTable.Rows)
                        {
                            idList.Add($"'{row[0]}'");
                        }
                        _log.Error($"【{tableName}】表的同步的主键Id【({string.Join(",", idList)})】！");

                        if (!writerMySqlDBHelper.SqlBulkCopyInsert(out string msg, tableName, dataTable))
                        {
                            Log(Color.Red, $"同步错误信息：{msg}");
                            //https://my.oschina.net/u/4265966/blog/3324325
                        }
                        else
                        {
                            Log(Color.Blue, $"【{tableName}】表的 【{dataTable.Rows.Count}】 条数据同步成功！");
                        }
                    }
                    else
                    {
                        Log(Color.Blue, $"【{tableName}】表的 【{dataTable.Rows.Count}】 条数据同步成功！");
                    }
                }
            }
            catch (Exception ex)
            {
                Log(Color.Red, $"错误：{ex.Message}");
            }
        }

        private bool Check()
        {
            var flag = true;
            //检查 读取数据库表 是否存在 写入数据库表
            foreach (string selectedItem in lbReadDatabase.SelectedItems)
            {
                var tableName = GetTableName(selectedItem);
                var writerTable = _writerDatabaseInfo.TableModels.SingleOrDefault(p => p.TableName == tableName);
                var readTable = _readDatabaseInfo.TableModels.SingleOrDefault(p => p.TableName == tableName);
                if (writerTable != null)
                {
                    //检查字段
                    if (writerTable.Fields.Count != readTable.Fields.Count)
                    {
                        Log(Color.Red, $"错误：【{tableName}】表 对应字段数目不一致！");
                        flag = false;
                        continue;
                    }
                    for (int i = 0; i < readTable.Fields.Count; i++)
                    {
                        if (writerTable.Fields[i].ColumnName != readTable.Fields[i].ColumnName)
                        {
                            Log(Color.Red, $"错误：【{tableName}】表的 【{writerTable.Fields[i].ColumnName}】字段对应不上！");
                            flag = false;
                        }
                    }
                }
                else
                {
                    Log(Color.Red, $"错误：写入数据库不存在 【{selectedItem}】表！");
                    flag = false;
                }
            }
            return flag;
        }

        private string GetTableName(string str)
        {
            var tableName = str.ToString().Trim().ToLower();
            if (tableName.IndexOf("where") != -1)
            {
                tableName = tableName.Substring(0, tableName.IndexOf("where"));
            }
            return Regex.Replace(tableName, "`| |\"", string.Empty);
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            messagesList.Items.Clear();
        }

        private void btnFilter_Click(object sender, EventArgs e)
        {
            try
            {
                var fields = txtWhereConf.Text.Trim().Split(';');
                for (int i = 0; i < lbReadDatabase.Items.Count; i++)
                {
                    var tableName = GetTableName(lbReadDatabase.Items[i].ToString());

                    var tableFields = _readDatabaseInfo.TableModels.SingleOrDefault(p => p.TableName == tableName)?.Fields;
                    var whereFields = new List<string>();
                    foreach (var field in fields)
                    {
                        if (!string.IsNullOrWhiteSpace(field))
                        {
                            var temp = Regex.Replace(field.Trim().Substring(0, GetIndexOf(field.Trim())), "`| |\"", string.Empty);
                            if (tableFields.Any(p => p.ColumnName.Equals(temp)))
                            {
                                whereFields.Add(field);
                            }
                        }
                    }
                    if (whereFields.Count() > 0)
                    {
                        lbReadDatabase.Items[i] = $"`{tableName}` where {string.Join(" and ", whereFields)}";
                    }
                    else
                    {
                        lbReadDatabase.Items[i] = $"`{tableName}`";
                    }
                }
            }
            catch (Exception ex)
            {
                Log(Color.Red, $"错误：{ex.Message}");
            }
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            try
            {
                if (lbReadDatabase.SelectedItems.Count == 0)
                {
                    Log(Color.Red, $"没有选择任何需要同步的表");
                    return;
                }

                if (Check() == false)
                {
                    return;
                }

                MySqlDBHelper readMySqlDBHelper = new MySqlDBHelper(this.txtReadConf.Text);
                foreach (var selectedItem in lbReadDatabase.SelectedItems)
                {
                    var tableName = GetTableName(selectedItem.ToString());
                    var sql = $"SELECT * FROM {selectedItem}";
                    var dataTable = readMySqlDBHelper.GetDataSet(sql);
                    Log(Color.Blue, $"【{tableName}】 表有 【{dataTable.Rows.Count}】 条数据待同步！");
                }
            }
            catch (Exception ex)
            {
                Log(Color.Red, $"错误：{ex.Message}");
            }
        }

        private void FrmMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            var json = new
            {
                readConf = txtReadConf.Text.Trim(),
                writerConf = txtWriterConf.Text.Trim(),
                whereConf = txtWhereConf.Text.Trim(),
            };
            WriterSetting(JsonConvert.SerializeObject(json));
        }

        #region Setting 配置

        private void WriterSetting(string config)
        {
            var settingFormat = ConvertJsonString(config);
            using (StreamWriter sw = new StreamWriter("setting.json"))
            {
                sw.Write(settingFormat);
            }
        }

        private string ReaderSetting()
        {
            if (File.Exists("setting.json"))
            {
                using (StreamReader sw = new StreamReader("setting.json"))
                {
                    return sw.ReadToEnd();
                }
            }
            return string.Empty;
        }

        private string ConvertJsonString(string str)
        {
            JsonSerializer serializer = new JsonSerializer();
            TextReader tr = new StringReader(str);
            JsonTextReader jtr = new JsonTextReader(tr);
            object obj = serializer.Deserialize(jtr);
            if (obj != null)
            {
                StringWriter textWriter = new StringWriter();
                JsonTextWriter jsonWriter = new JsonTextWriter(textWriter)
                {
                    Formatting = Formatting.Indented,
                    Indentation = 1,
                    IndentChar = ' '
                };
                serializer.Serialize(jsonWriter, obj);
                return textWriter.ToString();
            }
            else
            {
                return str;
            }
        }

        #endregion Setting 配置

        #region log 配置

        private void Log(Color color, string message)
        {
            Action callback = () =>
            {
                messagesList.Items.Add(new LogMessage(color, $"【{DateTime.Now:HH:mm:ss}】{message}"));
                _log.Error($"{message}");
            };
            Invoke(callback);
        }

        private void messagesList_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index >= 0)
            {
                var message = (LogMessage)messagesList.Items[e.Index];
                e.Graphics.DrawString(message.Content, messagesList.Font, new SolidBrush(message.MessageColor), e.Bounds);
            }
        }

        private class LogMessage
        {
            public Color MessageColor { get; }

            public string Content { get; }

            public LogMessage(Color messageColor, string content)
            {
                MessageColor = messageColor;
                Content = content;
            }
        }

        #endregion log 配置

        private void lbReadDatabase_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            int index = this.lbReadDatabase.IndexFromPoint(e.Location);
            if (index != ListBox.NoMatches)
            {
                FrmField frmField = new FrmField(_readDatabaseInfo.TableModels[index]);
                frmField.Show();
            }
        }

        private void lbWriterDatabase_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            int index = this.lbWriterDatabase.IndexFromPoint(e.Location);
            if (index != ListBox.NoMatches)
            {
                FrmField frmField = new FrmField(_writerDatabaseInfo.TableModels[index]);
                frmField.Show();
            }
        }

        private void btnCreateTable_Click(object sender, EventArgs e)
        {
            try
            {
                if (lbReadDatabase.SelectedItems.Count == 0)
                {
                    Log(Color.Red, $"没有选择任何需要同步的表");
                    return;
                }
                _writerDatabaseInfo = new DatabaseModel(this.txtWriterConf.Text);
                MySqlDBHelper writerMySqlDBHelper = new MySqlDBHelper(_writerDatabaseInfo.ToString());

                foreach (var selectedItem in lbReadDatabase.SelectedItems)
                {
                    var tableName = GetTableName(selectedItem.ToString());
                    var readTable = _readDatabaseInfo.TableModels.SingleOrDefault(p => p.TableName == tableName);
                    if (!writerMySqlDBHelper.TableExists(_writerDatabaseInfo.Database, readTable.TableName))
                    {
                        var tableScript = GetTableScript(readTable);
                        var count = writerMySqlDBHelper.ExecuteCommand(tableScript);
                        if (writerMySqlDBHelper.TableExists(_writerDatabaseInfo.Database, readTable.TableName))
                        {
                            Log(Color.Green, $"创建【{readTable.TableName}】表成功！");
                            //刷新列表
                            _writerDatabaseInfo.TableModels = writerMySqlDBHelper.GetTables(_writerDatabaseInfo.Database);
                            lbWriterDatabase.Items.Clear();
                            foreach (var table in _writerDatabaseInfo.TableModels)
                            {
                                lbWriterDatabase.Items.Add(table.TableName);
                            }
                        }
                    }
                    else
                    {
                        Log(Color.Red, $"错误：【{readTable.TableName}】目标数据库已存在！");
                    }
                }
            }
            catch (Exception ex)
            {
                Log(Color.Red, $"错误：{ex.Message}");
            }
        }

        #region 创建table

        private string GetTableScript(TableModel tableModel)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine($"CREATE TABLE `{tableModel.TableName}`");
            sb.AppendLine("(");
            foreach (var field in tableModel.Fields)
            {
                var isNull = " NULL ";
                if ("no".Equals(field.IsNullAble.ToLower()))
                {
                    isNull = " NOT NULL ";
                }
                var defaultStr = string.Empty;
                if (!string.IsNullOrWhiteSpace(field.ColumnDefault))
                {
                    //有待完善
                    var defaultValue = field.ColumnDefault;
                    if (!"CURRENT_TIMESTAMP".Equals(field.ColumnDefault) && !Regex.IsMatch(field.ColumnDefault, @"^[0-9]*$"))
                    {
                        defaultValue = $"'{defaultValue}'";
                    }
                    defaultStr = $"DEFAULT {defaultValue}";
                }
                sb.AppendLine($"\t`{field.ColumnName}` {field.ColumnType} {isNull} {defaultStr} COMMENT '{field.ColumnComment}',");
            }
            //主键
            var priKey = tableModel.Fields[0].ColumnName;//默认第一个字段为主键
            var priField = tableModel.Fields.SingleOrDefault(p => p.ColumnKey == "PRI");
            if (priField is null)
            {
                priKey = priField.ColumnName;
            }
            sb.AppendLine($"\tPRIMARY KEY (`{priKey}`)");
            sb.AppendLine(")");
            sb.AppendLine($"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{tableModel.TableName}表';");
            return sb.ToString();
        }

        #endregion 创建table

        private void btnCreateDatabase_Click(object sender, EventArgs e)
        {
            try
            {
                var databaseInfo = new DatabaseModel(this.txtWriterConf.Text);
                MySqlDBHelper writerMySqlDBHelper = new MySqlDBHelper(databaseInfo.CreateDatabaseConfig());
                var count = writerMySqlDBHelper.ExecuteCommand($"CREATE database `{databaseInfo.Database}`;");
                if (count == 1)
                {
                    Log(Color.Green, $"创建【{databaseInfo.Database}】数据库成功！");
                }
            }
            catch (Exception ex)
            {
                Log(Color.Red, $"错误：{ex.Message}");
            }
        }
    }
}

FrmField

using KBS_Sync_Database.Models;
using System;
using System.Windows.Forms;

namespace KBS_Sync_Database
{
    public partial class FrmField : Form
    {
        public FrmField(TableModel table)
        {
            InitializeComponent();
            this.Text = $"【{table.TableName}】表 字段详细信息";
            dgvFields.DataSource = table.Fields;
        }

        private void FrmField_Load(object sender, EventArgs e)
        {
        }
    }
}

DatabaseModel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;

namespace KBS_Sync_Database.Models
{
    public class DatabaseModel
    {
        public DatabaseModel(string configStr)
        {
            var config = configStr.Trim().Split(';');
            Server = GetOptions(config, nameof(Server));
            UId = GetOptions(config, nameof(UId));
            Pwd = GetOptions(config, nameof(Pwd));
            Port = GetOptions(config, nameof(Port));
            Database = GetOptions(config, nameof(Database));
            TreatTinyAsBoolean = GetOptions(config, nameof(TreatTinyAsBoolean));
            Connect_Timeout = GetOptions(config, nameof(Connect_Timeout));
        }

        private string GetOptions(string[] config, string option)
        {
            option = option.ToLower().Replace("_", " ");
            var keyValue = config.SingleOrDefault(p => p.ToLower().StartsWith($"{option}="));
            if (keyValue is null)
            {
                return string.Empty;
            }
            return keyValue.Split('=')[1];
        }

        public string Server { get; }
        public string UId { get; }
        public string Pwd { get; }
        public string Port { get; }
        public string Database { get; }
        public string TreatTinyAsBoolean { get; }
        public string Connect_Timeout { get; }

        public List<TableModel> TableModels { get; set; } = new List<TableModel>();

        public override string ToString()
        {
            return ConfigString();
        }

        private string ConfigString(bool isCreateDatabase = false)
        {
            List<string> config = new List<string>();
            Type t = this.GetType();
            PropertyInfo[] PropertyList = t.GetProperties();
            foreach (PropertyInfo item in PropertyList)
            {
                string name = item.Name.Replace("_", " ");
                if (!"TableModels".Equals(name))
                {
                    if (!(isCreateDatabase && nameof(Database).Equals(name)))
                    {
                        string value = item.GetValue(this, null)?.ToString() ?? string.Empty;
                        config.Add($"{name}={value}");
                    }
                }
            }
            var configString = string.Join(";", config);
            return configString;
        }

        public string CreateDatabaseConfig()
        {
            return ConfigString(true);
        }
    }
}

TableModel

using System.Collections.Generic;

namespace KBS_Sync_Database.Models
{
    public class TableModel
    {
        public string TableSchema { get; set; }
        public string TableName { get; set; }
        public string Where { get; set; }
        public List<FieldModel> Fields { get; set; } = new List<FieldModel>();
    }
}

FieldModel

namespace KBS_Sync_Database.Models
{
    public class FieldModel
    {
        public string ColumnName { get; set; }
        public string OrdinalPosition { get; set; }
        public string ColumnDefault { get; set; }
        public string IsNullAble { get; set; }
        public string ColumnType { get; set; }
        public string ColumnKey { get; set; }
        public string ColumnComment { get; set; }
    }
}