How to rollback transactions (UPDATING A 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;
            }
        }

        private void ComputeTotal()
        {


        }

}
}

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

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

Vijaya Kadiyala · Answer

The simplest thing is tarnsfer all the updation code to stored procedure and handle all the tarnsaction over there.

Bechir Bejaoui · Answer

To rollback a transation xxxConnection oConn; xxxTransaction oTrans; oConn.ConnectionString = ...; try { oTrans = oConn.BeginTransaction(); try { //Transaction block here oTrans.Commit(); } catch(Exception ex) { oTrans.RollBack(); } finally { oConn.Close(); } } But I think that this is not exactly your problem because the rollback processes only if there is a mistake within the transaction context wrong Sql comand or permissions issues and something like this . What I undersotood from your case is that the transaction was successfully commited but you want to go back to the previous state. In this case as a Finance graduate I can answer you in such operations even the turnback of the already shiped product is considered as a stock movement. I mean you create another transction for the turned back products you can add a new datacolumn to your product table to indicate whether a product is turned back to stock or not .