title: Using TransactionScope around a stored procedure with transaction in SQL Server 2014
body: <p>I am using C# and ADO.Net with a <code>TransactionScope</code> to run a transaction in an ASP.Net app. This transaction is supposed to save some data across multiple tables and then send an email to subscribers.</p>
  
  <p><strong>Question</strong>: is it a valid use of <code>TransactionScope</code>, when it includes a call to a stored procedure that has its own transaction in SQL Server 2014, or should I remove the SQL transaction statements i.e. <code>begin tran</code>, <code>commit tran</code> and <code>rollback tran</code> statements from the stored procedure being called within this <code>TransactionScope</code>?</p>
  
  <p>The C# code for this scenario and also the T-SQL code of stored procedure are both mentioned below.</p>
  
  <p>C# code using <code>TransactionScope</code>:</p>
  
  <pre><code>  try 
      {
          using (TransactionScope scope = new TransactionScope())
          {
              using (SqlConnection connection1 = new SqlConnection(connectString1))
              {
                  // Opening the connection automatically enlists it in the  
                  // TransactionScope as a lightweight transaction.
                  connection1.Open();
  
                  // SaveEmailData is a stored procedure that has a transaction within it
                  SqlCommand command1 = new SqlCommand("SaveEmailData", connection1);
                  command1.CommandType = CommandType.StoredProcedure;
                  command1.ExecuteNonQuery();
  
              }
  
              //Send Email using the helper method
              EmailHelper.SendCustomerEmails(customerIds);
  
              // The Complete method commits the transaction. If an exception has been thrown, 
              // Complete is not  called and the transaction is rolled back.
              scope.Complete();
  
          }
      }
      catch( Exception ex)
      {
         Logger.Log(ex);
      }
  </code></pre>
  
  <p>T-SQL of stored procedure <code>SaveEmailData</code>:</p>
  
  <pre><code>SET NOCOUNT ON
  
      BEGIN TRY
          DECLARE @emailToUserId BIGINT
  
          BEGIN TRAN
          -- //update statement. detail statement omitted
          UPDATE TABLE1...
  
           --update statement. detail statement omitted
          UPDATE TABLE2...
  
          IF @@trancount &gt; 0
          BEGIN
              COMMIT TRAN
          END
      END TRY
  
      BEGIN CATCH
  
          IF @@TRANCOUNT &gt; 0
          BEGIN
              ROLLBACK TRAN
          END
  
          EXEC Error_RaiseToADONET
  
      END CATCH
  </code></pre>