Transaction Scope in DotNet using C#.

S.Vinothkumar · #1 (**permalink**) 09-01-2007, 12:49 AM

Hi Friends,

From this thread I'm going to explain how to use Transaction Scope in C# and what the purpose of Transaction Scope.

Learn with my threads and share your knowledge here....

S.Vinothkumar · #2 (**permalink**) 09-01-2007, 12:53 AM

System.Transactions

The .NET 2.0 Framework introduces a new namespace called System.Transactions that makes transactional programming easier when dealing with ADO.NET, Sql Server, MSMQ, and the Microsoft Distributed Transaction Coordinator (MSDTC).

I came across some code that was using System.Transactions to handle local transactions connecting to Sql Server. The code was essentially like this:

Code:

using (TransactionScope scope =
    new TransactionScope())
{
    using (SqlConnection connection =
        new SqlConnection(connectionString))
    {
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "Insert....";
        
        SqlCommand command2 = connection.CreateCommand();
        command.CommandText = "Update....";

        connection.Open();
        command.ExecuteNonQuery();
        command2.ExecuteNonQuery():
        connection.Close();
    }

    scope.Complete();
}

Certainly you can use System.Transactions for local transactions, but it backfires on you with Sql Server .

If you are using Sql Server 2000, the local transaction automatically becomes promoted to a distributed transaction managed by MSDTC, which is unecessary in this case and will cause a performance hit. Sql Server 2000 does not support “promotable transactions,“ which is needed to take advantage of the Lightweight Transaction Manager.

I'm still using SqlTransaction for all local database transactions, although I certainly love the code simplicity of using System.Transactions and TransactionScope.

S.Vinothkumar · #3 (**permalink**) 09-01-2007, 01:05 AM

Anyway, I thought I would share some basic information about SQL Server Transactions from this thread for those who are new to ADO.NET.

This thread does a good job of providing an overview of transactions and a basic template for how to execute local transactions in code.

Here is the basic template for an ADO.NET 2.0 Transaction:

Code:

using (SqlConnection connection =
            new SqlConnection(connectionString))
{
    SqlCommand command = connection.CreateCommand();
    SqlTransaction transaction = null;
    
    try
    {
        // BeginTransaction() Requires Open Connection
        connection.Open();
        
        transaction = connection.BeginTransaction();
        
        // Assign Transaction to Command
        command.Transaction = transaction;
        
        // Execute 1st Command
        command.CommandText = "Insert ...";
        command.ExecuteNonQuery();
        
        // Execute 2nd Command
        command.CommandText = "Update...";
        command.ExecuteNonQuery();
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
    finally
    {
        connection.Close();
    }
}

S.Vinothkumar · #4 (**permalink**) 09-01-2007, 01:19 AM

A c# using statement wraps up the connection, because SqlConnection implements IDisposable. The using statement makes sure that Dispose() gets called on the connection object so it can free up any unmanaged resources.

Before you can begin a transaction, you must first open the connection. You begin your transaction and then assign any newly created command objects to that transaction and perform queries as necessary. Commit the transaction. If an error occurs, Rollback the transaction in a catch statement to void out any changes and then rethrow the error so that the application can deal with it accordingly.

The connection is properly closed in the finally statement, which gets called no matter what, and any unmanaged resources are disposed when the using statement calls Dispose() on the connection.

S.Vinothkumar · #5 (**permalink**) 09-01-2007, 01:21 AM

The above template could actually implement a second c# using statement around command, because SqlCommand also implements IDisposable. I don't know that it is really necessary, however. I just like to see using statements around anything that implements IDisposable:

Code:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command =
            connection.CreateCommand())
    {
        SqlTransaction transaction = null;
        
        try
        {
            // BeginTransaction() Requires Open Connection
            connection.Open();
            
            transaction = connection.BeginTransaction();
            
            // Assign Transaction to Command
            command.Transaction = transaction;
            
            // Execute 1st Command
            command.CommandText = "Insert ...";
            command.ExecuteNonQuery();
            
            // Execute 2nd Command
            command.CommandText = "Update...";
            command.ExecuteNonQuery();
            
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
}

S.Vinothkumar · #6 (**permalink**) 09-01-2007, 04:00 AM

SQL Server Transaction Savepoints

In the last post I talked about the basics of SQL Server Transactions. There is also a small section on SQL Server Savepoints, which is the ability to mark points within the life of your transaction in the case that you may only want to rollback the current transaction to a specific point as opposed to the very beginning.

Savepoints give you the flexibility to only roll back and commit portions of a SQL Server Transaction as opposed to rolling back the entire transaction. One may not need the functionality often, but it is nice to know savepoints exist when the time comes.

Mramesh · #7 (**permalink**) 09-01-2007, 04:03 AM

Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction.

The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement.

S.Vinothkumar · #8 (**permalink**) 09-02-2007, 10:28 PM

If you look at a snippet of the SQL Server Transaction Template presented in my previous post, you can notice the transaction.Rollback() statement in the catch block that rolls back the entire transaction when an unexpected exception is thrown,

Code:

try
{
    // ...
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}
finally
{
    connection.Close();
}

I'm going to explain about those two methods in my upcoming posts....

S.Vinothkumar · #9 (**permalink**) 09-02-2007, 10:30 PM

Yes...

Here is SqlTransaction.Rollback and SqlTransaction.Save Methods

The SqlTransaction Rollback method has an overload that will accept the name of a Savepoint if you would rather just roll the transaction back to a specific Savepoint as opposed to rolling back the entire transaction:

Code:

transaction.Rollback("FirstUpdate");

As you would expect, the SqlTransaction Class also has a method, called Save, that allows you to create a Savepoint in the life of the transaction:

Code:

transaction.Save("FirstUpdate");

S.Vinothkumar · #10 (**permalink**) 09-02-2007, 10:32 PM

Example of SqlTransaction.Rollback and SqlTransaction.Save

Using both the Rollback and Save methods in SqlTransaction to partially rollback a transaction would look something like below. Of course, rather than just rolling back for kicks, a real application would have some business logic for doing so:

Code:

using (SqlConnection connection =
        new SqlConnection(connectionString))
{
    using (SqlCommand command =
        connection.CreateCommand())
    {
        connection.Open();
            
        SqlTransaction transaction =
                connection.BeginTransaction();
        
        command.Transaction = transaction;
        
        try
        {
            command.CommandText = "Update...";
            command.ExecuteNonQuery();
            
            // Create a Savepoint
            transaction.Save("FirstUpdate");
            
            command.CommandText = "Insert...";
            command.ExecuteNonQuery();
            
            // Rollback to FirstUpdate
            transaction.Rollback("FirstUpdate");
            
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
}

Mramesh · #11 (**permalink**) 09-04-2007, 12:02 AM

Hi all,

I would like to use TransactionScope within the Business Layer of
my web application.
Is any Idea?

S.Vinothkumar · #12 (**permalink**) 09-04-2007, 12:13 AM

Hi,

it should not be used in a shared web hosting environment because the server may get
re-configured.

I don't see how the server becoming reconfigured affects the use of
TransactionScope.

You want to use the TransactionScope class when you want the code
encapsulated in the scope of the instance (it's meant to be used in a using
statement) is to run in a transactional context.

Look at the documentation for the TransactionScope class on how to use
it.

S.Vinothkumar · #13 (**permalink**) 09-04-2007, 12:19 AM

Actually, TransactionScope is supposed to work even if DTC is
disabled/uninstalled, as long as you don't do anything that causes
promotion.

only MS SqlServer 2005 supports promotable transactions.

krishnakumar · #14 (**permalink**) 09-04-2007, 12:42 AM

Hi,
The Following Notes will clear u...

Distributed Transactions:

A distributed transaction spans multiple data sources. Distributed transactions enable you to incorporate several distinct operations, which occur on different systems, into an atomic action that either succeeds or fails completely.

Properties of a transaction:

* Atomicity:
A transaction is an atomic unit of work, an indivisible set of operations. The operations that you perform within a transaction usually share a common purpose, and are interdependent. Either all of the operations in a transaction should complete, or none of them should. Atomicity helps to avoid data inconsistencies by eliminating the chance of processing a subset of operations.
* Consistency:
A transaction preserves the consistency of data. A transaction transforms one consistent state of data into another consistent state of data. Some of the responsibility for maintaining consistency falls to the application developer, who must ensure that the application enforces all known integrity constraints.
* Isolation:
A transaction is a unit of isolation. Isolation requires that each transaction appear to be the only transaction manipulating the data store, even though other transactions might be running concurrently. Transactions attain the highest level of isolation when they have the ability to be serialized; at this level of isolation, the results obtained from a set of concurrent transactions are identical to the results obtained by running each transaction serially. A high degree of isolation can limit the number of concurrent transactions, and consequently, applications often reduce the isolation level in exchange for better throughput.
* Durability:
A transaction is the unit of recovery for a set of operations. If a transaction succeeds, the system guarantees that its updates will persist, even if the computer crashes immediately after the application performs a commit operation. Specialized logging allows the system restart procedure to complete unfinished operations so that the transaction is durable.

S.Vinothkumar · #15 (**permalink**) 09-04-2007, 12:48 AM

TransactionScope Class

This class is new in the .NET Framework version 2.0.

Makes a code block transactional. This class cannot be inherited.

For more see TransactionScope Class in msdn.....

H2o · #16 (**permalink**) 09-04-2007, 01:28 AM

Hi,

how to Create Distributed Transaction?

Can u explain programmatically?

amansundar · #17 (**permalink**) 09-04-2007, 01:33 AM

Hi all,

I'm getting the following error when I use the TransactionScope in my coding....

Error: Communication with the underlying transaction manager has failed

I don't know where I'm doing mistake.....

This is where the error happens...

public void insertDB()
{
SqlConnection Conn = new SqlConnection(connectionString);
Conn.Open();
SqlTransaction sqltran = Conn.BeginTransaction();
SqlCommand cmd = new SqlCommand(statementString, Conn, sqltran);
cmd.ExecuteNonQuery();
sqltran.Commit();
}

I'm calling the insert here ....
private void InsertCustomerToDB(object sender, EventArgs e)
{
using (TransactionScope scope = new TransactionScope())
{
int BillingID = insertDB();
int DeliveryID = insertDB();

int CustomerID = DBTrip.CustomerAdapter.InsertContact(CustomerInfo) ;
}
}

raja · #18 (**permalink**) 09-04-2007, 03:32 AM

Hi AMan,

I found the solution for ur probs....

In ur calling method you have to open the conneciion by the transaction scope and keep it open while prforming the different insert methods.

Another thing you have to do is pass the Conn to the method where you will be using it.

in the method you will have to accept the passed connection with InsertContact(SqlConnection Conn)

Change ur code as follows,

Code:

private void InsertCustomerToDB(object sender, EventArgs e)

{

using (TransactionScope scope = new TransactionScope())

{
 Conn.Open();

 int BillingID = insertDB();

 int DeliveryID = insertDB();
 
 int CustomerID = DBTrip.CustomerAdapter.InsertContact(CustomerInfo);
 
 Conn.Close();

 scope.Complete();

}

}

amansundar · #19 (**permalink**) 09-04-2007, 04:17 AM

its working fine....thank u for your response raja

mobilegeek · #20 (**permalink**) 09-04-2007, 06:19 AM

When appling Transaction scope to handle transaction, it seems that the transaction manager will make use Distrbuted Trsnsaction Coordinator to manage the transaction.

However, with using DTC, will it be more expensive than using ADO.Net transaction Object ? And also, will the performance affect by the network traffic?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
scope variable	itbarota	HTML, CSS and Javascript Coding Techniques	1	11-13-2007 12:29 AM
What is Ngen.exe in DotNet?	leoraja8