multiple sqltransactions in single sqlconnection

Question

I have some code that I want to execute as follows. But I keep getting the exception "This SqlTransaction has completed; it is no longer usable" on the 2nd iteration. Could someone help me point out what I am doing wrong here? Thanks!

    SqlConnection cn = (SqlConnection)SqlConnectionManager.Instance.GetUserConnection(user);
 cn.Open();
 try
 {
    foreach (Master mRecord in masterList)
  {
   if (sqlTransaction == null)
       sqlTransaction = cn.BeginTransaction();
   SqlCommand cm = cn.CreateCommand();
   cm.Transaction = sqlTransaction;
   cm.CommandType = CommandType.StoredProcedure;
   cm.CommandText = "pr_InsertRecords";
       try
   {
    cm.ExecuteNonQuery();
    Debug.WriteLine("Auditor.Write: end sql table value param");
    sqlTransaction.Commit();
    sqlTransaction.Dispose();
   }
   catch (Exception Ex)
   {
    Debug.WriteLine(" Exception message: " + Ex.Message);
    if (Ex.InnerException != null)
    {
     Debug.WriteLine("Inner exception message" + Ex.InnerException.Message);
    }
    sqlTransaction.Rollback();
   }
  }
 }
 finally
 {
        cn.Close();
      }

Answer 1

You need to create a new SqlTransaction object on each iteration or move the transaction completely outside the loop if you are wanting all the operations inside the loop to occur in a single transaction. Once you commit a transaction, it is necessary to call BeginTransaction again on the connection to start a new one. You cannot reuse the old transaction object.

Answer 2

Try setting your sqlTransaction object to null after disposing it. Side note, you really should be wrapping those IDisposable objects in using blocks so Dispose is called at all times.

sqlTransaction.Commit();
sqlTransaction.Dispose();
sqlTransaction = null;

Answer 3

Inside the loop you either commit or rollback, but you do not reset the reference to null. SqlTransaction in general is not used as this, is used in a using() block, just as a SqlConnection is:

using (SqlConnection cn = SqlConnectionManager.Instance.GetUserConnection(user)) 
{
  foreach (Master mRecord in masterList)
  {
  try
  {
    using (SqlTransaction sqlTransaction = cn.BeginTransaction()) 
    {
      using (SqlCommand cm = cn.CreateCommand()) 
      {
        cm.Transaction = sqlTransaction;
        cm.CommandType = CommandType.StoredProcedure;
        cm.CommandText = "pr_InsertRecords";
        cm.ExecuteNonQuery();
      }
      sqlTransaction.Commit();
      Debug.WriteLine("Auditor.Write: end sql table value param");
    }
  }
  catch (Exception Ex)
  {
    Debug.WriteLine(" Exception message: " + Ex.Message);
  }
}