title: Trying to make two SQL INSERT statements atomic in C# code
body: <p>Below is the code I'm using in an SSIS script task. I am trying to make both inserts atomic as they deal with similar customers. </p>
  
  <p>The first <code>.executeNonQuery()</code> works fine, locking the SQL table as it should. </p>
  
  <p>The second <code>.executNonQuery()</code> throws an error: </p>
  
  <blockquote>
    <p><em>ExecuteNonQuery requires the command to have a transaction when the
    connection assigned to the command is in a pending local transaction. 
    The Transaction property of the command has not been initialized.</em></p>
  </blockquote>
  
  <p>Code:</p>
  
  <pre><code>    ConnectionManager cm; 
      SqlTransaction sqlTrans;
      SqlConnection sqlConn;
      SqlCommand sqlComm;                                  
      cm = Dts.Connections["connectionManager"];
  
      try
      {
           //Set 'global' variables                                        
           SqlParameter agentID = new SqlParameter("@agentID", 1000018); //retrievedMessage.Substring(2, 10));//Primary key
           SqlParameter lastChangeOperator = new SqlParameter("@lastChangeOperator", "LVO");
           SqlParameter lastChangeDate = new SqlParameter("@lastChangeDate", DateTime.Now);
           SqlParameter controlId = new SqlParameter("@controlID", 1); //Hard-coded value for testing - CHANGE LATER
  
           //Set variables for Agent table
           SqlParameter entityType = new SqlParameter("@entityType", "P");//retrievedMessage.Substring(162, 1));
           SqlParameter fName = new SqlParameter("@fName", "test");//retrievedMessage.Substring(12, 25));
           SqlParameter lName = new SqlParameter("@lName", "test");//retrievedMessage.Substring(37, 35));
           SqlParameter suffix = new SqlParameter("@suffix", "test");//retrievedMessage.Substring(72, 10));
           SqlParameter corporateName = new SqlParameter("@corporateName", "Initech");//retrievedMessage.Substring(82, 80));
  
           //Insert record into Agent table
           sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction);
           sqlComm = new SqlCommand
           (
             "SET IDENTITY_INSERT Agent ON " +
             "INSERT INTO Agent (UniqueAgentId, EntityType, FirstName, LastName, NameSuffix, CorporateName, LastChangeOperator, LastChangeDate, ControlId) " +
             "VALUES (@agentID, @entityType, @fName, @lName, @suffix, @corporateName, @lastChangeOperator, @lastChangeDate, @controlID)" +
             "SET IDENTITY_INSERT Agent OFF",
              sqlConn//, sqlTrans
           );
  
           sqlTrans = sqlConn.BeginTransaction("SqlAgentTableUpdates");
           sqlComm.Parameters.Add(agentID);
           sqlComm.Parameters.Add(lastChangeOperator);
           sqlComm.Parameters.Add(lastChangeDate);
           sqlComm.Parameters.Add(controlId);
           sqlComm.Parameters.Add(entityType);
           sqlComm.Parameters.Add(fName);
           sqlComm.Parameters.Add(lName);
           sqlComm.Parameters.Add(suffix);
           sqlComm.Parameters.Add(corporateName);
           sqlComm.Transaction = sqlTrans;
           sqlComm.ExecuteNonQuery();
  
           //Set variables for AgentIdentification table
           SqlParameter taxIdType = new SqlParameter("taxIdType", "S");//Hard-coded value for testing - CHANGE LATER
           SqlParameter agentTaxId = new SqlParameter("@agentTaxId", "999999999");//Hard-coded value for testing - CHANGE LATER
  
           //Insert record into AgentIdentification table
           sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction);
           sqlComm = new SqlCommand
           (
             "INSERT INTO AgentIdentification (UniqueAgentId, TaxIdType, AgentTaxId, LastChangeOperator, LastChangeDate, ControlId) " +
             "VALUES (@agentID, @taxIdType, @agentTaxId, @lastChangeOperator, @lastChangeDate, @controlId)",
              sqlConn//, sqlTrans
           );
  
           sqlComm.Parameters.Add(taxIdType);
           sqlComm.Parameters.Add(agentTaxId);
           sqlComm.Transaction = sqlTrans;
           sqlComm.ExecuteNonQuery();
      }
      catch (Exception)
      {
         sqlTrans.Rollback();
         cm.ReleaseConnection(sqlConn);
      }
      finally
      {
         sqlTrans.Commit();
         cm.ReleaseConnection(sqlConn);
      }
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>I was able to make this transaction work by eliminating the second connection. However, both queries use a couple of the same variables (SqlParameters). I was forced to duplicate these in order for this to run without errors. Is there a way for them to share the variables so I do not have to waste space re-creating them? </p>