How can I execute multiple transactions under same connection?
<p>Here my requirement is like this:</p>

<ol>
<li>Initially I execute a stored procedure to insert\update a record in DB, under one  transaction.</li>
<li>In case the SP execution fails due to some issue, I must be able to re-invoke the SP again under different transaction.</li>
<li>Even if the re-invocation of SP fails then only, I should throw error to calling function.</li>
</ol>

<p>Here is the sample code. Is this the proper way of handling transactions and errors, or is there a better way to do this?</p>

<pre><code>public void InsertUser(string code)
{  
   bool bRetry=false;
   SqlTransaction transaction = null;
   Exception RetrunEx = null;
   using (SqlConnection sqlConnection = new SqlConnection(this.connectionString))
   {
      sqlConnection.Open();

      SqlCommand sqlCommand = new SqlCommand("InsertUser", sqlConnection);
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
      SqlParameter param = sqlCommand.Parameters.Add("@UserCode", SqlDbTypes.VarChar);
      param.Value = code;

      //Is this the proper place to begin a transaction?
      SqlTransaction transaction = connection.BeginTransaction();
      sqlCommand.Transaction = transaction;     

      try
      {         
          sqlCommand.ExecuteNonQuery();
          transaction.Commit();

      }      
      catch(SqlException SqlEx)
      {
         transaction.Rollback();
     bRetry = true;
         RetrunEx = SqlEx;
      }
      catch(Exception ex)
      {
          transaction.Rollback();
          RetrunEx = ex;
      }

      //Will this be treated as new transaction?
      //Is there any best way of doing this?
      transaction = connection.BeginTransaction();
      sqlCommand.Transaction = transaction;

      try
      { 
          if (bRetry)        
      {
              sqlCommand.ExecuteNonQuery();
              transaction.Commit();
              ReturnEx = null;
          }

      }      
      catch(Exception Ex)
      {
         transaction.Rollback();
         RetrunEx = Ex;
      }

      //When both the trials fails then throw exception
      if (RetrunEx != null)
      {
          throw RetrunEx;
      }
   }
}
</code></pre>
