【MySQL】 拷贝本地数据库中的数据表到远程数据库的数据表

项目中的一项需求：

1. 当远程数据库能够正常连接时，将数据上传到远程数据库；
2. 当远程数据库断线时，将数据上传到本地数据库；
3. 远程数据库由断线变为可连接时，将本地数据库中的数据上传到远程数据库中；

见以下代码：

public static void UploadDataFromLocalToRemote()
{
    using (var connectionRemote = new MySqlConnection(RemoteConnectString))
    {
        // 更测远程数据库的连接
        if (connectionRemote.Ping())
        {
            _remoteDatabaseAvailable = false;
            return;
        }

        // 远程测试通过，打开连接
        connectionRemote.Open();
        _remoteDatabaseAvailable = true;

        // 打开本地连接，读取本地数据
        using (var connectionLocal = new MySqlConnection(LocalConnectString))
        {
            connectionLocal.Open();

            // 获取本地数据库中数据量
            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection  = connectionLocal;
                command.CommandText = $"SELECT COUNT(*) AS Count FROM {TableName}";
                if ((long)command.ExecuteScalar() <= 0)
                {
                    return;
                }
            }

            // 获取远程数据中，当前最大的ID
            long maxid;
            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionRemote;
                command.CommandText = $"SELECT MAX(id) FROM {TableName}";
                maxid = (long)command.ExecuteScalar();
            }

            // 将本地数据路的ID+远程数据库的最大ID作为新的ID
            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionLocal;
                command.CommandText = $"UPDATE {TableName} Set id = id + {maxid}";
                command.ExecuteNonQuery();
            }

            // 用修改后的本地数据库填充数据表
            var sqlString = $"SELECT id,{ColNameDataCode},{ColNameDateTime},{ColNameBendingWidthOne},{ColNameBendingWidthTwo}, {ColNameDepth},{ColNameEmbossing},{ColNameDislocation},{ColNameBoundVertical},{ColNameBoundHorizontal} FROM {TableName}";
            var dt = new DataTable();
            using (var adapterLocal = new MySqlDataAdapter())
            {
                using (var command = new MySqlCommand())
                {
                    command.CommandText = sqlString;
                    command.Connection = connectionLocal;
                    command.CommandType = CommandType.Text;
                    adapterLocal.SelectCommand = command;
                    adapterLocal.Fill(dt);
                }
            }

            // 复制表结构
            var ddt = dt.Clone();

            // 复制表数据，需要一个一个的添加，以保证行状态时Added
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row = ddt.NewRow();
                row.ItemArray = dt.Rows[i].ItemArray;
                ddt.Rows.Add(row);
            }

            // 将数据表的内容更新到远程数据库
            using (var adapterRemote = new MySqlDataAdapter())
            {
                using (var command = new MySqlCommand())
                {
                    command.Connection  = connectionRemote;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlString;
                    adapterRemote.SelectCommand = command;

                    // 根据SELECT语句自动生成插入指令
                    var builder = new MySqlCommandBuilder(adapterRemote);
                    adapterRemote.InsertCommand = builder.GetInsertCommand();
                    adapterRemote.Update(ddt);
                }
            }

            // 删除本地数据库的所有内容
            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionLocal;
                command.CommandText = $"DELETE FROM {TableName}";
                command.ExecuteNonQuery();
            }
        }
    }
}

修正：本地或远程数据库中数据行数为零的BUG

public void UploadDataFromLocalToRemote2()
{
    using (var connectionRemote = new MySqlConnection(GetRemoteConnectString()))
    {
        connectionRemote.Open();
        using (var connectionLocal = new MySqlConnection(GetLocalConnectString()))
        {
            connectionLocal.Open();

            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionLocal;
                command.CommandText = $"SELECT COUNT({ColId}) AS Count FROM {LocalTableName}";
                var res = command.ExecuteScalar();
                if (Convert.IsDBNull(res))
                {
                    return;
                }
            }

            long maxid;
            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionRemote;
                command.CommandText = $"SELECT MAX({ColId}) FROM {RemoteTableName}";

                var res = command.ExecuteScalar();
                if (Convert.IsDBNull(res))
                {
                    maxid = 0;
                }
                else
                {
                    maxid = (long)res;
                }

            }

            long minid;
            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionLocal;
                command.CommandText = $"SELECT MIN({ColId}) FROM {LocalTableName}";

                var res = command.ExecuteScalar();
                if (Convert.IsDBNull(res))
                {
                    minid = 0;
                }
                else
                {
                    minid = (long)res;
                }
            }

            // 将本地数据路的ID+远程数据库的最大ID作为新的ID
            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionLocal;
                command.CommandText = $"UPDATE {LocalTableName} SET {ColId} = {ColId} + {maxid - minid + 1}";
                command.ExecuteNonQuery();
            }

            var dt = new DataTable();
            using (var adapterLocal = new MySqlDataAdapter())
            {
                using (var command = new MySqlCommand())
                {
                    command.CommandText = $"SELECT * FROM {LocalTableName}";
                    command.Connection = connectionLocal;
                    command.CommandType = CommandType.Text;
                    adapterLocal.SelectCommand = command;
                    adapterLocal.Fill(dt);
                }
            }

            // 复制表结构
            var ddt = dt.Clone();

            // 复制表数据，需要一个一个的添加，以保证行状态时Added
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row = ddt.NewRow();
                row.ItemArray = dt.Rows[i].ItemArray;
                ddt.Rows.Add(row);
            }
            dt.Dispose();


            using (var adapterRemote = new MySqlDataAdapter())
            {
                using (var command = new MySqlCommand())
                {
                    command.Connection = connectionRemote;
                    command.CommandType = CommandType.Text;
                    command.CommandText = $"SELECT * FROM {RemoteTableName}";
                    adapterRemote.SelectCommand = command;

                    // 根据SELECT语句自动生成插入指令
                    var builder = new MySqlCommandBuilder(adapterRemote);
                    adapterRemote.InsertCommand = builder.GetInsertCommand();
                    adapterRemote.Update(ddt);
                }
            }

            using (var command = new MySqlCommand())
            {
                command.CommandType = CommandType.Text;
                command.Connection = connectionLocal;
                command.CommandText = $"TRUNCATE TABLE {LocalTableName}";
                command.ExecuteNonQuery();
            }
        }
    }
}