Simple example of System.Transaction code from ADO.NET 2.0 to SQL Server 2005


The namespace System.Transaction is new in the .NET Framework 2.0. It is the next step in the journey which took us from MTS to COM+ to Enterprise Services. It is a very simple, comprehensive way of working with transactions. At its heart it understands when a lightweight transaction is sufficient – and when it need to promote to two phase commit using the DTC.

Embarassingly (given I did a LOT of MTS in my day) I have only just got round to writing my first sample. It is very noddy – but I like to start simple and build 🙂

You will need to reference “System.Transactions.dll” when you compile. I also created a very simple 2 column table ‘people’. But just change the sql to work with any table you have “lying around” 🙂 Enjoy.

using System;
using System.Transactions;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleTxTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // Start a transaction.
            // Single resource manager (RM) of SQL Server so we will get a lightweight transaction.
            // If not SQL Server then explicit enlistment may be necessary
            // If more than one RM then tranaction will promote to being handled by
            // the Distributed Transaction Coordinator

            using (TransactionScope scope = new TransactionScope())
            {
                Console.WriteLine(“ISO LEVEL ” + Transaction.Current.IsolationLevel);
                Console.WriteLine(“Type ” + Transaction.Current.GetType());
                Console.WriteLine(“Status ” + Transaction.Current.Status);
                Console.WriteLine(“Identifier ” + Transaction.Current.TransactionTraceId.TransactionIdentifier);
                //Beta 1 bug – promotes to DTC by interrogating the Identifier poperty. Check in COM+ explorer
                Console.WriteLine(“Identifier ” + Transaction.Current.Identifier);

                // Insert a record against a connection
                // SQL provider will auto enlist in the transaction if one exists – which is the case

                using (SqlConnection conn = new SqlConnection(“server=localhost;database=pubs;Integrated Security=true”))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = “INSERT INTO people VALUES (‘eric’, 30)”;
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
 
               // Insert a second record against a connection inside same transaction
                // SQL provider will auto enlist in the transaction if one exists – which is the case

                using (SqlConnection conn = new SqlConnection(“server=localhost;database=pubs;Integrated Security=true”))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = “INSERT INTO people VALUES (‘Andrew’, 21)”;
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                // Commit or rollback?
                Console.WriteLine(“Commit?”);
                string x = Console.ReadLine();
                if (x == “yes”)
                {
                    scope.Consistent = true;
//Beta 2 this should be .Complete
                }
            }
        }
    }
}