VistaDB 6订单数据迁移异常：主表成功子表未迁移

问题描述 #

• 使用VistaDB 6进行结构一致的主备库数据迁移，客户、库存等表迁移正常，但订单迁移时仅Orders主表成功，OrderDetails、OrderPayment等子表数据未同步
• 需求流程：用户选择备份库→选择待迁移订单→选中多个OrderId→按顺序迁移每个订单的主表+子表数据至主库

错误分析 #

1. 子表查询条件错误：TransferOrderDetails中使用主库的最大OrderId从备份库查询子表数据，而备份库中该OrderId不存在，导致查不到任何子表记录
2. 子表数据插入逻辑缺失循环：读取子表DataReader时仅更新实体属性，未在循环内执行插入，最终只插入最后一条记录
3. 全局实体类数据覆盖：使用全局的Orders、OrderDetails等类存储数据，多订单迁移时后续数据会覆盖之前的内容
4. 支付表迁移时机错误：在TransferOrderDetails的插入连接内调用TransferOrderPayments，会导致每条OrderDetails都触发一次支付表迁移，逻辑冗余且错误

修复后的代码示例 #

主订单迁移方法 #

private void TransferOrders()
{
    string backupFile = (string)TreeListTransfer.FocusedNode[0];
    string backupConnStr = $"Data Source={bPath}{backupFile}.vdb6";

    try
    {
        for (int i = 0; i < LstFiles.Items.Count; i++)
        {
            int sourceOrderId = Convert.ToInt32(LstFiles.Items[i].ToString(), CultureInfo.InvariantCulture);

            // 从备份库读取订单主表数据
            OrderDto order = null;
            using (VistaDBConnection conn = new(backupConnStr))
            {
                conn.Open();
                string sql = @"SELECT OrderId, OrderType, ClientId, OrderDate, DueDate, SubTotal, SalesTax, CityTax, LaborTax, Delivery, Discount, OrderTotal
                               FROM Orders WHERE OrderId = @OrderId";
                using VistaDBCommand cmd = new(sql, conn);
                cmd.Parameters.AddWithValue("@OrderId", sourceOrderId);
                
                using VistaDBDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    order = new OrderDto
                    {
                        OrderType = dr["OrderType"] != DBNull.Value ? (string)dr["OrderType"] : null,
                        ClientId = dr["ClientId"] != DBNull.Value ? (int)dr["ClientId"] : 0,
                        OrderDate = dr["OrderDate"] != DBNull.Value ? (DateTime)dr["OrderDate"] : DateTime.MinValue,
                        DueDate = dr["DueDate"] != DBNull.Value ? (DateTime)dr["DueDate"] : DateTime.MinValue,
                        SubTotal = dr["SubTotal"] != DBNull.Value ? (decimal)dr["SubTotal"] : 0,
                        SalesTax = dr["SalesTax"] != DBNull.Value ? (decimal)dr["SalesTax"] : 0,
                        CityTax = dr["CityTax"] != DBNull.Value ? (decimal)dr["CityTax"] : 0,
                        LaborTax = dr["LaborTax"] != DBNull.Value ? (decimal)dr["LaborTax"] : 0,
                        Delivery = dr["Delivery"] != DBNull.Value ? (decimal)dr["Delivery"] : 0,
                        Discount = dr["Discount"] != DBNull.Value ? (decimal)dr["Discount"] : 0,
                        OrderTotal = dr["OrderTotal"] != DBNull.Value ? (decimal)dr["OrderTotal"] : 0
                    };
                }
            }

            if (order == null) continue;

            // 插入主库订单主表，并获取插入后的OrderId
            int targetOrderId = 0;
            using (VistaDBConnection conn = new(Settings.Default.someSetting))
            {
                conn.Open();
                string sql = @"INSERT INTO Orders(OrderType, ClientId, OrderDate, DueDate, SubTotal, SalesTax, CityTax, LaborTax, Delivery, Discount, OrderTotal)
                               VALUES (@OrderType, @ClientId, @OrderDate, @DueDate, @SubTotal, @SalesTax, @CityTax, @LaborTax, @Delivery, @Discount, @OrderTotal);
                               SELECT SCOPE_IDENTITY();";
                using VistaDBCommand cmd = new(sql, conn);
                cmd.Parameters.AddWithValue("@OrderType", order.OrderType ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@ClientId", order.ClientId);
                cmd.Parameters.AddWithValue("@OrderDate", order.OrderDate);
                cmd.Parameters.AddWithValue("@DueDate", order.DueDate);
                cmd.Parameters.AddWithValue("@SubTotal", order.SubTotal);
                cmd.Parameters.AddWithValue("@SalesTax", order.SalesTax);
                cmd.Parameters.AddWithValue("@CityTax", order.CityTax);
                cmd.Parameters.AddWithValue("@LaborTax", order.LaborTax);
                cmd.Parameters.AddWithValue("@Delivery", order.Delivery);
                cmd.Parameters.AddWithValue("@Discount", order.Discount);
                cmd.Parameters.AddWithValue("@OrderTotal", order.OrderTotal);

                targetOrderId = Convert.ToInt32(cmd.ExecuteScalar());
            }

            // 迁移子表数据
            TransferOrderDetails(backupConnStr, sourceOrderId, targetOrderId);
            TransferOrderPayments(backupConnStr, sourceOrderId, targetOrderId);
        }
    }
    catch (Exception ex)
    {
        XtraMessageBox.Show(ex.Message, "Transfer Order Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

// 局部DTO类，避免全局变量污染
private class OrderDto
{
    public string OrderType { get; set; }
    public int ClientId { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime DueDate { get; set; }
    public decimal SubTotal { get; set; }
    public decimal SalesTax { get; set; }
    public decimal CityTax { get; set; }
    public decimal LaborTax { get; set; }
    public decimal Delivery { get; set; }
    public decimal Discount { get; set; }
    public decimal OrderTotal { get; set; }
}

订单详情子表迁移方法 #

private void TransferOrderDetails(string backupConnStr, int sourceOrderId, int targetOrderId)
{
    try
    {
        using (VistaDBConnection conn = new(backupConnStr))
        {
            conn.Open();
            string sql = @"SELECT InventoryId, Category, Description, Qnty, Unit, RetailPrice, Total
                           FROM OrderDetails WHERE OrderId = @OrderId ORDER BY ItemId";
            using VistaDBCommand cmd = new(sql, conn);
            cmd.Parameters.AddWithValue("@OrderId", sourceOrderId);
            
            using VistaDBDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                // 读取单条详情记录
                var detail = new
                {
                    InventoryId = dr["InventoryId"] != DBNull.Value ? (int)dr["InventoryId"] : 0,
                    Category = dr["Category"] != DBNull.Value ? (string)dr["Category"] : null,
                    Description = dr["Description"] != DBNull.Value ? (string)dr["Description"] : null,
                    Qnty = dr["Qnty"] != DBNull.Value ? (decimal)dr["Qnty"] : 0,
                    Unit = dr["Unit"] != DBNull.Value ? (string)dr["Unit"] : null,
                    RetailPrice = dr["RetailPrice"] != DBNull.Value ? (decimal)dr["RetailPrice"] : 0,
                    Total = dr["Total"] != DBNull.Value ? (decimal)dr["Total"] : 0
                };

                // 插入主库详情表
                using (VistaDBConnection targetConn = new(Settings.Default.someSetting))
                {
                    targetConn.Open();
                    string insertSql = @"INSERT INTO OrderDetails(OrderId, InventoryId, Category, Description, Qnty, Unit, RetailPrice, Total)
                                         VALUES (@OrderId, @InventoryId, @Category, @Description, @Qnty, @Unit, @RetailPrice, @Total)";
                    using VistaDBCommand insertCmd = new(insertSql, targetConn);
                    insertCmd.Parameters.AddWithValue("@OrderId", targetOrderId);
                    insertCmd.Parameters.AddWithValue("@InventoryId", detail.InventoryId);
                    insertCmd.Parameters.AddWithValue("@Category", detail.Category ?? DBNull.Value);
                    insertCmd.Parameters.AddWithValue("@Description", detail.Description ?? DBNull.Value);
                    insertCmd.Parameters.AddWithValue("@Qnty", detail.Qnty);
                    insertCmd.Parameters.AddWithValue("@Unit", detail.Unit ?? DBNull.Value);
                    insertCmd.Parameters.AddWithValue("@RetailPrice", detail.RetailPrice);
                    insertCmd.Parameters.AddWithValue("@Total", detail.Total);

                    insertCmd.ExecuteNonQuery();
                }
            }
        }
    }
    catch (Exception ex)
    {
        XtraMessageBox.Show(ex.Message, "Transfer Order Items Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

订单支付子表迁移方法（逻辑同详情表） #

private void TransferOrderPayments(string backupConnStr, int sourceOrderId, int targetOrderId)
{
    try
    {
        using (VistaDBConnection conn = new(backupConnStr))
        {
            conn.Open();
            string sql = @"SELECT [你的支付表字段] FROM OrderPayment WHERE OrderId = @OrderId";
            using VistaDBCommand cmd = new(sql, conn);
            cmd.Parameters.AddWithValue("@OrderId", sourceOrderId);
            
            using VistaDBDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                // 读取支付记录
                var payment = new
                {
                    // 映射支付表字段
                };

                // 插入主库支付表
                using (VistaDBConnection targetConn = new(Settings.Default.someSetting))
                {
                    targetConn.Open();
                    string insertSql = @"INSERT INTO OrderPayment(OrderId, [你的支付表字段])
                                         VALUES (@OrderId, [对应参数])";
                    using VistaDBCommand insertCmd = new(insertSql, targetConn);
                    insertCmd.Parameters.AddWithValue("@OrderId", targetOrderId);
                    // 添加其他参数

                    insertCmd.ExecuteNonQuery();
                }
            }
        }
    }
    catch (Exception ex)
    {
        XtraMessageBox.Show(ex.Message, "Transfer Order Payments Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

关键改进点 #

• 传递正确的查询条件：将备份库的原OrderId传递给子表迁移方法，确保从备份库查询到对应子表数据
• 循环内执行插入：在子表DataReader的循环中逐条插入记录，保证所有子表数据都被迁移
• 使用局部DTO类：替代全局实体类，避免多订单迁移时的数据覆盖问题
• 获取插入后的OrderId：通过SCOPE_IDENTITY()获取主库插入后的OrderId，用于关联子表数据
• 调整子表迁移时机：主表插入完成后再调用子表迁移，且支付表迁移单独调用，避免逻辑冗余

内容的提问来源于stack exchange，提问作者Robert