.Net Learning made easy: Transactions in ADO.Net

In this blog 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.

We will be using the following Accounts table in this demo

The table has got 2 Accounts (A1 and A2). We want to design a web application totransfer $10 from Account A1 to Account A2. The design of the webform should be as shown below.

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.

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

Create Table Accounts

(

AccountNumber nvarchar(10) primary key,

CustomerName nvarchar(50),

Balance int

)

Insert into Accounts values('A1', 'Mark', 100)

Insert into Accounts values('A2', 'Pam', 100)

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

Step 3 : Include connection string in web.config file
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<connectionStrings>
<add name="DBConnectionString" connectionString="Data Source=REDDY-PC\REDDYSQLEXP; database=DB_H2K;Integrated Security=true;" providerName="System.Data.SqlClient;"/>
</connectionStrings>
<appSettings>
<add key="ValidationSettings:UnobtrusiveValidationMode" value="None"/>
</appSettings>

</configuration>

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

<div>

<tr>

<td class="auto-style7">Account Number</td>

<asp:Label ID="lblAccountNumber1" runat="server"></asp:Label>

</td>

<asp:Label ID="lblAccountNumber2" runat="server"></asp:Label>

</td>

</tr>

<tr>

<td class="auto-style8">Customer Name</td>

<asp:Label ID="lblName1" runat="server"></asp:Label>

</td>

<asp:Label ID="lblName2" runat="server"></asp:Label>

</td>

</tr>

<tr>

<td class="auto-style8">Balance</td>

<asp:Label ID="lblBalance1" runat="server"></asp:Label>

</td>

<asp:Label ID="lblBalance2" runat="server"></asp:Label>

</td>

</tr>

<tr>

<asp:Button ID="btnTransfer" runat="server" OnClick="btnTransfer_Click" Text="Transfer 10$ from A1 to A2" />

</td>

</tr>

<tr>

<asp:Label ID="lblMessage" runat="server"></asp:Label>

</td>

</tr>

</table>

</div>

</form>

Step 5 : Copy and paste the following code in the code-behind file.

using System;

using System.Configuration;

using System.Data.SqlClient;

namespace ADO_Demo

{

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

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

GetAccountsData();

}

private void GetAccountsData()

{

string strConn = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

using (SqlConnection conn = new SqlConnection(strConn))

{

conn.Open();

SqlCommand com = new SqlCommand("select * from tbl_Accounts", conn);

SqlDataReader dr = com.ExecuteReader();

while (dr.Read())

{

if (dr["AccountNumber"].ToString() == "A1")

{

lblAccountNumber1.Text = "A1";

lblName1.Text = dr["CustomerName"].ToString();

lblBalance1.Text = dr["Balance"].ToString();

}

else

{

lblAccountNumber2.Text = "A2";

lblName2.Text = dr["CustomerName"].ToString();

lblBalance2.Text = dr["Balance"].ToString();

}

protected void btnTransfer_Click(object sender, EventArgs e)

{

string strConn = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

using (SqlConnection conn = new SqlConnection(strConn))

{

conn.Open();

SqlTransaction transaction = conn.BeginTransaction();

try

{

SqlCommand cmd = new SqlCommand("Update tbl_Accounts set Balance=Balance - 10 WHERE AccountNumber = 'A1'", conn,transaction);

cmd.ExecuteNonQuery();

cmd = new SqlCommand("Update tbl_Accounts set Balance=Balance + 10 WHERE AccountNumber = 'A2'", conn, transaction);

cmd.ExecuteNonQuery();

transaction.Commit();

lblMessage.Text = "Transaction Succeeded";

lblMessage.ForeColor = System.Drawing.Color.Green;

}

catch (Exception)

{

transaction.Rollback();

lblMessage.Text = "Transaction failed, please try again later.";

lblMessage.ForeColor = System.Drawing.Color.Red;

}

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.

Click on button 'Transfer 10$ from A1 to A2'

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 From

cmd = new SqlCommand("Update tbl_Accounts set Balance=Balance + 10 WHERE AccountNumber = 'A2'", conn, transaction);

cmd = new SqlCommand("Update tbl_Accounts1 set Balance=Balance + 10 WHERE AccountNumber = 'A2'", conn, transaction);

Run the application again and click the "Transfer $10 from Account A1 to Account A2"button. Notice that the transaction is rolled back and the data integrity is not lost.

.Net Learning made easy

my own pages

Tuesday, May 12, 2015

Transactions in ADO.Net

No comments:

Post a Comment