using construct with sqlTransaction dependent on number of different action results ...

Question

I'm curious if following code would be considered safe?

using (SqlConnection cn = new SqlClient.SqlConnection(connectionString))
{
   cn.Open();
   using (SqlTransaction tr = cn.BeginTransaction())
   {
      try
      {

         if (!Data.DoSomething1(tr, p1, p2))
         {
            tr.Rollback();
            return false;
         }

         foreach (ItemType item in Items)
         {
            if (!Data.DoSomething2(tr, p3, p4))
            {
               tr.Rollback();
               return false;
            }
         }

         tr.Commit();
         return true;
      }
      catch (Exception myErr)
      {
         if (tr != null)
            tr.Rollback();

         throw myErr;
      }
      finally
      {
         if (cn != null)
         {
            cn.Close();
            cn.Dispose();
         }
      }
   }
}

I wanted to pass transaction 'tr' by ref but couldn't because it is within "using" construct. I would like to hear suggestions for better approach in similar situations.

Regards

Answer 1

You don't need to pass the transaction by reference. It is a reference type so when you pass it to a function you are already providing a reference. What you are doing is fine.

Although there's a couple of other things about your code, not directly related to your question:

• you really should use throw; not throw myErr;.
• There's no need to dispose your resources explicitly if you already have them in a using statement. That's what the using is for.

Answer 2

Your finally block is not required as is your inner using statement.

The point of the using statement is that you do not need to use try catch blocks, one is implicity created for you - this means the finally block is redundant in your example. Your inner using block is also redundant.

Good question though.

A pattern for using both these items is given here:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx