using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Transactions;


public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void UseSqlTransaction()
  {
    // Example 1: start transaction using the API
    SqlConnection conn = new SqlConnection("context connection=true");
    conn.Open();
    SqlTransaction tx = conn.BeginTransaction();
    // do some work
    tx.Commit();
    conn.Dispose();
  }

  public static void UseTransactionScope()
  {
    // Example 2: start transaction using Transaction Scope
    using (TransactionScope ts = new TransactionScope())
    {
      SqlConnection conn = new SqlConnection("context connection=true");
      // connection auto-enlisted in transaction on Open()
      conn.Open();
      // transactional commands here
      conn.Close();
      ts.Complete();
    } // transaction commits when TransactionScope.Dispose called implicitly
  }
};
