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

class Bugtest {

          //  Connection string, change server and database!
  private static string
      strConn = "Integrated Security=SSPI;" +
                "Data Source=SQL2014CU;Initial Catalog=Northwind;";


   private static void Subsie(int threadno,
                              int data) {

       using(SqlConnection cn = new SqlConnection(strConn)) {
          try {
              cn.Open();
          }
          catch (System.Exception ex) {
              System.Console.WriteLine(ex.Message, "Connection failed!");
              return;
          }

          SqlCommand cmd = new SqlCommand(
                @"SELECT @data = SUM(Quantity), @x = xact_state() FROM [Order Details]
                  WHERE OrderID = 11000 + @threadno");
          cmd.Parameters.Add("@threadno", SqlDbType.Int).Value = threadno;
          cmd.Parameters.Add("@data", SqlDbType.Int);
          cmd.Parameters["@data"].Direction = ParameterDirection.Output;
          cmd.Parameters.Add("@x", SqlDbType.Int);
          cmd.Parameters["@x"].Direction = ParameterDirection.Output;
          cmd.Connection = cn;
          cmd.ExecuteNonQuery();
          int res = System.Convert.ToInt32(cmd.Parameters["@data"].Value);
          System.Console.WriteLine("The value for " +
                                   threadno.ToString() + " is " +
                                   res.ToString());
          int x = System.Convert.ToInt32(cmd.Parameters["@x"].Value);
          System.Console.WriteLine("The value for xact_state " +
                                   threadno.ToString() + " is " +
                                   x.ToString());
          cmd.Dispose();
       }
   }

   public static void threadie(System.Object _threadno) {

      TransactionOptions tropt = new TransactionOptions();
 //     tropt.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
      tropt.Timeout = new System.TimeSpan(1, 0, 1);

      int threadno = (int) _threadno;

      SqlConnection cn1 = new SqlConnection(strConn);
      cn1.Open();
      SqlCommand cmd1 = new SqlCommand("SELECT 1", cn1);
      cmd1.ExecuteNonQuery();
      SqlConnection cn2 = new SqlConnection(strConn);
      cn2.Open();
      SqlCommand cmd2 = new SqlCommand("SELECT 12", cn2);
      cmd2.ExecuteNonQuery();
      cmd1.Dispose();
      cmd2.Dispose();
      cn1.Close();
      cn2.Close();
      cn1.Dispose();
      cn2.Dispose();



      using (TransactionScope tx1 =
              new TransactionScope(TransactionScopeOption.RequiresNew,
                                   tropt))
      {
         Subsie(threadno, 1);
         Thread.Sleep(threadno * 500);
         Subsie(threadno, 2);
         Thread.Sleep(threadno * 7000);
         Subsie(threadno, 3);
         tx1.Complete();
      }

   }


   public static void Main () {
       Thread[] threads = new Thread[10];
       for (int i = 0; i < 10; i++) {
           threads[i] = new System.Threading.Thread(threadie);
           threads[i].Start(i+1);
       }

       for (int i = 0; i < 10; i++) {
           threads[i].Join();
       }
   }
}