How can I execute multiple transactions under same connection?

Question

Here my requirement is like this:

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

Here is the sample code. Is this the proper way of handling transactions and errors, or is there a better 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;
      }
   }
}

LeJeune · Accepted Answer · 2009-02-25 05:30:22Z

1

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.

answered Feb 25, 2009 at 5:30

LeJeune

9501 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BFree · Accepted Answer · 2009-02-25 05:30:31Z

0

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
}

answered Feb 25, 2009 at 5:30

BFree

104k21 gold badges164 silver badges205 bronze badges

Collectives™ on Stack Overflow

How can I execute multiple transactions under same connection?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related