title: Sql transactions with looping, one commit statment
body: <p>I'm mainly just curious if my approach to this is correct. What I'm trying to do is loop though two arrays and insert values at an index into my database, and rolling back if something fails.</p>
  
  <p>What I'm thinking would look something like this.</p>
  
  <pre><code>        SqlCommand cmd = new SqlCommand();
          SqlConnection cn = new SqlConnection(s.ConnectionString.ConnectionString);
  
          cmd.Connection = cn;
          cmd.CommandText = "asp_FinalInspectionTransaction";
          cmd.CommandType = CommandType.StoredProcedure;
  
          SqlTransaction trans = cn.BeginTransaction();
          cmd.Transaction = trans;
  
          cn.Open();
  
          try
          {
              for (int i = 0; i &lt; array1.Length - 1; i++)
              {
                  cmd.Parameters.AddWithValue("@MasterID", masterID);
                  cmd.Parameters.AddWithValue("@TagName", array1[i]);
                  cmd.Parameters.AddWithValue("@TagValue", array2[i]);
  
                  cmd.ExecuteNonQuery();
  
                  cmd.Parameters = new SqlParameterCollection();
              }
  
              trans.Commit();
          }
          catch (SqlException e)
          {
              LogManager lm = new LogManager();
              lm.WriteErrorTextLog(e, "Broken Manager - Final Inspection Broker");
              lm.Dispose();
  
              trans.Rollback();
          }
          catch (Exception e)
          {
              LogManager lm = new LogManager();
              lm.WriteErrorTextLog(e, "Broken Manager - Final Inspection Broker");
              lm.Dispose();
  
              trans.Rollback();
          }
          finally
          {
              cn.Close();
          }
  </code></pre>
  
  <p>I'm still learning all the in and outs of ADO.Net and haven't worked with transactions outside of SQL stored procedures. I know I could put it into XML and do all the looping and inserts in the stored procedure it self. Just curious if this route would work as well. Any advice would be greatly appreciated.</p>