Transfer rows from one database to another

Question

This may be answered already somewhere in this forum, but I can't seem to find a good example since I'm using Vistadb 6.

I have to 2 databases; one is the main database, and the other is the backup copy of the main database (with the same structure). I can transfer rows from the customers, inventory tables etc. But when I try to transfer the orders table, only the main (Order) table rows are transferred, but not the Order Details rows.

The child table rows like Details, Payments are not transferred. I'm using a List box to store the OrderId number. There may be more than 1 order number in the list to transfer rows, this depends on if the user selects more than 1 row to transfer.

How it is supposed to work:

1. User selects the backup database copy. (ie. Backup1.vdb6, or Backup2.vdb6, etc.)

2. User selects what to transfer (ie...Orders records for example)

3. User selects more than 1 order # to transfer (ie.100, 305, 422, etc.)

4. The Order numbers are in a list box control

   -100
   -305
   -422
   

5. Need to transfer the records: start with the first record to transfer, ie..100 copy the Orders Table, then the details table, followed by the payments table.

6. Move to the next Order # (ie..305, 422, etc.) and repeat the same for all the Order numbers that need to be transferred to the main database.

This works it transfers all the rows from MyFile database to the main database Orders table. But it does not transfer the OrderDetails rows or the OrderPayment rows.

private void TransferOrders()
{
    // Backup Database
    MyFile = (string) TreeListTransfer.FocusedNode[0];
    string VdbConn1 = $"Data Source={bPath}{MyFile}.vdb6";

    try
    {
        // I suspect the problem may be in this, not sure.
        for (int i = 0; i < LstFiles.Items.Count; i++)
        {
            LstFiles.SelectedIndex = i;

            // Get the Order number from the list, call it FileId
            FileId = Convert.ToInt32(LstFiles.Text, CultureInfo.InvariantCulture);

            Standard.StrSql = @"SELECT OrderId, OrderType, ClientId, OrderDate, DueDate, SubTotal, SalesTax, CityTax, LaborTax, Delivery, Discount, OrderTotal
                                FROM Orders
                                WHERE (OrderId = @OrderId)";

            using (VistaDBConnection conn1 = new(VdbConn1))
            {
                // Open the connection
                conn1.Open();

                using VistaDBCommand cmd1 = new(Standard.StrSql, conn1);
                cmd1.Parameters.AddWithValue("@OrderId", FileId);

                using VistaDBDataReader dr = cmd1.ExecuteReader();

                while (dr.Read())
                {
                    if (dr["OrderId"] != DBNull.Value)
                    {
                        Orders.OrderId = (int) dr["OrderId"];
                    }

                    if (dr["OrderType"] != DBNull.Value)
                    {
                        Orders.OrderType = (string) dr["OrderType"];
                    }

                    if (dr["ClientId"] != DBNull.Value)
                    {
                        Clients.ClientId = (int) dr["ClientId"];
                    }

                    if (dr["OrderDate"] != DBNull.Value)
                    {
                        Orders.OrderDate = (DateTime) dr["OrderDate"];
                    }

                     if (dr["DueDate"] != DBNull.Value)
                         {
                         Orders.DueDate = (DateTime) dr["DueDate"];
                         }

                     if (dr["SubTotal"] != DBNull.Value)
                         {
                         Orders.SubTotal = (decimal) dr["SubTotal"];
                         }

                     if (dr["SalesTax"] != DBNull.Value)
                         {
                         Orders.SalesTax = (decimal) dr["SalesTax"];
                         }

                     if (dr["CityTax"] != DBNull.Value)
                         {
                         Orders.CityTax = (decimal) dr["CityTax"];
                         }

                     if (dr["LaborTax"] != DBNull.Value)
                         {
                         Orders.LaborTax = (decimal) dr["LaborTax"];
                         }

                     if (dr["Delivery"] != DBNull.Value)
                         {
                         Orders.Delivery = (decimal) dr["Delivery"];
                         }

                     if (dr["Discount"] != DBNull.Value)
                         {
                         Orders.Discount = (decimal) dr["Discount"];
                         }

                     if (dr["OrderTotal"] != DBNull.Value)
                         {
                         Orders.OrderTotal = (decimal) dr["OrderTotal"];
                         }

                     }

                 }

//Open the main database using a new conncection.
             //Add the Contents to the main Database
             using (VistaDBConnection conn2 = new(Settings.Default.someSetting))
                 {
                 conn2.Open();

                     Standard.StrSql = @"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)";
                     

                 using VistaDBCommand cmd2 = new(Standard.StrSql, conn2);

                 cmd2.Parameters.AddWithValue("@OrderType", Orders.OrderType);
                 cmd2.Parameters.AddWithValue("@ClientId", Clients.ClientId);
                 cmd2.Parameters.AddWithValue("@OrderDate", Orders.OrderDate);
                 cmd2.Parameters.AddWithValue("@DueDate", Orders.DueDate);
                 cmd2.Parameters.AddWithValue("@SubTotal", Orders.SubTotal);
                 cmd2.Parameters.AddWithValue("@SalesTax", Orders.SalesTax);
                 cmd2.Parameters.AddWithValue("@CityTax", Orders.CityTax);
                 cmd2.Parameters.AddWithValue("@LaborTax", Orders.LaborTax);
                 cmd2.Parameters.AddWithValue("@Delivery", Orders.Delivery);
                 cmd2.Parameters.AddWithValue("@Discount", Orders.Discount);
                 cmd2.Parameters.AddWithValue("@OrderTotal", Orders.OrderTotal);


                 cmd2.ExecuteNonQuery();
                 }
             //Transfer the Order Details
             TransferOrderDetails();
//End of the i
             }


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

     }

//This is the method to get the order details 

        private void TransferOrderDetails()
            {
            MyFile = (string) TreeListTransfer.FocusedNode[0];
            string VdbConn1 = $"Data Source={bPath}{MyFile}.vdb6";
            //Go get the last OrderId number so we can focus on it
//This gets the Last OrderId number from the main database
            int MyOrderId = Orders.GetMaxOrderId(OrderId: 0);

            try
                {
                Standard.StrSql = @"SELECT ItemId, OrderId, InventoryId, Category, Description, Qnty, Unit, RetailPrice, Total
                                  FROM OrderDetails
                                  WHERE (OrderId = @OrderId)
                                  ORDER BY ItemId";

                using (VistaDBConnection conn1 = new(VdbConn1))
                    {
                    //Open the connection
                    conn1.Open();

                    using VistaDBCommand cmd1 = new(Standard.StrSql, conn1);
                    cmd1.Parameters.AddWithValue("@OrderId", MyOrderId);

                    using VistaDBDataReader dr = cmd1.ExecuteReader();
                        {
                        while (dr.Read())
                            {
                            if (dr["OrderId"] != DBNull.Value)
                                {
                                Orders.OrderId = (int) dr["OrderId"];
                                }

                            if (dr["InventoryId"] != DBNull.Value)
                                {
                                Inventory.InventoryId = (int) dr["InventoryId"];
                                }

                            if (dr["Category"] != DBNull.Value)
                                {
                                OrderDetails.ItemCat = (string) dr["Category"];
                                }

                            if (dr["Description"] != DBNull.Value)
                                {
                                OrderDetails.ItemDesc = (string) dr["Description"];
                                }

                            if (dr["Qnty"] != DBNull.Value)
                                {
                                OrderDetails.qnty = (decimal) dr["Qnty"];
                                }

                            if (dr["Unit"] != DBNull.Value)
                                {
                                OrderDetails.ItemUnit = (string) dr["Unit"];
                                }

                            if (dr["RetailPrice"] != DBNull.Value)
                                {
                                OrderDetails.ItemRetail = (decimal) dr["RetailPrice"];
                                }

                            if (dr["Total"] != DBNull.Value)
                                {
                                OrderDetails.ItemTotal = (decimal) dr["Total"];
                                }
                            }
                        }
                    }
                //Save Everything to the Database
                using (VistaDBConnection conn = new(Settings.Default.someSetting))
                    {
                    //Open the Connection
                    conn.Open();

                    Standard.StrSql = @"INSERT INTO OrderDetails(OrderId, InventoryId, Category, Description, Qnty, Unit, RetailPrice, Total)
                             VALUES (@OrderId, @InventoryId, @Category, @Details, @Qnty, @Unit, @Retail, @Total)";

                    using VistaDBCommand cmd = new(Standard.StrSql, conn);
                        {
                        cmd.Parameters.AddWithValue("@OrderId", MyOrderId);
                        cmd.Parameters.AddWithValue("@InventoryId", Inventory.InventoryId);
                        cmd.Parameters.AddWithValue("@Category", OrderDetails.ItemCat);
                        cmd.Parameters.AddWithValue("@Details", OrderDetails.ItemDesc);
                        cmd.Parameters.AddWithValue("@Qnty", OrderDetails.qnty);
                        cmd.Parameters.AddWithValue("@Unit", OrderDetails.ItemUnit);
                        cmd.Parameters.AddWithValue("@Retail", OrderDetails.ItemRetail);
                        cmd.Parameters.AddWithValue("@Total", OrderDetails.ItemTotal);

                        //Execute the Query
                        cmd.ExecuteNonQuery();
                        }

                    //Transfer the payments related to the order
                    TransferOrderPayments();

                    }

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

            }

For the sake of brevity, I left out the payments table records, but it is the same procedure as the Order details.

Answer 1

I figured it out. I had to keep the first database connection open and data reader in order to transfer the records to the table. Once I did that, the records display in the main database, Order Details, and Order Payments tables. Thank You to anyone who was working on this.