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 int ComposeTx()
  {
    int returnCode = 0;
    // determine if we have transaction
    bool noCallerTx = (Transaction.Current == null);
    SqlTransaction tx = null;

    SqlConnection conn = new SqlConnection("context connection=true");
    conn.Open();

    if (noCallerTx)
      tx = conn.BeginTransaction();

    try
    {
      // do the procedure's work here
      SqlCommand workcmd = new SqlCommand("INSERT jobs VALUES('New job', 10, 10)", conn);
      if (tx != null)
        workcmd.Transaction = tx;
      int rowsAffected = workcmd.ExecuteNonQuery();


      if (noCallerTx)
        tx.Commit();
    }

    catch (Exception ex)
    {
      if (noCallerTx)
      {
        tx.Rollback();
        // raise error - covered later in chapter
      }
      else
      {
        // signal an error to the caller with return code
        returnCode = 50010;
      }
    }
    conn.Dispose();
    return returnCode;
  }
};
