title: Call multiple SQL Server stored procedures in a transaction
body: <p>For usage in my current project I've created a class that allows me to call SQL Server async.</p>
  
  <p>My code looks like this:</p>
  
  <pre><code>internal class CommandAndCallback&lt;TCallback, TError&gt;
  {
      public SqlCommand Sql { get; set; }
      public TCallback Callback { get; set; }
      public TError Error { get; set; }
  }
  
  class MyCodes:SingletonBase&lt;MyCodes&gt;
  {
      private static string _connString = @"Data Source=MyDB;Initial Catalog=ED;Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST";
  
      private MyCodes() { }
  
      public void SetSystem(bool production)
      {
          _connString =
              string.Format(@"Data Source=MyDB;Initial Catalog={0};Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST", production ? "ED" : "TEST_ED");
      }
  
      public void Add(string newCode, Action&lt;int&gt; callback, Action&lt;string&gt; error)
      {
          var conn = new SqlConnection(_connString);
          SqlCommand cmd = conn.CreateCommand();
          cmd.CommandTimeout = 0;
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = @"ADD_CODE";
          cmd.Parameters.Add("@NEW", SqlDbType.NVarChar).Value = newCode;
          cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;
  
          try
          {
              cmd.Connection.Open();
          }
          catch (Exception ex)
          {
              error(ex.ToString());
              return;
          }
  
          var ar = new CommandAndCallback&lt;Action&lt;int&gt;, Action&lt;string&gt;&gt; { Callback = callback, Error = error, Sql = cmd };
          cmd.BeginExecuteReader(Add_Handler, ar, CommandBehavior.CloseConnection);
      }
  
      private static void Add_Handler(IAsyncResult result)
      {
          var ar = (CommandAndCallback&lt;Action&lt;int&gt;, Action&lt;string&gt;&gt;)result.AsyncState;
          if (result.IsCompleted)
          {
              try
              {
                  ar.Sql.EndExecuteReader(result);
                  ar.Callback(Convert.ToInt32(ar.Sql.Parameters["@NewId"].Value));
              }
              catch (Exception ex)
              {
                  ar.Error(ex.Message);
              }
          }
          else
          {
              ar.Error("Error executing SQL");
          }
      }
  
  public void Update(int codeId, string newCode, Action callback, Action&lt;string&gt; error)
      {
          var conn = new SqlConnection(_connString);
          SqlCommand cmd = conn.CreateCommand();
          cmd.CommandTimeout = 0;
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = @"UPDATE_CODE";
          cmd.Parameters.Add("@CODE_ID", SqlDbType.Int).Value = codeId;
          cmd.Parameters.Add("@NEW", SqlDbType.NVarChar).Value = newCode;
  
          try
          {
              cmd.Connection.Open();
          }
          catch (Exception ex)
          {
              error(ex.ToString());
              return;
          }
  
          var ar = new CommandAndCallback&lt;Action, Action&lt;string&gt;&gt; { Callback = callback, Error = error, Sql = cmd };
          cmd.BeginExecuteReader(Update_Handler, ar, CommandBehavior.CloseConnection);
      }
  
      private static void Update_Handler(IAsyncResult result)
      {
          var ar = (CommandAndCallback&lt;Action, Action&lt;string&gt;&gt;)result.AsyncState;
          if (result.IsCompleted)
          {
              try
              {
                  ar.Sql.EndExecuteReader(result);
                  ar.Callback();
              }
              catch (Exception ex)
              {
                  ar.Error(ex.Message);
              }
          }
          else
          {
              ar.Error("Error executing SQL");
          }
      }
  
  }
  </code></pre>
  
  <p>This may look like too much of code, but it lets me call it as so:</p>
  
  <pre><code>private void Add_Click(object sender, EventArgs e)
  {
     MyCodes.Instance.Add("Test",Success,Error)
  }
  
  private void Success(int newId)
  {
     MessageBox.Show(newId.ToString(), "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
  }
  
  private void Error(string error)
  {
     MessageBox.Show(error, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
  }
  </code></pre>
  
  <p>Above code works just fine for me, I'm able to do every call async.</p>
  
  <p>Problem that I have right now is to do multiple calls as transaction - I would like to update 2 codes and add one new.</p>
  
  <p>Normally I would call update, then in success handler call second update, and in handler to second update I would call add that would return new id.</p>
  
  <p>Something like:</p>
  
  <pre><code>-UPDATE CODE
   |-UPDATE CODE
     |-ADD CODE (only this one return something)
  </code></pre>
  
  <p>But I would like to call all of those as transaction, so if add code would break updates would rollback.</p>
  
  <p>Question:</p>
  
  <p>Is it possible to call multiple async queries as a transaction?</p>
  
  <p>Can I call my above methods as transaction or do I must create separate method to call my procedures as one? (I would like to avoid this one because it's just copying the same code from one method to another)</p>
  
  <p>I would like to add that I use .NET 3.5 so await and other nice features aren't an option.</p>