title: Execute multiple stored procedures with single trip to database
body: <p>I have a lot of legacy data access code mainly SqlCommand with Stored Procedure calls that we used to execute alot of Insert statment into an database. 
  As long as the SQL server has been on the same machine as the application there have been acceptable performace but now are we trying to move some of the data to SQL Azure.</p>
  
  <p>The problem is that our code calls a SP for every record to insert which results in quite a few trips to the database and when not located on the same server it takes some time. </p>
  
  <pre><code>var conn = new SqlConnection("connString")
  var cmd = new SqlCommand(conn, "spMyStoreProc");
  cmd.Params.Add("@a", SqlDbType.VarChar, 10);
  cmd.Params.Add("@b", SqlDbType.Int);
  
  using(conn)
  {
   conn.Open();
   foreach(var rec in recordsToInsert)
   {
     cmd.Parameters["@a"].Value = rec.A;
     cmd.Parameters["@b"].Value = rec.B;
     cmd.ExecuteNonQuery();
   }
   conn.Close();
  }
  </code></pre>
  
  <p>I have tried the code above with and without Transactions.</p>
  
  <p>I have also tried to use a "batch" SQL statement to execute several SPs in every trip to the server.
  Like this:</p>
  
  <pre><code>var cmd = new SqlCommand(conn);
  cmd.CommandText = "EXEC spMyStoreProc @a='a' @b=2; EXEC spMyStoreProc @a='b' @b=4;"
  </code></pre>
  
  <p>It greatly increases the performance of the operation but since I have quite a few SPs where every SP has about 20-50 params it gets quite tedious to write this code for all the insert commands in this data access component.</p>
  
  <p>Is this the best way to achive this, or can I somehow tell ADO.NET I want to execute my calls as a batch (havent fount anything suggesting its possible but feel that I atleast should ask) to avoid network latency etc betweeen every single SP call?</p>
  
  <p>If not does anybody know any good way to achive this without having to write it "by hand" and since its a legacy application I can not change the data layer completely.
  Is there any applications that can take SqlCommands with parameters and generate the TQL they would execute?</p>
  
  <p>Thanks in advance</p>