Consider a situation in which some amount is being transferred from one bank account to another and the process is taking place online. Something goes wrong and the transaction is unable to complete itself. Under this situation what would be the status of the two accounts? Would it be that the amount has been completely transferred or not transferred or an intermediate situation in which some has been transferred and the rest has failed to do so?

Incase of an intermediate result, the situation would become complex. The use of transactions in this type of situations ensures that either the exchange taking place is completed or not completed or there is never an intermediate result.

Sometimes we need to handle transaction on the database level when inserting, updating or deleting data. Here in .Net we are provided by number of classes that will help us to maintain the concurrency. The SqlTransaction class is one that provides the concurrency control and helps us to maintain the atomicity and durability in the data that is there in the database. Transactions are most useful when you are inserting or updating two or more tables, and if one operation fails, even if the other succeeds, both should be rolled back to their previous state

The SqlTransaction class allows you to implement this type of behavior easily by following these steps:

• 
  

  Create a SqlConnection object and open the connection.

  
  


• 
  

  Create a new SqlTransaction object by calling the SqlConnection method Begin Transaction.

  
  


• 
  

  This method will return a transaction object that will be used to commit or rollback any sort of changes that are related to updation, insertion or to deletion.

  
  


• 
  

  Set the SqlCommand property of the transaction to the new instance, so it knows the specific transaction to execute inside (because you can have more than one).

  
  


• 
  

  Then call the command object and then call the commit method.

  
  


• 
  

  Call the rollback method if the transaction fails in between, and then call the rollback method.

  
  

Note: Remember that before calling the Commit method you need to specify the set of commands and operations to be performed. Each of these is added (in the order you specify) to the current transaction, where they are pending and waiting to be committed to the database.

Locking:

One other piece of information you should note is that when you create a transaction by calling the connection object's BeginTransaction method you can also specify the Isolation Level , which dictates the locking behavior as follows:

1. System.Data.IsolationLevel.Chaos - Pending changes from a higher isolated transaction cannot be overwritten.


2. System.Data.IsolationLevel.ReadCommitted - Locks are created to avoid dirty reads, but data can still be changed even before a transaction is ended.


3. System.Data.IsolationLevel.ReadUncommitted - There are no shared or exclusive locks.


4. System.Data.IsolationLevel.RepeatableRead - Locks are placed on all data preventing non-repeatable reads (phantom rows could still exist though).


5. System.Data.IsolationLevel.Serializable - Uses a range lock on a DataSet to prevent updating or inserting until the transaction is completed.


6. System.Data.IsolationLevel.Unspecified - An undetermined isolation level.

BeginTransaction method: This method starts a new transaction. It requires an open connection. The method utilizes the open connection and changes made to the data then are tracked as a transaction, until the changes are committed or rolled back.

Commit ( Transaction ): Saves all the changes that have been made, permanently to the data store. The method is called incase of a successful transaction.

Rollback ( Transaction ): Returns the source to its previous state, by abandoning the changes that were made. This method is normally called incase of an unsuccessful transaction.

Example: Demonstrate SQL Transaction

TransactionIn.aspx

<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="TransactionIn.aspx.cs" Inherits ="TransactionIn" %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns ="http://www.w3.org/1999/xhtml" >

<head runat ="server">

</head >

<body >

<form id ="form1" runat ="server">

<div> <strong >

<span style ="text-decoration: underline"> Transaction in .Net < br /></ span >

<table>

< tr >< td >

<asp:Label ID ="Label1" runat ="server" Text ="CategoryName"></ asp : Label >

</ td >< td ></ td >< td >

<asp:TextBox ID ="txtCategoryName" runat ="server"></ asp : TextBox ></ td ></ tr >

<tr><td>

<asp:Label ID ="Label2" runat ="server" Text ="Description"></ asp : Label ></ td >

<td></td><td>

<asp:TextBox ID ="txtDescription" runat ="server"></ asp : TextBox ></ td > </ tr >

<tr><td>

<asp:Label ID ="Label3" runat ="server" Text ="FirstName"> </ asp : Label ></ td >

<td ></ td >< td >

<asp:TextBox ID ="txtFirstName" runat ="server"></ asp : TextBox ></ td ></ tr >

<tr><td>

<asp:Label ID ="Label4" runat ="server" Text ="LastName"></ asp : Label ></ td >< td ></ td > < td >

<asp:TextBox ID ="txtLastName" runat ="server"></ asp : TextBox ></ td >

</ tr >< tr >< td >

<asp:Button ID ="Button2" runat ="server" OnClick ="Button2_Click" Text ="Insert" /></ td >< td ></ td >< td >

<asp:Button ID ="Button1" runat ="server" OnClick ="Button1_Click" Text ="RollBack"/>

</td></tr></table></strong> </div></form>

</body>

</html>

TransactionIn.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class TransactionIn : System.Web.UI. Page

{

  protected void Button1_Click( object sender, EventArgs e)

    { //Transaction Implementation : Rollback

  string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;

    SqlConnection conn = new SqlConnection (strConn);

    SqlTransaction trans;

    conn.Open();

    //Start a transaction

    trans = conn.BeginTransaction();

   try {

     string sqlQuery = "Insert into Categories(CategoryName,Description)values(@CategoryName,@Description) " ;

     string sqlQuery1 = "Insert into Employees(FirstName,LastName,Title) values(@FirstName,@LastName) " ;

            SqlCommand cmd = new SqlCommand ();

            cmd.Connection = conn;

            cmd.CommandText = sqlQuery;

            cmd.CommandType = CommandType .Text;

    //Connect the transaction object with the command object

            cmd.Transaction = trans;

            cmd.Parameters.AddWithValue( "@CategoryName" ,  txtCategoryName.Text);

            cmd.Parameters.AddWithValue( "@Description" ,txtDescription.Text);

            cmd.ExecuteNonQuery();

            SqlCommand cmd1 = new SqlCommand ();

            cmd1.Connection = conn;

            cmd1.CommandText = sqlQuery1;

            cmd1.CommandType = CommandType .Text;

        //Connect the transaction object with the command object

            cmd1.Transaction = trans;

            cmd1.Parameters.AddWithValue( "@FirstName" ,   txtFirstName.Text);

            cmd1.Parameters.AddWithValue( "@LastName" ,txtLastName.Text);

            cmd1.ExecuteNonQuery();

        //Transaction has completed successfully.

        //Save changes to the data store.

            trans.Commit();

            Response.Write( "Transaction Completed" );

        }catch ( Exception ex) {

                trans.Rollback();

                Response.Write(ex.ToString());

                Response.Write( "Transaction Rolled Back" );

        }  finally {

            conn.Close();

        }

}

    protected void Button2_Click( object sender, EventArgs e)

        { //Insert Record Using Transaction

    string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;

        SqlConnection conn = new SqlConnection (strConn);

        SqlTransaction trans;

        conn.Open();

    //Start a transaction

        trans = conn.BeginTransaction();

        try

        {

            string sqlQuery = "Insert into Categories(CategoryName,Description) values(@CategoryName,@Description) " ;

            string sqlQuery1 = "Insert into Employees(FirstName,LastName) values(@FirstName,@LastName) " ;

            SqlCommand cmd = new SqlCommand ();

            cmd.Connection = conn;

            cmd.CommandText = sqlQuery;

            cmd.CommandType = CommandType .Text;

        //Connect the transaction object with the command object

            cmd.Transaction = trans;

            cmd.Parameters.AddWithValue( "@CategoryName" , txtCategoryName.Text);

            cmd.Parameters.AddWithValue( "@Description" , txtDescription.Text);

            cmd.ExecuteNonQuery();

            SqlCommand cmd1 = new SqlCommand ();

            cmd1.Connection = conn;

            cmd1.CommandText = sqlQuery1;

            cmd1.CommandType = CommandType .Text;

        //Connect the transaction object with the command object

            cmd1.Transaction = trans;

            cmd1.Parameters.AddWithValue( "@FirstName" , txtFirstName.Text);

            cmd1.Parameters.AddWithValue( "@LastName" ,txtLastName.Text);

        //conn.Open();

            cmd1.ExecuteNonQuery();

        //Transaction has completed successfully.

        //Save changes to the data store.

            trans.Commit();

            Response.Write( "Transaction Completed" );

        }catch ( Exception ex)

        {

        //Incase if an error was raised, cancel the transaction.

        //All changes are to be dropped as the transaction has

            trans.Rollback();

            Response.Write(ex.ToString());

            Response.Write( "Transaction Rolled Back" );

        }finally{

            conn.Close();

        }

    }

}

Output:

Clicking On “Insert” Button

Clicking On: Rollback Button