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
                }
            }
        }
    }
}