Transactions in ASP.NET

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?

1. Atomicity
2. Consistency
3. Isolation
4. 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 :

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 = newOleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\\inetpub\\wwwroot\\dotnet\\test.mdb;");

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

Note how 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.";ORlbl.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 = newOleDbConnection("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 !

What TransactionScope isYesterday I was stuck with some logic to maintain a single transaction in a multi DML operation in SQL Server and Oracle Database. I mean to say that I had to set a transaction on a single click where an insert or update was to be done in a SQL database and an insert or update was to be done in a Oracle Database. After extensive research I finally got the solution from the MSDN. I want to explain my experience with a small example.
 
Namespace Name: System.Transactions.TransactionScope

Definition: TransactionalScope makes your code block Transactional.
 
You can easily maintain one transaction for multiple databases or a single database with multiple connectionstrings, using TransactionScope.

When you use TransactionScope there is no need to close any Database connections in the middle.

Just make an object of the TransactionScope class with using. Write all your code in this block and after completion of all your operations call "objTransactionscope.complete()". Ensure one thing; you should write a try/catch block in the TransactionScope block.
 
Syntax to use TransactionScope

using (TransactionScope transactionScope = new TransactionScope())
{

      try

      {

           method1() // Oracle DML operation

           method2() // Sql DML operation

           method3() // Oracle DML operation

           // transactionScope.complete()

           // transactionScope.Dispose();

      }

      catch (TransactionException ex)

      {

          transactionScope.Dispose();

          MessageBox.Show("Transaction Exception Occured");

      }

}
 
Now if an exception occurrs in method1(), method2() or method3() then it will catch the exception in the catch block and all the operations will be rolled back automatically. There is no option to call a rollback manually in TransactionScope.
 
Take care of one thing, that there is no connection.close() or dispose() in any methods (1, 2 or 3) otherwise it will give you the error:
 
"the transaction has aborted"

when you call transactionScope.Dispose() after completion of all your objects that are used in this TransactionScope dispose.
 
Now I will explain a simple scenario for a better understanding.
 
Suppose I have a table in SQL Server named TestSql that contains the 3 columns Name, Salary and Status.
 

CREATE TABLE [dbo].[TESTSql](

   [Name] [varchar](200) NULL,

   [Salary] [int] NULL,

   [Status] [varchar](50) NULL

)
 
Name contains emp name, salary contains emp salary and status contains nothing initially, in other words NULL.
 
I have another table in an Oracle database named TestOracle. It also contains the 3 columns Name, Salary, Srlno.

CREATE TABLE TESTORACLE

(

  NAME    VARCHAR2(200 BYTE),

  SALARY  INTEGER,

  SRLNO   INTEGER

)
 
Name and salary contains the same info as the Sqlserver table. Here Srlno is an autogenerated number that shows how many rows I currently have.
In this example I have some emp records in SQL Server table. I need to upload this data into the Oracle Server. If the data insertion in the Oracle Server is successful then I update the status in the sqlserver table with "UPD".
 
I have created a form where two TextBoxes are placed. There you need to enter your Oracle connectiontionstring and Sqlserver in the proper manner. When you press the "GO" button if everything occurs correctly then you will get a success msg.
 
If you get any error like Unable to load DLL (oramts.dll) 

then you need to go to the Oracle folder (where Oracle is installed) and find the DLL oramts.dll and copy and paste this file into the system32 folder in the C drive.
 
Code on Button_Click

using (TransactionScope transactionScope = new TransactionScope())

{

     try

     {

           string SqlString = txtSqlConn.Text.Trim();

           string OraString = txtOracleConn.Text.Trim();

           //-------- Get data from Sql server --------

           DataSet TotData = new DataSet();

           TotData = DbSettings.GetDataSetSQL(SqlString, "Select * from TestSql");

           for (int i = 0; i < TotData.Tables[0].Rows.Count; i++)

           {

                 DataSet Oracleds = new DataSet();

                 Oracleds = DbSettings.GetDataSetOracle(OraString, "Select NVL(MAX(SRLNO),0)+1 from TestOracle");

                 int k = DbSettings.ExecuteNonQueryOracle(OraString, "Insert into TestOracle(NAME,SALARY,SRLNO) values('" + TotData.Tables[0].Rows[i][0].ToString() + "'," + Convert.ToInt32(TotData.Tables[0].Rows[i][1]) + "," + Convert.ToInt32(Oracleds.Tables[0].Rows[0][0]) + ")");

                if (k == 1)

                {

                    int j = DbSettings.ExecuteNonQuerySQL(SqlString, "Update TestSql Set status='UPD' where Name='" + TotData.Tables[0].Rows[i][0].ToString() + "'");

                }

         }

         transactionScope.Complete();

         transactionScope.Dispose();

         MessageBox.Show("Successful");

         }

         catch (TransactionException ex)

         {

             transactionScope.Dispose();

             MessageBox.Show("Transaction Exception Occured");

        }

      catch (Exception ex1)

      {

         MessageBox.Show(ex1.Message);

      }

}

I think you will find this article useful. Thanks for reading.

1. Introduction

TransactionScope is a very special and important class in the .NET Framework. Supporting transactions from a code block is the main responsibility of this class. We often use this class for managing local as well as distributed transactions from our code. Use of TransactionScope is very simple and straightforward. It is very reliable and easy to use. For this reason it is very popular among .NET developers. In this article, I explain transaction related theory with code sample, and show various scenarios where we can use TransactionScope with various options for managing real life transactions.

2. Background

Transaction management is very, very important to any business application. Each and every large scale development framework provides a component for managing transactions. .NET Framework is a large development framework and it also provides its own transaction management component. Before the launch of .NET Framework 2.0 we used SqlTransaction to manage transactions. From version 2 .NET Framework has theTransactionScope class. This class is available in the System.Transactions assembly. This class provides a transactional framework with the help of which any .NET developer can write transactional code without knowing much details. For this reason it is very popular among .NET developers and they widely use it for managing transactions. But the story is not finished yet. I will say the story has only just started.

In the real world any one you will find exceptional scenarios, exceptional issues where only a knowledge of how to use TransactionScope is not good enough. To resolve transactional issues like deadlocks, timeouts, etc., you must know each and every concept directly/indirectly related to a transaction. There is no alternative. So the concepts of a transaction and its related components need to be clear.

3. How to Use TransactionScope

Use of TransactionScope in a .NET application is very, very simple. Any one can use it by following these steps:

1. Add a System.Transactions assembly reference to the project.
2. Create a transactional scope/area with the help of the TransactionScope class starting with a usingstatement.
3. Writing code which needs to have transactional support.
4. Execute the TransactionScope.Complete method to commit and finish a transaction.

Really, as simple as that. But in a real life project only that knowledge is not sufficient. You need more transaction related knowledge, otherwise you can not handle transaction related issues. So first of all, we should be clear about the transactional concept.

4. Transaction

What is a transaction? You can find the definition of a transaction from various sources like Wikipedia, other websites, books, articles, blogs. In a very short, we can say, a series/number of works treated as a whole, either completed fully or not at all.

Example: Transfer money from Bank Account-A to Account-B

Series of (actually two) tasks/processes:

1. Withdraw amount from Account-A
2. Deposit that amount to Account-B

We understand that transfer of money (from Account-A to Account-B) consists of two individual processes. Transferring money will only be accurate and successful if both the processes are individually successful. If that is not happening, suppose process-1 succeeds but process-2 fails, then the money will be deducted from Account-A but not deposited to Account-B. If that happens, it will be very bad and no one will accept it.

5. Business Transaction

Business transactions are interactions between Customer/Supplier/StackHolders and other parties who are involved in doing business. In this article I am not going to present anything regarding business transactions.

6. Database Transaction

In software development, when we say transaction by default we guess that it is a database transaction. In a database transaction we can say, a series of data manipulation statements (insert/update/delete) execute as a whole. All statements either successfully execute, or will fail each and every statement, so that the database is in consistent mode. Database transactions actually represent a database state change in an accurate way.

7. Local Transaction

A transaction where a series of data manipulation statements execute as a whole on a single data source/database. It is actually a single phase transaction handled by a database directly. To manage local transactions, System.Transactions has a Lightweight Transaction Manager (LTM). It acts like a gateway. All transactions are started by System.Transactions are handled directly by this component. If it finds the transaction nature is distributed based on some predefined rules it has a fallback transaction to the MSDTC distributed transaction.

8. Distributed Transaction

A transaction which works with multiple data sources is called a distributed transaction. If a transaction fails then the affected data sources will be rolled back. In System.Transactions, MSDTC (Microsoft Distributed Transaction Coordinator) manages distributed transactions. It implements a two phase commit protocol. A distributed transaction is much slower than a local transaction. The transaction object automatically escalates a local transaction to a distributed transaction when it understands that a distributed transaction is needed. The developer can not do anything here.

9. Distributed Transaction System Architecture

We know that in a distributed transaction, several sites are involved. Each site has two components:

1. Transaction Manager
2. Transaction Coordinator

1. Transaction Manager: Maintains a log and uses that log if recovery is needed. It controls the whole transaction by initiating and completing, and managing the durability and atomicity of a transaction. It also coordinates transactions across one or more resources. There are two types of transaction managers.

1. Local Transaction Manager: Coordinates transaction over a single resource only.
2. Gloabl Transaction Manager: Coordinates transaction over multiple resources.

2. Transaction Coordinator: Starting the execution of transactions that originate at the site. Distributes subtransactions at appropriate sites so that they can execute in those sites. Coordinates each transaction of each site. As a result the transaction is committed or rolled back to all sites.

10. Connection Transaction

Transaction which is tied directly with a database connection (SqlConnection) is called Connection Transaction.SqlTransaction (IDbTransaction) is an example of a connection transaction. In .NET Framework 1.0/1.1 we use SqlTransaction.

Hide   Copy Code

string connString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (IDbTransaction tran = conn.BeginTransaction())
    {
        try
        {
            // transactional code...
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO Data(Code) VALUES('A-100');";
                cmd.Transaction = tran as SqlTransaction;
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
        catch(Exception ex)
        {
            tran.Rollback();
            throw;
        }
    }
}

11. Ambient Transaction

A transaction which automatically identifies a code block that needs to support a transaction without explicitly mentioning any transaction related things. An ambient transaction is not tied just to a database, any transaction aware provider can be used. TransactionScope implements an ambient transaction. If you see the use of TransactionScope, you will not find transaction related anything sent to any method or setting any property. A code block is automatically attached with the transaction if that code is in any TransactionScope. A WCF transaction is another example of a transaction aware provider. Any one can write a transaction aware provider like the WCF implementation.

12. Transaction Properties

There are four important properties for a transaction. We call them ACID properties. They are:

1. A-Atomic
2. C-Consistent
3. I-Isolation
4. D-Durable
1. Atomic: If all parts of the transaction individually succeed then data will be committed and the database will be changed. If any single part of a transaction fails then all parts of the transaction will fail and the database will remain unchanged. Part of the transaction might fail for various reasons like business rule violation, power failure, system crash, hardware failure, etc.
2. Consistent: Transaction will change the database from one valid state to another valid state following various database rules like various data integrity constraints (primary/unique key, check/not null constraint, referential integrity with valid reference, cascading rules ) etc.
3. Isolation: One transaction will be hidden from another transaction. In another way we can say, one a transaction will not affect an other transaction if both work concurrently.
4. Durability: After a transaction is successfully completed (committed to the database), changed data will not be lost in any situation like system failure, database crash, hardware failure, power failure etc.

13. Transaction Isolation Level

Now I will start explaining a very important thing directly related to transactions, and that is transaction isolation level. Why is it so important? First of all, I previously explained that isolation is an important transaction property. It describes each transaction is isolated from another and do not affect other concurrently executed transactions. How does a transaction management system achieve that important feature?

A Transaction Management System introduces a locking mechanism. With the help of this mechanism one transaction is isolated from another. The locking policy behaves differently based on the Isolation level set for each transaction. There are four very important isolation levels in .NET transaction scope. These are:

1. Serializable
2. Repeatable Read
3. Read Committed
4. Read UnComitted

Before I start explaining isolation levels, I need to explain data reading mechanizm inside a transaction. This data reading mechanism is very important to understand isolation levels properly.

• Dirty Read: One transaction reads changed data of anohter tranaction but that data is still not committed. You may take decission/action based on that data. A problem will arise when data is rolled-back later. If rollback happens then your decision/action will be wrong and it produces a bug in your application.
• Non Repeatable Read: A transaction reads the same data from same table multiple times. A problem will arise when for each read, data is different.
• Phantom Read: Suppose a transaction will read a table first and it finds 100 rows. A problem will arise when the same tranaction goes for another read and it finds 101 rows. The extra row is called a phantom row.

Now I will start explaining in short the important isolation levels:

1. Serializable: Highest level of isolation. It locks data exclusively when read and write occurs. It acquires range locks so that phantom rows are not created.
2. Repeatable Read: Second highest level of isolation. Same as serializable except it does not acquire range locks so phantom rows may be created.
3. Read Committed: It allow shared locks and read only committed data. That means never read changed data that are in the middle of any transaction.
4. Read Un-Committed: It is the lowest level of Isolation. It allows dirty read.

Now I will start explaining TransactionScope and its usage pattern:

14. TranactionScope Default Properties

It is very important to know about the default properties of the TransactionScope object. Why? Because many times we create and use this object without configuring anything.

Three very important properties are:

1. IsolationLevel
2. Timeout
3. TransactionScopeOptions

We create and use TransactionScope as follows:

Hide   Copy Code

using (var scope = new TransactionScope())
{
    //transctional code…
    scope.Complete();
} 

Here the TransactionScope object is created with the default constructor. We did not define any value forIsolationLevel, Timeout, and TransactionScopeOptions . So it gets default values for all three properties. So now we need to know what the default property values of these properties.

Property	Default Value	Available Options
IsolationLevel	Serializable	Serializable, Read Committed, Read Un Committed, Repeatable Read
Timeout	1 Minute	Maximum 10 Minutes
TransactionScopeOption	Required	Required, Required New, Suppress

1. Isolation Level: It defines the locking mechanism and policy to read data inside another transaction.
2. Timeout: How much time object will wait for a transaction to be completed. Never confuse it with theSqlCommand Timeout property. SqlCommand Timeout defines how much time the SqlCommand object will wait for a database operation (select/insert/update/delete) to be completed.
3. TransactionScopeOption: It is an enumeration. There are three options available in this enumeration:

No	Option	Description
1	Required	It is default value for TransactionScope. If any already exists any transaction then it will join with that transaciton otherwise create new one.
2	RequiredNew	When select this option a new transaction is always created. This transaction is independent with its outer transaction.
3	Suppress	When select this option, no transaction will be created. Even if it already

How to know the default values of these properties?

The System.Transactions assembly has two classes:

1. Transaction
2. TransactionManager

These classes will provide default values. Inside TransactionScope, if you run the following code, you will know the default values:

Hide   Copy Code

using (var scope = new System.Transactions.TransactionScope())
{
    IsolationLevel isolationLevel = Transaction.Current.IsolationLevel;
    TimeSpan defaultTimeout = TransactionManager.DefaultTimeout;
    TimeSpan maximumTimeout = TransactionManager.MaximumTimeout;
}

Is it possible to override the default property values?

Yes, you can. Suppose you want the default value to be 30 seconds and the maximum timeout value to be 20 minutes. If that is the requirement then you can do it using your web config.

Hide   Copy Code

<system.transactions>    
    <defaultSettings timeout="30"/>
    <machineSettings maxTimeout="1200"/>
</system.transactions>

For the machineSettings value, you need to update your machine.config in your server.

Hide   Copy Code

<section name="machineSettings" type="System.Transactions.Configuration.MachineSettingsSection,
System.Transactions,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089,
Custom=null"allowdefinition="MachineOnly"allowexedefinition="MachineToApplication" />

15. Transaction Isolation Level Selection

You need to have a proper knowledge when you use isolation levels. The following table will give you a very basic idea so that you can understand the basics and select the appropriate isolation level for your transaction scope.

Isolation Level	Suggestion
Serializable	It locks data exclusively at the time of read/write operations. For that reason, many times it may create a deadlock, and as a result you may get a timeout exception. You can use this isolation level for a highly secured transactional application like a financial application.
Repeatable Read	Same as Serializable except allows phantom rows. May use in a financial application or a heavily transactional application but need to know where phantom row creational scenarios are not there.
Read Committed	Most of the applications you can use it. SQL Server default isolation level is this.
Read Un-Committed	Applications with these have no need to support concurrent transactions.

Now I will explain with scenarios, how we can use TransactionScope:

16. Requirement-1

Create a transaction in which isolation level will be read committed and transaction timeout will be 5 minutes.

Implementation:

Hide   Copy Code

var option = new TransactionOptions();
option.IsolationLevel = IsolationLevel.ReadCommitted;
option.Timeout = TimeSpan.FromMinutes(5);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
    ExcuteSQL("CREATE TABLE MyNewTable(Id int);");                                        
    scope.Complete();
} 

First off, create TransactionOptions and set ReadCommitted and 5 minutes to its IsolationLevel andTimeout property, respectively.

Second, create a transactional block by creating a TransactionScope object with its parameterized constructor. In this constructor you will pass a TransactionOptions object which you created early and theTransactionScopeOption.Required value.

One important note, many times we are confused when using a DDL statement (Data Definition Language) in a transaction and a question arises, will it support DDL transaction? The answer is yes. You can use a DDL statement like create/alter/ drop statement in the transaction. You can even use a Truncate statement inside the transaction.

17. Requirement-2

We need to create a transaction where a database operation will be in my local database and another will be in a remote database.

Implementation:

Hide   Copy Code

using (var scope = new TransactionScope())
{
    UpdateLocalDatabase();
    UpdateRemoteDatabase();
    scope.Complete();
} 

There is no difference between a local or remote/distributed transaction implementation code in transactions. Previously I said that TransactionScope implements ambient type transaction. This means, it automatically marks code blocks that need to support a transaction, local or remote. But you may find an error when working with distributed transactions. The error message will be like:

"The partner transaction manager has disabled its support for remote/network transaction."

If you find that type of exception, you need to configure security settings, both your local and remote servers, for MSDTC, and make sure services are running.

To find the MSDTC configuration interface, you will go to:

ControlPanel > AdministritiveTools >ComponentServices > DistributedTransactionCoordinator > LocalDTC

Some options for the Security tab are described bellow:

Property Name	Description
Network DTC Access	If not selected, MSDTC will not allow any remote transaction
Allow Remote Clients	If it is checked, MSDTC will allow to coordinate remote clients for transaction.
Allow Remote Administration	Allow remote computers to access and configure these settings.
Allow Inbound	Allow computers to flow transaction to local computers. This option is needed where MSDTC is hosted for a resource manager like SQL Server.
Allow Outbound	Allow computers to flow transaction to remote computers. It is needed for a client computer where transaction is initiated.
Mutual Authentication	Local and Remote computers communicate with encrypted messages. They establish a secured connection with the Windows Domain Account for message communication.
Incoming Calling Authentication Required	If mutual authentication cannot be established but the incoming caller is authenticated then communication will be allowed. It supports only Windows 2003/XP ServicePack-2.
No Authentication Required	It allows any non-authenticated non-encrypted communication.
Enable XA Transaction	Allows different operating systems to communicate with MSDTC with XA Starndard.
DTC Logon Account	DTC Service running account. Default account is Network Service.

18. Distributed Transaction Performance

Distributed transactions are slower than local transactions. A two phase commit protocol is used for managing distributed transactions. A two phase commit protocol is nothing but an algorithm by which a distributed transaction is performed. There are three commit protocols that are mostly used:

1. Auto Commit: Transaction is committed automatically if all SQL statements are executed successfully or rolled-back if any of them fails to execute.
2. Two Phase Commit: Transaction waits before final commit for messages from all other parties involved in transaction. It locks resources before commit or rollback. For this reason it is called a blocking protocol. In terms of performance it is the reason it is much slower. It is a widely used protocol for managing distributed transactions.
3. Three Phase Commit: Transaction is finally committed if all nodes are agreed. It is a non-blocking protocol. In terms of performance it is faster than the two phase commit protocol. This protocol is complicated and more expensive but avoids some drawbacks in the two phase commit protocol.

19. Requirement-3

I want to create a transaction inside another transaction.

Implementation:

Hide   Shrink    Copy Code

string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
var option = new TransactionOptions
{
     IsolationLevel = IsolationLevel.ReadCommitted,
     Timeout = TimeSpan.FromSeconds(60)
};
using (var scopeOuter = new TransactionScope(TransactionScopeOption.Required, option))
{
    using (var conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText="INSERT INTO Data(Code, FirstName)VALUES('A-100','Mr.A')";
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    using (var scopeInner = new TransactionScope(TransactionScopeOption.Required, option))
    {
        using (var conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText="INSERT INTO Data(Code, FirstName) VALUES('B-100','Mr.B')";
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
        }
        scopeInner.Complete();
    }
    scopeOuter.Complete();
}

No problems in creating a transaction inside anohter (nested) transaction. You should define the behaviour or the inner transaction. This behaviour is dependent on the value of TransactionScopeOption. If you selectRequired as TransactionScopeOption, it will join its outer transaction. That means if the outer transaction is committed then the inner transaction will commit if the outer transaction is rolled back, then the inner transcation will be rolled back. If you select the RequiredNew value of TrasnactionScopeOption, a new transaction will be created and this transaction will independently be committed or rolled back. You must be clear about those concepts before working with nested transactions using TransactionScope.

20. Requirement-4

I want to call rollback explicitly from a transaction.

Implementation:

Hide   Copy Code

using (var scope = new TransactionScope())
{
    //either 1 of following lines will use
    Transaction.Current.Rollback();
    scope.Dispose();
    //if you comment the following line transaction will
    //automatically be rolled back
    //scope.Complete();
}

If you do not call the TransactionScope.Complete() method then the transaction will automatically be rolled back. If you need to explicitly call rollback for some scenarios, then you have two options:

1. Executing Transaction.Current.Rollback() will rollback the current transaction.
2. Executing TransactionScope.Dispose() will also rollback the current transaction.

Just one thing: remember that if you explicitly call Transaction.Rollback() orTranactionScope.Dispose() then you should not call the TransactionScope.Complete() method. If you do so then you will get an ObjectDisposeException.

"Cannot access a disposed object. Object name 'TransactionScope'"

21. Requirement-5

I want to create a file/folder dynamically inside a transaction scope. If my transaction is rolled back then I want that created file/folder to be removed automatically, like a database row.

Implementation:

Hide   Copy Code

string newDirectory = @"D:\TestDirectory";
string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (var scope = new TransactionScope())
{
    using (var conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "Insert into data(Code) values ('A001');";
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    Directory.CreateDirectory(newDirectory);
    File.Create(@"D:\NewFile.txt").Dispose();
    scope.Dispose();
} 

TranactionScope is not limited for only databases. It will support other data sources like FileSystem, MSMQ, etc. But you need more work to support TranactionScope. First of all what I show in the above code block will not work. Why? Because that directory creation and file creation will not be marked for transaction by default. Then what do we need to do?

Hide   Copy Code

public interface IEnlistmentNotification
{       
    void Commit(Enlistment enlistment);       
    void InDoubt(Enlistment enlistment);      
    void Prepare(PreparingEnlistment preparingEnlistment);        
    void Rollback(Enlistment enlistment);
} 

The System.Transactions namespace has an interface named IEnlistmentNotification. If I want my component/service to be transaction aware then I need to implement that interface. The following code will show a very simple and straightforward way to implement this:

Hide   Shrink    Copy Code

public class DirectoryCreator : IEnlistmentNotification
{
    public string _directoryName; 
    private bool _isCommitSucceed = false;
    public DirectoryCreator(string directoryName)
    {
        _directoryName = directoryName;
        Transaction.Current.EnlistVolatile(this, EnlistmentOptions.None);
    }
    public void Commit(Enlistment enlistment)
    {
        Directory.CreateDirectory(_directoryName);
        _isCommitSucceed = true;
        enlistment.Done();
    }
    public void InDoubt(Enlistment enlistment)
    {
        enlistment.Done();
    }
    public void Prepare(PreparingEnlistment preparingEnlistment)
    {
        preparingEnlistment.Prepared();
    }
    public void Rollback(Enlistment enlistment)
    {
        if (_isCommitSucceed))
            Directory.Delete(_directoryName);
        enlistment.Done();
    }
} 

The above class will create a directory (folder) and this component is transaction aware. We can use this class with any TranactionScope and if TranactionScope is committed the directory will be created, otherwise it will be deleted (if already created). I show here just the diretory creation, if you want you can create a class/component for file creation. Now, how to use this class in the transactions scope?

Hide   Copy Code

string newDirectory = @"D:\TestDirectory";
string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (var scope = new TransactionScope())
{
    using (var conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "Insert into data(Code) values ('A001');";
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    var creator = new DirectoryCreator(newDirectory);
    Transaction.Current.Rollback();
    //scope.Complete();
}

Now, it will work!

Transactional NTFS(TxF) .NET is a open source project. You can use this library for creating/writing/coping file/directory inside transactionscope and it will support transaction automatically.

• You first need to download component from http://txfnet.codeplex.com
• Add that component as reference to your project.
• Use component api to your transactional block.

Txf API usage code sample:

Hide   Copy Code

using (var ts = new System.Transactions.TransactionScope())
{
    using (var conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "Insert into data(Code) values ('A001');";
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    TxF.Directory.CreateDirectory("d:\\xyz", true);
    TxF.File.CreateFile("D:\\abc.txt", File.CreationDisposition.OpensFileOrCreate);
    ts.Complete();
}

TxF component supports:

• Create/Delete Directory
• Create/Delete File
• Read/Write File
• Copy File

22. Points of Interest

Transaction Management is actually a huge subject. It is a very complex subject too, specially distributed transaction. I tried my level best to present it as simple as possible. If you want to get all transaction related knowledge then you should study more on that. I suggest you read research papers on transactions, specially distributed transactions.

You can also explore more regarding transaction aware service/components. I showed here a very simple way to implement them. But in real life you may face difficult scenarios. So you need to prepare for that. In the near future Microsoft may add transaction aware components like dictionary/filesystem/directory service, etc. If it that happens then developers' life will be more easier.

What is a Transaction?

Frequently in a database application you come across a situation where you need to execute two or more SQL commands in such a way that if any one of the statements fails, then no other statement will be able to change the database. The classic example of this is transferring money from a bank account into another bank account:

Hide   Copy Code

UPDATE Accounts SET Balance = Balance – 10 WHERE Customer = 1;
UPDATE Accounts SET Balance = Balance + 10 WHERE Customer = 2;

If the first SQL statement was to execute and the second SQL statement was to fail, then ten dollars would be removed from the first customer’s account but will never be added to the second customer’s account. This would, naturally, be bad – ten dollars has just disappeared entirely.

One way to deal with this would be to handle the situation in your database access code. You could do this by catching System.Data.SqlClient.SqlException when you do your database access. The situation is not as easy as it first appears, however. The SQL statements could have failed in a number of places:

1. Before the first SQL statement is executed.
2. After the first SQL statement is executed.
3. After the second SQL statement is executed.

This means that you have to determine what the balances of both the accounts were before the transfer and, after the exception was thrown, determine which account’s current balance doesn’t match the starting balance, and execute an UPDATE statement. This can, potentially, be a lot of code – especially if your example is non-trivial.

The situation is made even worse by the fact that SQL Server is a multi-user environment. At any given point of time, other users could be accessing the Accounts table – and if they access that table between your statement failing and your corrections to the table, they could very well access invalid data. This would be extraordinarily bad, and could lead to immensely hard to find bugs in your code. (bugs relating to data synchronization across several threads or processes are so difficult to find, that they have their own name: ‘Heisenbugs’)

Modern database theory postulates that in a perfect transaction world, a database would have a series of properties known as ACID. These properties are:

• Atomic – All statements in a group must execute, or no statement in a group must execute.
• Consistent – This follows naturally from atomic – a group of SQL statements must take the database from a known starting state to a known ending state. If the statements execute, the database must be at the known ending state. If the statements fail, the database must be at the known starting state.
• Isolated – A group of statements must execute independently from any other statement groups being executed at the same time. If this wasn’t the case, it would be impossible for statement groups to be consistent – the known ending state could be altered by a code you have no control over or knowledge of. This is one of those concepts that are great in theory, but total isolation has important performance implications in the real world. More on how SQL Server implements this is explained later.
• Durable – Once the group of SQL statements execute, the results need to be stored in a permanent media – if the database crashes right after a group of SQL statements execute, it should be possible to restore the database state to the point after the last transaction committed.

In SQL Server, ACID-ness is provided by the concept of transactions. Simply put, a transaction is a way to group SQL statements together so that, when executed, the transaction obeys the ACID principles. A transaction is enabled on a single connection to the database (over the lifetime of a SqlConnection object, in .NET parlance) and will apply to all commands executed over that connection until the transaction ends. Once you have a transaction, there are two things you can do with it. You can either commit the transaction to the database at the end of the transaction, or you can abandon the transaction and rollback the changes made in the transaction.

In terms of Transact-SQL, there are three importance commands to manage a transaction. BEGIN TRANSACTIONwill begin a transaction, COMMIT TRANSACTION will commit the transaction to the database, and ROLLBACK TRANSACTION will roll the transaction back. These statements can actually be a little more complicated – feel free to refer to the MSDN documentation regarding these statements.

.NET Transactions

Within .NET, transactions are managed with the System.Data.SqlClient.SqlTransaction class. Again, a transaction exists over a SqlConnection object – and thus all the SqlCommand objects you create using that connection. Let's look at a quick example:

Hide   Shrink    Copy Code

public class TransactionDemo
{
   public TransactionDemo()
   {

   }

   [STAThread]
   public static void Main() 
   {
      Demo1();
   }

   private static void Demo1() 
   {
      SqlConnection db = new SqlConnection("connstringhere");
      SqlTransaction transaction;

      db.Open();
      transaction = db.BeginTransaction();
      try 
      {
         new SqlCommand("INSERT INTO TransactionDemo " +
            "(Text) VALUES ('Row1');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO TransactionDemo " +
            "(Text) VALUES ('Row2');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO CrashMeNow VALUES " +
            "('Die', 'Die', 'Die');", db, transaction)
            .ExecuteNonQuery();
         transaction.Commit();
      } 
      catch (SqlException sqlError) 
      {
         transaction.Rollback();
      }
      db.Close();
   }
}

As you can see from this example, we first open a connection to the SQL database. We then call theBeginTransaction method on the connection, keeping a reference to the object that it returns. At this point, the connection is bound to the SqlTransaction object that was returned. This means that any SqlCommandexecuted on that connection will be within the transaction. You can see, within the try block, we create and execute three SqlCommand objects. You’ll notice, though, that in this case we’re using the strings,SqlConnection, SqlTransaction, overload of the SqlCommand constructor. This is because the SqlCommandobject requires passing in the transaction bound to a connection – failing to do so will cause an exception to be thrown. This is, in my opinion, a weakness of the model – since a transaction is bound on a per-connection basis, and a SqlCommand object should be able to simply pull the SqlTransaction object out of the suppliedSqlConnection.

In the example above, the first two SqlCommand executes are perfectly valid – TransactionDemo exists in the database. The CrashMeNow table, however, does not. Since the table does not exist, a SqlException object will be thrown on ExecuteNonQuery object. It is important to realize that having a transaction "does not replace the standard exception handling mechanism". If you think your statements might not execute, you have to catchSqlException and, within your catch block, rollback the transaction.

There are essentially, two operations you can use on the SqlTransaction object. Rollback will cancel your transaction, undoing all the changes that have been made. Commit will cause the transaction to be written to the database permanently. Either case will end the transaction.

If you execute the code above and look at the TransactionDemo table, you’ll see that no rows have been added – the transaction was rolled back after the exception was thrown. If you remove the offending line of SQL, though, and run the program and look again, you’ll see that two rows have been added. That is, essentially, Transactions in action.

Advanced Transactions – isolation levels

This is not, though, the limit of transactions. As I stated earlier in my description of ACID properties, transactions don’t necessarily meet the strictest definition of Isolated. This is because the isolation level of a transaction is configurable by you, when you create the transaction.

Why would you want to do this? Performance. While full isolation is wonderful in theory, so was communism. In reality, exclusively locking a set of rows while a transaction works on them may be unfeasible due to performance – you don’t necessarily want to stop every reader in the system from reading a table because your transaction locked the whole thing.

To alleviate this concern .NET (through SQL Server), provides the ability to specify isolation levels when you create a transaction. Doing this simply requires supplying a System.Data.IsolationLevel value to theBeginTransaction method. The available values for SQL Server 2000 are:

1. ReadUncommitted – This is, essentially, no isolation. Anyone can read the data placed in a table or updated immediately after the SQL statement causes the change – no commit is required. This could lead to a process having out-of-date data: it may be using a version of the data that was then rolled back out of the table!
2. ReadCommitted – This is slightly more isolated. In this case, a transaction can only read data from the table that has already been committed. When a transaction wants to update data, it acquires a shared lock on that data and (if successful getting the lock) updates the data. Transactions outside of that transaction cannot update the data in that table until the locking transaction commits. This is only slightly more isolated, however: a SQL statement executed twice within a transaction could return a different result-set if a second transaction changes and commits the data the SQL statement executes on between the two statements. This is the default isolation level for SqlTransaction.
3. RepeatableRead – Slowly getting more isolated. In this case, a shared lock is applied on all data queried within a transaction. This means that no other transaction can alter the data used in your transaction. This prevents the case where data you had queried once changes on subsequent queries. It does not, though, prevent the case where rows are added to the table that may be returned in subsequent queries.
4. Serializable – Locks are placed on ranges of the tables you are using, preventing other users from changing your data or adding new rows underneath you. This is the most isolated isolation level, but it does come with the drawback of locking more data than your transaction may strictly need.

In SQL Server 2005, a new isolation level will be added: snapshot isolation. In snapshot isolation, rows are versioned once they are accessed in a transaction. This essentially means that once a transaction accesses a set of values, they are guaranteed to remain the same until you commit or rollback the transaction. Other transactions starting in the middle of the first will get a ‘copy’ of the original database to operate on. Before any transaction commits, though, SQL Server will test to ensure that the original data they were operating on is the same as the current data in the database. If this is the case, the transaction will commit. Otherwise, the transaction will be rolled back and the user will have to try the batch once again.

Conclusion

Transactions are useful for several other things. First, they provide a way to rollback a group of SQL statements if a single one should. Remember that failure can mean more than just an error being returned. A failure can also be logically related - in the example above, perhaps the account being transferred from doesn’t have enough money to do the deposit. In that case, you can roll back the transaction when you discover that fact. Secondly, they provide a way to isolate the data your transaction is working on so that you don’t have to worry about surprises. In all cases, though, you should examine what isolation level you really need and be aware of the performance implications of all of them.

Introduction

This article is a beginner's tutorial for understanding what are transactions and how can transactions be implemented using .Net framework and ADO.NET for any ASP.NET web application or any other database driven application.

Background 

Transaction word, when used in normal context mean exchange of goods, products or money in return for something of equal value. What makes this exchange a transaction is the fact that the total amount of goods or money remains the same i.e. it doesn't get increased or decreased and the reliability i.e. if one party is giving something then other party will receive that much quantity (no less, no more).
Following the same lines, when we talk about transactions in database operations, When we perform some database operations in such a way that either all the database operations are successful or all of them fail. This would result in the amount information being same once the transaction is complete or it fails.
To illustrate the above process, let say I have two account holders, one person is trying to transfer some money to other person. From the database perspective this operation consist of two sub-operations i.e.

1. Debiting the first account by specified amount.
2. Secondly, crediting the second account with required amount.

Now from a technical perspective, if the first operation is successful but second one fails the result would be that the first persons account will be debited but second one will not be credited i.e. we loose the amount of information. The other way round will in fact increase the amount ion second account without even debiting the first amount.
So the bottom-line here is that we need either both of them to be successful to both of them should fail. Success of any one operation will result in inconsistent results and thus even if one operation fails we need to rollback what we did in the other operation. This is precisely where transaction are useful.
Let us now look at some technical details associated with transactions and transactions in .Net framework. We will then see how we can implement transactions in .NET.

Using the code

By definition a transaction must be Atomic, Consistent, Isolated and Durable. What does we mean by all these terms

Properties of Transaction

• Atomic: Atomic means that all the statements (SQL statement or operations) that are a part of the transaction should work as atomic operation i.e. either all are successful or all should fail.
• Consistent: This means that in case the atomic transaction success, the database should be in a state that reflect changes. If the transaction fails then database should be exactly like it was when the transaction started.
• Isolated: If more than one transactions are in process then each of these transactions should work independently and should not effect the other transactions.
• Durable: Durability means that once the transaction is committed, the changes should be permanent i.e. these changes will get saved in database and should persist no matter what(like power failure or something).

Description of Sample code

Now to illustrate how can transactions be implemented, We will work on a small application that contains a single table database. This table contains the account id and the amount present in the account. The application will facilitate transfer of amount of one account to another. Since there are two operations involved we will see how we can use transactions to perform these operations.
The sample DB table looks like:

The UI will look like:

And we will implement three versions of this page to see three different ways. We will see how can handle transaction at database level using SQL transactions, how to implement transactions using ADO.NET transaction object and finally we will see how to implement transactions using TransactionScope object.
Note: The code is written to elaborate the transaction functionality only, it is not as per the coding standards i.e it is vulnerable to SQL injection. It should not be taken as code that could go in production. It is just the sample code and has a lot of scope for improvement.

Creating and Implementing Transactions in SQL

The transactions can also be handled at SQL level. The SQL construct for handling the transactions is like follows:

Hide   Copy Code

BEGIN TRY
    BEGIN TRANSACTION
        -- update first account and debit from it
        -- update second account and credit in it
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

So if something goes wrong, the SQL itself will take care of rolling back the transaction.
So for our sample application we can write the following code to get this code to work with SQL transactions in place.

Hide   Shrink    Copy Code

private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{        
    SqlConnection con = null;

    // they will be used to decide whether to commit or rollback the transaction
    bool result = false;
   
    string updateCommand = @"BEGIN TRY
                                BEGIN TRANSACTION
                                    update Account set Amount = Amount - {0} where ID = {1}
                                    update Account set Amount = Amount + {2} where ID = {3}
                                COMMIT TRANSACTION
                            END TRY
                            BEGIN CATCH
                                ROLLBACK TRANSACTION
                            END CATCH";

    try
    {
        con = new SqlConnection(CONNECTION_STRING);
        con.Open();            

        // Let us do a debit first
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = string.Format(updateCommand,                    
                amount, debitAccountID,
                amount, creditAccountID);

            // check if 2 records are effected or not
            result = cmd.ExecuteNonQuery() == 2;
        }
    }
    catch
    {
        // do application specific cleanup or show message to user about the problem
    }
    finally
    {
        con.Close();
    }
}

Note: This sample code just shows the code snippet handling the transaction and database operation, to get the full understanding please refer to the sample code.

Creating and Implementing Transactions using ADO.NET DbTransaction Object

Now having the transactions in SQL level is perfect solution if all the operations are being done in one place. I can create a stored procedure which will take care of all the transactions  But what if all the operations are taking place in separate classes or even separate assemblies. If that is the case need to use the ADO.NET transaction to handle transactions in code.
The other way to handle the transaction is from the code itself using ADO.NET DbTransaction object. To do that following steps should be taken:

1. Create the Connection.
2. Create the Transaction.
3. Create the commands for all the operations that are within transaction.
4. Open the connection.
5. Begin Transaction.
6. Associate all the commands with the above transaction object.
7. Execute commands.
8. Check for the commands status separately.
9. If any of the command fails, Roll back the transaction.
10. If all Commands are successful, Commit the transaction.

To illustrate the above mentioned process, lets write some code to perform the transaction in our sample application.

Hide   Shrink    Copy Code

private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{
    SqlTransaction transaction = null;
    SqlConnection con = null;

    // they will be used to decide whether to commit or rollback the transaction
    bool debitResult = false;
    bool creditResult = false;

    try
    {
        con = new SqlConnection(CONNECTION_STRING);
        con.Open();

        // lets begin a transaction here
        transaction = con.BeginTransaction();

        // Let us do a debit first
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = string.Format(
                "update Account set Amount = Amount - {0} where ID = {1}",
                amount, debitAccountID);

            // assosiate this command with transaction
            cmd.Transaction = transaction;

            debitResult = cmd.ExecuteNonQuery() == 1;
        }

        // A dummy throw just to check whether the transaction are working or not
        //throw new Exception("Let see..."); // uncomment this line to see the transaction in action

        // And now do a credit
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = string.Format(
                "update Account set Amount = Amount + {0} where ID = {1}",
                amount, creditAccountID);

            // assosiate this command with transaction
            cmd.Transaction = transaction;

            creditResult = cmd.ExecuteNonQuery() == 1;
        }

        if (debitResult && creditResult)
        {
            transaction.Commit();
        }
    }
    catch
    {
        transaction.Rollback();            
    }
    finally
    {
        con.Close();
    }
}

This code block makes sure that either all the operations associated with the transactions succeed or none of them will.
Note: This sample code just shows the code snippet handling the transaction and database operation, to get the full understanding please refer to the sample code.

Creating and Implementing Transactions using TransactionScope Object

Creating and using transactions using ADO.NET transaction is fine as long as we do either a commit or rollback on the transaction object. If we forget to so do this and leave the code then it will cause some problems.
To solve such problems there is one more way of handling the transaction i.e. using TransactionScope object.TransactionScope if more of a syntactic sugar when dealing with transactions. Also it makes sure that if the transaction is not committed and the code goes out of scope, the transaction will get rolled back.
To use the TransactionScope object to handle the transactions following needs to be done.

1. Create a TransactionScope object within a using block.
2. Create the connection inside this TransactionScope using block.
3. Create all the commands inside this.
4. Perform all the operations using Commands.
5. If all the operations are successful call the Complete function on TransactionScope object.
6. If any of the command fail, let the control go out of scope and the transaction will be rolled back.

To illustrate this process let us try to re-implement the functionality in our sample application usingTransactionScope object.

Hide   Shrink    Copy Code

private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{
    // they will be used to decide whether to commit or rollback the transaction
    bool debitResult = false;
    bool creditResult = false;

    try
    {
        using (TransactionScope ts = new TransactionScope())
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();

                // Let us do a debit first
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(
                        "update Account set Amount = Amount - {0} where ID = {1}",
                        amount, debitAccountID);

                    debitResult = cmd.ExecuteNonQuery() == 1;
                }

                // A dummy throw just to check whether the transaction are working or not
                throw new Exception("Let see..."); // uncomment this line to see the transaction in action

                // And now do a credit
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(
                        "update Account set Amount = Amount + {0} where ID = {1}",
                        amount, creditAccountID);

                    creditResult = cmd.ExecuteNonQuery() == 1;
                }                                       

                if (debitResult && creditResult)
                {
                    // To commit the transaction 
                    ts.Complete();
                }
            }
        }
    }
    catch
    {
        // the transaction scope will take care of rolling back
    }  
}

This code block makes sure that either all the operations within the scope of TransactionScope will succeed or none of them will.
Note: This sample code just shows the code snippet handling the transaction and database operation, to get the full understanding please refer to the sample code.
To give more control to the developer, along with the scoped control of TrasanctionScope, there are few options that can be associated with the TransactionScope

• Required: If within current scope another TransactionScope is already instantiated, thisTransactionScope object will join that one.
• RequiresNew: Even if within current scope another TransactionScope is already instantiated, thisTransactionScope object will create a new transaction that will work within its scope.
• Supress: Even if within current scope another TransactionScope is already instantiated, thisTransactionScope object now keep all the operations within its scope out of the existing transaction.

These options can be passed in the Constructor of the TransactionScope as:

Hide   Copy Code

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
{
    // Code goes here
}

A Note on Distributed Transactions 

If a transaction span across multiple processes then it is a Distributed transaction i.e. if I need to perform an operation in SqlServer and other in Oracle ans I have a transaction associated with it then it will be a distributed transaction.
The reason this is important is because of two things. The Isolation property of transaction will be ensured by theLTM(Lightweight Transaction Manager) if it is not a distributed transaction. But of it is a distributed transaction then Distributed Transaction Controller(DTC) will take over the control.
When we use TransactionScope class, if the transaction starts with one process LTM will keep it going but if another operation in some other process is done the DTC takes over and the transaction will then be automatically promoted to distributed transaction. So TransactionScope class can be used to create promotable transactions with ease.
But even for non distributed transactions, transactionScope object should only be used with SqlServer 2005or later products because the earlier SqlServer products were not aware of this LTM and DTC. so for the earlier products ADO.NET DbTransaction object is the perfect way to handle the transactions from code.