Transactions in ASP.NET

What are Transactions?

A transaction symbolizes code or a set of components or procedures that 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?

1. Atomicity
2. Consistency
3. Isolation
4. Durability

Transfer Funds Sample

We will build a sample ASP.NET web form for a fictitious bank that 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 use 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.

1. There are insufficient funds in the From Account to cover the transfer.
2. Either of the SQL statements for marking credit or debit in the To and From accounts results in an error.

We create the Connection needed to connect to our database.

OleDbConnection Conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\\inetpub\\wwwroot\\dotnet\\test.mdb;");

In real life, we would use a Server.MapPath to map to the location of the database.

We use the Data Reader reader 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 a maximum number of positive commits.

Create the transaction and associate the transaction with the OleDbCommand as follows.

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 transactions.

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();
}

Note. how do we Throw an exception if the balance in the From Account is less than the transfer amount?

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.

Listing 1.Test.aspx: Transfer Funds Web Page.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<html>

<head>

    <title>Transfer Funds</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;
            OleDbConnection Conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\\inetpub\\wwwroot\\dotnet\\test.mdb;");
            Conn.Open();
            OleDbDataReader oReader;
            OleDbCommand cmd = new OleDbCommand(strSQL, Conn);
            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();
            }
        }
    </script>

</head>

<body>

    <form id="frmTransfer" runat="server">
        <asp:Label ID="lblFrom" runat="server">Enter the account number from which to transfer funds</asp:Label>
        <asp:TextBox ID="txtFrom" runat="server"></asp:TextBox><br />
        <asp:Label ID="lblTo" runat="server">Enter the account number to which to transfer funds</asp:Label>
        <asp:TextBox ID="txtTo" runat="server"></asp:TextBox><br />
        <asp:Label ID="lblAmount" runat="server">Enter the amount to transfer</asp:Label>
        <asp:TextBox ID="txtAmt" runat="server"></asp:TextBox><br />
        <asp:Button ID="Button1" OnClick="TransferFund" runat="server" Text="Start Transfer"></asp:Button><br />
        <asp:Label ID="lbl" runat="server"></asp:Label>
    </form>

</body>

</html>

Figure 1: Front-end web page for the transaction example.

Figure 2. Successfully Committed Transactions.

Figure 3. Insufficient Funds RollBack.

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.

Conclusion

Real-time web projects will need an extensive use of transactions. The principle is All or None.