How can I execute multiple transactions under same connection?

Here my requirement is like this:

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

Here is the sample code, Please suggest whether this is proper way of handling transactions and error. Or is there any bettter way to do this?

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

} }

From stackoverflow

• The easiest way to work with transaction is to use System.Transaction It is a very simple yet powerful api.

  ...
  using (TransactionScope ts = new TransactionScope())
  {
     //Do Transactional Work
     ...
     ...
  
     //Commit your transaction
     ts.Complete();
   }
  

  You dont need no try/catch to rollback your transaction. If you exit the "using" with an exception, or without calling the "ts.Complete" statement, your transaction will be automatically rolled back.

• I'd probably have a method that's only purpose is to try an execute this stored procedure. Pseudo Code:

  public bool ExecuteSP()
  {
     try{
     //open connection, begin tran execture sp
     //commit transaction, return true;
     }
  
     catch(SqlException){
  
      //rollback transaction
      //return false
      }
  
  }
  

  Then in your calling code you can just do something like this:

  if(!ExecuteSP() && !ExecuteSP())
  {
     //throw Exception
  }