Using Rollback transactions (UPDATE Table)

Question

Good day. I'm having a trouble on how I will able to update my data

 

I have 3 tables: Orders, Products, & OrderDetails Table

Orders - OrderID (PK), CustomerName, OrderDate, TotalAmount, Status

Products - ProductD (PK), ProductName, Category, Price, Quantity, CriticalLevel

OrderDetails - DetailID (PK), OrderID (FK), ProductID (FK), Quantity, Price, Amount

 

The process is that everytime I'll order a product, the quantity from the Products table will update, decreasing its numbers (ex. when I ordered 2 pcs. of the product, the updated qty. would be total quantity - 2. Its almost similarly called to inventory). In addition, after each time I order, the status will be put as 'COMPLETE'

Anyway, that is not my problem. I have this ViewOrder.cs which display the summary of orders. It's look like this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace FirstApplication
{
    public partial class ViewOrders : Form
    {
        DataTable dtOrders;
        private SqlConnection cn;
        public ViewOrders()
        {
            InitializeComponent();
        }

        private void ViewOrders_Load(object sender, EventArgs e)
        {
            OpenConnection();
            ViewOrder();
            CloseConnection();
        }

        private void OpenConnection()
        {
            cn = new SqlConnection();
            cn.ConnectionString =
                ConfigurationManager.AppSettings["AppConnection"].ToString();
            cn.Open();
        }

        private void CloseConnection()
        {
            if (cn != null)
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        private void btnCancelOrder_Click(object sender, EventArgs e)
        {
            OpenConnection();
            CancelOrder();
            ViewOrder();

            CloseConnection();  
        }

        private void ViewOrder()
        {
            SqlCommand cmdSelect = new SqlCommand();
            cmdSelect.Connection = cn;
            cmdSelect.CommandType = CommandType.Text;
            cmdSelect.CommandText = "Select * from orders";

            SqlDataAdapter daSelect = new SqlDataAdapter();
            daSelect.SelectCommand = cmdSelect;

            dtOrders = new DataTable();
            daSelect.Fill(dtOrders);

            dgOrders.DataSource = dtOrders;
        }

       
        private void CancelOrder()
        {
            if (dgOrders.Rows.Count == 0 || dgOrders.SelectedRows.Count == 0)
            {
                MessageBox.Show("Select first an order to remove.");
                return;
            }
            else
            {  
                MessageBox.Show("Are sure you want to cancel this order.", "",
                    MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (Convert.ToBoolean(DialogResult.Yes))
                {
                    OpenConnection();

                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "UPDATE Orders SET Status = 'Cancelled' " +
                        "WHERE OrderID = @OrderID";
                    cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value =
                        dgOrders.SelectedRows[0].Cells[0].Value.ToString();

                    cmd.ExecuteNonQuery();

/*Upon executing, the 'COMPLETE' status will be changed to 'CANCELLED' when I click the btnCancelOrder. (ALREADY RUNNING)

 

My problem is, if I'll able to change the status of my order, is there any way that the quantity I ordered, but then was cancelled, will be put back again in the product table, or should i say rollback the transaction?

For example, I ordered 2 pcs. of Product A which has a total quantity of 10 pcs. After ordering, the total quantity will become 8. But then, when I cancelled my order, 2 pcs. will be brought back again, so that the 8 in the total quantity will become 10 again. Getting my point?

 

                    CloseConnection();
                }
                else
                    return;
            }
        }
        

    }
}

 

any help would do. sorry if it's kinda long.

 

I do hope someone would help me out in this problem.