Transaction
It ensures that either all the operation will successfully execute or none of them will execute.
Let's see with an example:
We have 2 tables
1. select * from employeeMaster
2. select * from companyAccount
In the aspx file put the following code
In code behind add the following code
It ensures that either all the operation will successfully execute or none of them will execute.
Let's see with an example:
We have 2 tables
1. select * from employeeMaster
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1 {
width: 100%;
}
.auto-style2 {
width: 217px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:Label ID="lblHeader" runat="server" Text="Credit Salary" Font-Italic="true" Font-Bold="true"></asp:Label></td>
</tr>
<tr>
<td>Company Name :
<asp:DropDownList ID="ddlCompnayAccount" runat="server"></asp:DropDownList></td>
</tr>
<tr>
<td>Employee Name :
<asp:DropDownList ID="ddlEmpName" runat="server"></asp:DropDownList></td>
</tr>
<tr>
<td class="auto-style2">Amount
:
<asp:TextBox ID="txtAmount" runat="server" Width="100px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnTransfer" runat="server" OnClick="btnClick_Tranfer" Text="Transfer" />
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblError" runat="server"></asp:Label>
</td>
</tr>
</table>
<br />
<br />
</div>
<div>
<table>
<tr>
<td>
<asp:GridView ID="gdvEmpDetail" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</td>
<td>
</td>
<td>
<asp:GridView ID="gdvComapnyDetail" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
1. Without Transaction Scope
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ADONET
{
public partial class UsingTransaction : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployee();
BindCompany();
}
}
protected void BindEmployee()
{
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("Select * from
employeeMaster", sqlcon);
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
ddlEmpName.DataSource = dt;
ddlEmpName.DataTextField = "EmpName";
ddlEmpName.DataValueField = "EmpID";
ddlEmpName.DataBind();
gdvEmpDetail.DataSource = dt;
gdvEmpDetail.DataBind();
}
}
protected void BindCompany()
{
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("Select * from
companyAccount", sqlcon);
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
ddlCompnayAccount.DataSource =
dt;
ddlCompnayAccount.DataTextField
= "CompanyName";
ddlCompnayAccount.DataValueField = "AccountID";
ddlCompnayAccount.DataBind();
gdvComapnyDetail.DataSource =
dt;
gdvComapnyDetail.DataBind();
}
}
protected void btnClick_Tranfer(object sender, EventArgs e)
{
int EmpID = Convert.ToInt32(ddlEmpName.SelectedValue);
int AccountID = Convert.ToInt32(ddlCompnayAccount.SelectedValue);
int Amount = Convert.ToInt32(txtAmount.Text);
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
sqlcon.Open();
try
{
int Success = 0;
SqlCommand sqlcmd1 = new SqlCommand("Update companyAccount set
AccountBalance=AccountBalance - " +
Amount + " where AccountID=" + AccountID, sqlcon);
Success =
sqlcmd1.ExecuteNonQuery();
sqlcmd1 = new SqlCommand("Update employeeMaster set Salary=Salary + " + Amount + "
where EmpID=" + EmpID, sqlcon);
Success +=
sqlcmd1.ExecuteNonQuery();
if (Success < 2)
throw new Exception("Transaction Failed");
lblError.Text = "Transaction Successfull";
lblError.ForeColor =
System.Drawing.Color.Green;
}
catch (Exception ex)
{
lblError.Text = "Transaction Failed";
lblError.ForeColor =
System.Drawing.Color.Red;
}
finally
{
BindEmployee();
BindCompany();
}
}
}
}
}
Lets Execute our application
Lets Transfer 5000 to Sanjeet Account
Lets increase the EmpID by 99. There is no Such EmpID exist in our system.
protected void btnClick_Tranfer(object sender, EventArgs e)
{
int EmpID = Convert.ToInt32(ddlEmpName.SelectedValue);
int AccountID = Convert.ToInt32(ddlCompnayAccount.SelectedValue);
int Amount = Convert.ToInt32(txtAmount.Text);
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
sqlcon.Open();
try
{
int Success = 0;
SqlCommand sqlcmd1 = new SqlCommand("Update companyAccount set
AccountBalance=AccountBalance - " +
Amount + " where AccountID=" + AccountID, sqlcon);
Success =
sqlcmd1.ExecuteNonQuery();
sqlcmd1 = new SqlCommand("Update employeeMaster set Salary=Salary + " + Amount + "
where EmpID=" + (EmpID + 99), sqlcon);
Success +=
sqlcmd1.ExecuteNonQuery();
if (Success < 2)
throw new Exception("Transaction Failed");
lblError.Text = "Transaction Successfull";
lblError.ForeColor =
System.Drawing.Color.Green;
}
catch (Exception ex)
{
lblError.Text = "Transaction Failed";
lblError.ForeColor =
System.Drawing.Color.Red;
}
finally
{
BindEmployee();
BindCompany();
}
}
}
Transaction Failed but there is a deduction in Company Account and there is no change in Employee Account.
2. With Transaction Scope
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ADONET
{
public partial class UsingTransaction : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployee();
BindCompany();
}
}
protected void BindEmployee()
{
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("Select * from
employeeMaster", sqlcon);
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
ddlEmpName.DataSource = dt;
ddlEmpName.DataTextField = "EmpName";
ddlEmpName.DataValueField = "EmpID";
ddlEmpName.DataBind();
gdvEmpDetail.DataSource = dt;
gdvEmpDetail.DataBind();
}
}
protected void BindCompany()
{
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("Select * from
companyAccount", sqlcon);
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
ddlCompnayAccount.DataSource =
dt;
ddlCompnayAccount.DataTextField
= "CompanyName";
ddlCompnayAccount.DataValueField = "AccountID";
ddlCompnayAccount.DataBind();
gdvComapnyDetail.DataSource =
dt;
gdvComapnyDetail.DataBind();
}
}
protected void btnClick_Tranfer(object sender, EventArgs e)
{
int EmpID = Convert.ToInt32(ddlEmpName.SelectedValue);
int AccountID = Convert.ToInt32(ddlCompnayAccount.SelectedValue);
int Amount = Convert.ToInt32(txtAmount.Text);
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
sqlcon.Open();
SqlTransaction transaction = sqlcon.BeginTransaction();
try
{
int Success = 0;
SqlCommand sqlcmd1 = new SqlCommand("Update companyAccount set
AccountBalance=AccountBalance - " +
Amount + " where AccountID=" + AccountID, sqlcon, transaction);
Success =
sqlcmd1.ExecuteNonQuery();
sqlcmd1 = new SqlCommand("Update employeeMaster set Salary=Salary + " + Amount + "
where EmpID=" + EmpID, sqlcon,
transaction);
Success +=
sqlcmd1.ExecuteNonQuery();
if (Success < 2)
throw new Exception("Transaction Failed");
transaction.Commit();
lblError.Text = "Transaction Successfull";
lblError.ForeColor =
System.Drawing.Color.Green;
}
catch (Exception ex)
{
transaction.Rollback();
lblError.Text = "Transaction Failed";
lblError.ForeColor =
System.Drawing.Color.Red;
}
finally
{
transaction.Dispose();
BindEmployee();
BindCompany();
}
}
}
}
}
Lets Transfer 5000 to Sanjeet Account
Lets increase the EmpID by 99. There is no Such EmpID exist in our system.
protected void btnClick_Tranfer(object sender, EventArgs e)
{
int EmpID = Convert.ToInt32(ddlEmpName.SelectedValue);
int AccountID = Convert.ToInt32(ddlCompnayAccount.SelectedValue);
int Amount = Convert.ToInt32(txtAmount.Text);
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
sqlcon.Open();
SqlTransaction transaction = sqlcon.BeginTransaction();
try
{
int Success = 0;
SqlCommand sqlcmd1 = new SqlCommand("Update companyAccount set
AccountBalance=AccountBalance - " +
Amount + " where AccountID=" + AccountID, sqlcon, transaction);
Success =
sqlcmd1.ExecuteNonQuery();
sqlcmd1 = new SqlCommand("Update employeeMaster set Salary=Salary + " + Amount + "
where EmpID=" + (EmpID + 99), sqlcon,
transaction);
Success +=
sqlcmd1.ExecuteNonQuery();
if (Success < 2)
throw new Exception("Transaction Failed");
transaction.Commit();
lblError.Text = "Transaction Successfull";
lblError.ForeColor =
System.Drawing.Color.Green;
}
catch (Exception ex)
{
transaction.Rollback();
lblError.Text = "Transaction Failed";
lblError.ForeColor =
System.Drawing.Color.Red;
}
finally
{
transaction.Dispose();
BindEmployee();
BindCompany();
}
}
}
Transaction Failed but there is no deduction. The state of the system is maintained
Posts
