Posted on 12/28/2020 3:57:52 AM by Admin

Transaction in Ado.Net

In order to understand Transaction, first we need to understand Data Integrity. Data integrity is the overall accuracy, completeness, and consistency of data. When the integrity of data is secure, the information stored in a database will remain complete, accurate, and reliable no matter how long it’s stored or how often it’s accessed. Data integrity also ensures that your data is safe from any outside forces.

We use different techniques to achieve database integrity, Primary keys and Foreign keys are the most basic constraint that every SQL developer applies while creating a database to maintain the database integrity.

You can't assign a department to an employee which doesn't even exist, for example, suppose there are three departments namely HR, Development and Business in a company with Id of 1,2 and 3 respectively, if you don't apply a foreign key constraint on Employee table then it is very possible that you could assign Department Id 4 to an employee which doesn't even exist, this will lead to inconsistent database and impact on your business.

while creating a database we oppose a lot of constraints to maintain database integrity like a foreign key constraint, unique key constraint, Primary key constraint, etc, however in real-time, there is no guarantee of Database integrity.

consider an application where multiple users are active at the same time and they simultaneously perform database transactions like inserting, updating, or deleting data, if any moment of time some operations failed for some reason then it will leave the database in an inconsistent state.

similarly consider a banking application, where if a user has both current and saving account and he transfers money from his current (checked) account to saving account and for some reason like system failure amount debited from current amount but not credit to saving account then it will create a big chaos.

Transactions are essential for maintaining data integrity, both for multiple related operations and when multiple users that update the database concurrently.

What Is a Transaction?

A transaction is a set of operations performed so all operations are guaranteed to succeed or fail as one unit.

Transaction is all or none

A common example of a transaction is the process of transferring money from a checking account to a savings account.

This involves two operations:
    1. Deducting money from the checking account and
      Note: in the USA a checking account is like a current account in India
    2. Adding it to the savings account.

Both must succeed together and the changes must be committed to the accounts, or both must fail together and rolled back so that the accounts are maintained in a consistent state. Under no circumstances should money be deducted from the checking account but not added to the savings account (or vice versa), you would at least not want this to happen with the transactions occurring with your bank accounts.

By using a transaction concept, both the operations, namely debit and credit, can be guaranteed to succeed or fail together. So both accounts remain in a consistent state all the time.

Let's understand this with an example.

transaction Example
Transaction example

The database has got 2 Accounts (current and saving) table. We want to design a web application to transfer money from Current Account to Saving Account. The design of the webform should be as shown below.

Transaction UI
Transaction UI

When we click "Transfer money from Current Account to saving Account" button, we should subtract the amount from the Current account and add amount to Saving account. So there will be 2 database UPDATE statements on two tables. What do you think will happen if only the first update statement is executed successfully and not the second statement. Money is deducted from the first account, but not added to the second account. This is definitely not desirable. Either both the statements should succeed or both of them should fail. If one succeeds and the other fails we should also roll back the changes made by the first statement to maintain the integrity of the data. This can be achieved using transactions in ado.net.

Step 1 : Create the Accounts table using the following SQL script


  Create database TransactionInAdo
  Go
  use TransactionInAdo
  Go
  Create Table SavingAccount
  (
     AccountNumber nvarchar(15) primary key,
     CustomerName nvarchar(50),
     Balance int
  )
  Create Table CurrentAccount
  (
     AccountNumber nvarchar(15) primary key,
     CustomerName nvarchar(50),
     Balance int
   )
  GO
  Insert into SavingAccount values('12023435', 'Sachin', 300000)
  Insert into SavingAccount values('12023436', 'Arjun', 250000)
  GO
  Insert into CurrentAccount values('12023435', 'Sachin', 500000)
  Insert into CurrentAccount values('12023436', 'Arjun', 50000)

Step 2 : Create a new empty asp.net web application. Name it TransactionInAdoNet.

Step 3 : Include connection string in web.config file


 <connectionStrings>
 <add name="CS"connectionString="Data Source=SACHIN-PC\SQLEXPRESS;Initial Catalog=TransactionInAdo;Integrated 
 Security=True"/>
 </connectionStrings>

Step 4 : Add a WebForm. Copy and paste the following HTML.


    <form id="form1" runat="server">
        <div>
            <table border="1" style="background: brown; color: White">
                <tr>
                    <td>Enter Name to fetch Account balances</td>
                    <td>
                        <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
                    </tr>
                      <tr>
                          <td colspan="2">
                              <asp:Button ID="btnfetch" runat="server" Text="Fetch My both A/c Balance" OnClick="btnfetch_Click"/></td>
                      </tr>
            </table>
            <br /> <br />
            <table border="1" style="background: brown; color: White">
                <tr>
                    <td>saving Account No</td>
                    <td>
                        <asp:Label Text="N/A" ID="lblSavingAcNo" runat="server" />
                    </td>
                    </tr>
                      <tr>
                    <td>saving Account Balance</td>
                    <td>
                        <asp:Label Text="N/A" ID="lblSavingAcBalance" runat="server" />
                    </td>
                    </tr>
                <tr>
                    <td>Current Account No</td>
                    <td>
                        <asp:Label Text="N/A" ID="lblCurrentAcNo" runat="server" />
                    </td>
                    </tr>
                      <tr>
                    <td>Current Account Balance</td>
                    <td>
                        <asp:Label Text="N/A" ID="lblCurrentAcBalance" runat="server" />
                    </td>
                    </tr>
             </table>
             <br /> <br />
             <table border="1" style="background: brown; color: White">
                <tr>
                    <td>Specify Amount you want to Transfer</td>
                    <td>
                        <asp:TextBox ID="txtAmount" runat="server"></asp:TextBox></td>
                </tr>
                <tr> 
                    <td colspan="2">
                   <asp:Button ID="btnTransfer" runat="server" Text="Transfer from Current to Saving" OnClick="btnTransfer_Click" /></td>
                </tr>
            </table>
            <br />
           <asp:Label ID="lblMessage" runat="server" Font-Bold="true"></asp:Label>
        </div>
    </form>

Step 5 : Double click on btnFetch button to generate button event handler and Copy and paste the following code.


     string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        private void UpdateLabels()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand(@"select * from SavingAccount where CustomerName=@Name;
                select * from CurrentAccount where CustomerName=@Name", con);
                cmd.Parameters.AddWithValue("@Name", txtName.Text);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    lblSavingAcBalance.Text = rdr["Balance"].ToString();
                    lblSavingAcNo.Text = rdr["AccountNumber"].ToString();
                }
                if (rdr.NextResult())
                {
                    while (rdr.Read())
                    {
                        lblCurrentAcBalance.Text = rdr["Balance"].ToString();
                        lblCurrentAcNo.Text = rdr["AccountNumber"].ToString();
                    }
                }
            }
        }
        protected void btnfetch_Click(object sender, EventArgs e)
        {
            UpdateLabels();
        }

Step 6 : Double click on btnTransfer button to generate button event handler and Copy and paste the following code.


      protected void btnTransfer_Click(object sender, EventArgs e)
        {
            string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                // Begin a transaction. The connection needs to 
                // be open before we begin a transaction
                SqlTransaction transaction = con.BeginTransaction();
                try
                {
                    // Associate the first update command with the transaction

             SqlCommand cmd = new SqlCommand ("Update CurrentAccount set Balance = Balance -" + Convert.ToInt32(txtAmount.Text) 
     +"where AccountNumber =" + lblCurrentAcNo.Text , con, transaction);
                    cmd.ExecuteNonQuery();
                    // Associate the second update command with the transaction
                    cmd = new SqlCommand(
                        "Update SavingAccount set Balance = Balance +" + Convert.ToInt32(txtAmount.Text) + "where AccountNumber =" + 
        lblSavingAcNo.Text
                        , con, transaction);
                    cmd.ExecuteNonQuery();
                    // If all goes well commit the transaction
                    transaction.Commit();
                    lblMessage.ForeColor = System.Drawing.Color.Green;
                    lblMessage.Text = "Transaction committed";
                }
                catch
                {
                    // If anything goes wrong, rollback the transaction
                    transaction.Rollback();
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = "Transaction rolled back";
                }
            }
            UpdateLabels();
        } 

Testing : Run the application and Enter your Name and Click on Fetch my both A/C balance button , it will fetch the details of your both Account.

fetch Account Details
Account Details

Then Specify the amount you want to transfer and click the "Transfer Money from Current to saving" button. Notice that Amount is deducted from CurrentAccount and added to SavingAccount and the transaction is committed.

updated  Account Details
Updated Account Details

Let's now deliberately introduce a change that would crash the application at run time after executing the first update statement.

CHANGE THE FOLLOWING LINE

  cmd = new SqlCommand("Update SavingAccount set Balance = Balance +" + Convert.ToInt32(txtAmount.Text) + "where 
   AccountNumber=" + lblSavingAcNo.Text, con, transaction);

TO

    cmd = new SqlCommand( "Update SavingAccount2 set Balance = Balance +" + Convert.ToInt32(txtAmount.Text) + "where 
    AccountNumber =" + lblSavingAcNo.Text, con, transaction);

Specify the amount you want to transfer and click the "Transfer Money from Current to saving" button. Notice that the transaction is rolled back and the data integrity is not lost.

Transaction Rolled back
Transaction Rolled back

The complete source code looks like below.


   namespace AdoNet
   {
    public partial class TransactionInAdoNet : System.Web.UI.Page
    {
        string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        private void UpdateLabels()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand(@"select * from SavingAccount where CustomerName=@Name;
                select * from CurrentAccount where CustomerName=@Name", con);
                cmd.Parameters.AddWithValue("@Name", txtName.Text);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    lblSavingAcBalance.Text = rdr["Balance"].ToString();
                    lblSavingAcNo.Text = rdr["AccountNumber"].ToString();
                }
                if (rdr.NextResult())
                {
                    while (rdr.Read())
                    {
                        lblCurrentAcBalance.Text = rdr["Balance"].ToString();
                        lblCurrentAcNo.Text = rdr["AccountNumber"].ToString();
                    }
                }
            }
        }
        protected void btnfetch_Click(object sender, EventArgs e)
        {
            UpdateLabels();
        }

        protected void btnTransfer_Click(object sender, EventArgs e)
        {
            string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                // Begin a transaction. The connection needs to 
                // be open before we begin a transaction
                SqlTransaction transaction = con.BeginTransaction();
                try
                {
                    // Associate the first update command with the transaction

                    SqlCommand cmd = new SqlCommand
                        ("Update CurrentAccount set Balance = Balance -" + Convert.ToInt32(txtAmount.Text) + "where AccountNumber =" + 
     lblCurrentAcNo.Text
                        , con, transaction);
                    cmd.ExecuteNonQuery();
                    // Associate the second update command with the transaction
                    cmd = new SqlCommand("Update SavingAccount2 set Balance = Balance +" + Convert.ToInt32(txtAmount.Text) + "where 
    AccountNumber =" + lblSavingAcNo.Text
                        , con, transaction);
                    cmd.ExecuteNonQuery();
                    // If all goes well commit the transaction
                    transaction.Commit();
                    lblMessage.ForeColor = System.Drawing.Color.Green;
                    lblMessage.Text = "Transaction committed";
                }
                catch
                {
                    // If anything goes wrong, rollback the transaction
                    transaction.Rollback();
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = "Transaction rolled back";
                }
            }
            UpdateLabels();
        }
    }
   }