Part 21 Transactions in ADO NET

c# transaction operation
c# transaction rollback on exception
c# transaction try catch
c# transaction using rollback

In this video we will discuss how to implement Transactions in ADO.NET

What is a Transaction
A Transaction ensures that either all of the database operations succeed or all of them fail. This means the job is never half done, either all of it is done or nothing is done. Let’s understand this with an example.

When we click “Transfer $10 from Account A1 to Account A2” button, we should subtract 10 from A1 account and add 10 to A2 account. So there will be 2 database UPDATE statements. What do you think will happen if only the first update statement is executed successfully and not the second statement. $10 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 other fails we should also rollback the changes made by the first statement to maintain the integrity of the data. This can be achieved using transactions in ado.net.

namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetAccountsData();
}
}

private void GetAccountsData()
{
string cs = ConfigurationManager.ConnectionStrings[“CS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“Select * from Accounts”, con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr[“AccountNumber”].ToString() == “A1”)
{
lblAccountNumber1.Text = “A1”;
lblName1.Text = rdr[“CustomerName”].ToString();
lblBalance1.Text = rdr[“Balance”].ToString();
}
else
{
lblAccountNumber2.Text = “A2”;
lblName2.Text = rdr[“CustomerName”].ToString();
lblBalance2.Text = rdr[“Balance”].ToString();
}
}
}
}

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 Accounts set Balance = Balance – 10 where AccountNumber = ‘A1′”, con, transaction);
cmd.ExecuteNonQuery();
// Associate the second update command with the transaction
cmd = new SqlCommand(“Update Accounts set Balance = Balance + 10 where AccountNumber = ‘A2′”, 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”;
}
}
GetAccountsData();
}
}
}

Testing : Run the apllication and click the “Transfer $10 from Account A1 to Account A2” button. Notice that $10 is deducted from Account A1 and added to Account A2 and the transaction is committed.

Text version of the video

Slides

All ADO .NET Text Articles

All ADO .NET Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic