Sql transactions with looping, one commit statment

Question

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.

What I'm thinking would look something like this.

        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 < 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();
        }

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.

Answer 1

You could also try putting it in a Transaction Scope (http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx)

using (TransactionScope scope = new TransactionScope())
{
    // your ado.net sql here

    // if success then:
    scope.Complete();
}

The benefit of this is that it will also roll back multiple SQL commands.

Answer 2

Your propossal looks good to my with the only expection that you should use more the keyworusing (some minor fixes added too):

using (SqlCommand cmd = new SqlCommand())
{
    using (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 < 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(); <-- This is a read only collection created when constructing the command
            }

            trans.Commit();
        }
        catch (Exception e) // As you are doing the same thing on boths exceptions one handler is enought
        {
            using (LogManager lm = new LogManager())
            {
                lm.WriteErrorTextLog(e, "Broken Manager - Final Inspection Broker");
            }
            trans.Rollback();
        }
    }
}