What are Transactions?
A transaction symbolizes code or a set of components or procedures which must be executed as a unit. All the methods must execute successfully or the complete unit fails. A transaction can be described to cover the ACID properties for mission critical applications.
What are the ACID Properties?
- Atomicity
- Consistency
- Isolation
- Durability
Transfer Funds Sample
We will build a sample ASP.NET web form for a fictitious bank which will transfer a specified amount from one account to another - if the balance in the first account is sufficient to cover the transfer.
First we need to create the database we will using in the example.
I used an MS Access database containing only one table : tblAccount. Field Name Field Type AccNumber Text dBalance Double
Listing 1 displays the code for the web page. Save the web page as Test.aspx.
First include the Namespaces required for accessing the data.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
Here is the function which processes the transaction for transferring the data.
For this example we assume that the transaction should be rolled back (Cancelled) if :
- There are insufficient funds in the From Account to cover the transfer.
-
We create the Connection needed to connect to our database.
OleDbConnection
In real life we would use Server.MapPath to map to the location of the database.
We use the Data Reader oReader to check the validity of the amount in the From Account. The crux of the function is to execute the two SQL queries one to subtract the amount from the From Account and one to add the same amount to the balance in the To Account.
We start the transaction after we have created the data objects .The transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits.
OleDbTransaction Trans = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = Trans;
Within the Try block run the transaction and Commit the transaction if everything proceeds smoothly. Committing a transaction will write the changes to the database.
If there is an exception we will Roll Back the transaction. This will cancel any changes that have been carried out as a part of the transaction. This is how we maintain the integrity of our transaction.
try
{
oReader = cmd.ExecuteReader();
oReader.Read();
dCurrBalance = oReader.GetDouble(0);
oReader.Close();
if (dCurrBalance < Convert.ToDouble(txtAmt.Text))
{
throw (new Exception("Insufficient funds for transfer"));
}
strSQL = "Update tblAccount set dbalance = dBalance - " + txtAmt.Text + " where AccNumber = '" +
txtFrom.Text + "'";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
strSQL = "Update tblAccount set dbalance = dBalance + " + txtAmt.Text + " where AccNumber = '" +
txtTo.Text + "'";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
Trans.Commit();
lbl.Text = "true";
}
catch (Exception ex)
{
Trans.Rollback();
lbl.Text = "Error: " + ex.Message;
}
finally
{
Conn.Close();
}
throw (new Exception("Insufficient funds for transfer"));
The string passed in the constructor of the Exception object initializes the message for the Exception that will be raised.
Finally we indicate the results of the transfer activity to the user .
lbl.Text = "Fund Transfer of Amount " + txtAmt.Text + " from Account " + txtFrom.Text + " to Account " + txtTo.Text + " was executed successfully.";
OR
lbl.Text = "Error: " + ex.Message;
In real life, we would have converted the error message to a more meaningful and user friendly message.
Here is the complete code listing for the web form.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<title>Transfer Funds</< span>title>
<script language="C#" runat="server">
protected void TransferFund(Object Sender, EventArgs e)
{
String strSQL = "Select dBalance FROM tblAccount where AccNumber='" + txtFrom.Text + "'";
double dCurrBalance;
SOURCE=c:\\inetpub\\wwwroot\\dotnet\\test.mdb;");
Conn.Open();
OleDbDataReader oReader;
OleDbCommand cmd = new OleDbCommand(strSQL,
OleDbTransaction Trans = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = Trans;
try
{
oReader = cmd.ExecuteReader();
oReader.Read();
dCurrBalance = oReader.GetDouble(0);
oReader.Close();
if (dCurrBalance < Convert.ToDouble(txtAmt.Text))
{
throw (new Exception("Insufficient funds for transfer"));
}
strSQL = "Update tblAccount set dbalance = dBalance - " + txtAmt.Text + " where AccNumber = '"
+ txtFrom.Text + "'";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
strSQL = "Update tblAccount set dbalance = dBalance + " + txtAmt.Text + " where AccNumber = '"
+ txtTo.Text + "'";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
Trans.Commit();
lbl.Text = "true";
}
catch (Exception ex)
{
Trans.Rollback();
lbl.Text = "Error: " + ex.Message;
}
finally
{
Conn.Close();
}
}
</< span>script>
</< span>head>
<body>
<form id="frmTransfer" runat="server">
<asp:Label ID="lblFrom" runat="server">Enter the account number from which to transfer
funds</< span>asp:Label>
<asp:TextBox ID="txtFrom" runat="server"></< span>asp:TextBox><br />
<asp:Label ID="lblTo" runat="server">Enter the account number to which to transfer funds</< span>asp:Label>
<asp:TextBox ID="txtTo" runat="server"></< span>asp:TextBox><br />
<asp:Label ID="lblAmount" runat="server">Enter the amount to transfer</< span>asp:Label>
<asp:TextBox ID="txtAmt" runat="server"></< span>asp:TextBox><br />
<asp:Button ID="Button1" OnClick="TransferFund" runat="server" Text="Start Transfer">
</< span>asp:Button><br />
<asp:Label ID="lbl" runat="server"></< span>asp:Label>
</< span>form>
</< span>body>
</< span>html>
Note: When the transaction is rolled back (Insufficient funds or an error in the SQL statements) the Balance field in both the From Account and To Account in the database is not updated.
