locked
Web Service - Need to add transactions RRS feed

  • Question

  • User1029764681 posted

    I have a webservice that has one main function.  This function makes several insert/update/deletes as it process.

    Each of those insert/update/deletes are in there on function in their respective class's.  

    In each of those functions I

    1. Open the connection.
    2. Excecute the database action
    3. Dispose and close the connection.

    I have been asked to make this all transaction based, so that after it performs all say 5 inserts/updates/deletes (depending on what was sent to the service) Then I can commit or rollback at the very end of the main function.

    I have found examples on how to do it on each Insert/update/delete.  The goal is to make sure they are all successful and then commit, or if any fail, rollback.

    Wednesday, February 25, 2015 9:55 AM

Answers

  • User1644755831 posted

    Hi fmrock164,

    If you are using Entity Framework you can do it like this

    public static bool DoDatabaseOperation()
        {
          bool blnSuccess = false;
          try
          {
    
            using (DataBaseContext db = new DataBaseContext())
            {
              //Begin a new transaction
              using (DbContextTransaction transaction = db.Database.BeginTransaction())
              {
                try
                {
                  // Do Insert // Update //Delete
    
                  //Complete the transaction
                  transaction.Commit();
                  blnSuccess = true;
                }
                catch (Exception)
                {
                  //exception occurred roll back the database
                  transaction.Rollback();
                  throw;
                }
              }
            }
          }
          catch (Exception ex)
          {
            throw;
          }
          return blnSuccess;
        }

    If you are not using entity framework and using sqlconnection directly then you can use Transaction Scope across multiple connections.

    https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx

      public static bool DoDatabaseOperation()
        {
          bool blnSuccess = false;
          try
          {
            string commandText1 = "somecommand";
            string commandText2 = "somecommand2";
            string connectString1 = "firstdbconnectinostring";
            string connectString2 = "seconddbconnectionstring";
            // Create the TransactionScope to execute the commands, guaranteeing 
            // that both commands can commit or roll back as a single unit of work. 
            using (TransactionScope scope = new TransactionScope())
            {
              using (SqlConnection connection1 = new SqlConnection(connectString1))
              {
                // Opening the connection automatically enlists it in the  
                // TransactionScope as a lightweight transaction.
                connection1.Open();
    
                // Create the SqlCommand object and execute the first command.
                SqlCommand command1 = new SqlCommand(commandText1, connection1);
                command1.ExecuteNonQuery();
    
                // If you get here, this means that command1 succeeded. By nesting 
                // the using block for connection2 inside that of connection1, you 
                // conserve server and network resources as connection2 is opened 
                // only when there is a chance that the transaction can commit.    
                using (SqlConnection connection2 = new SqlConnection(connectString2))
                {
                  // The transaction is escalated to a full distributed 
                  // transaction when connection2 is opened.
                  connection2.Open();
    
                  // Execute the second command in the second database.
                  SqlCommand command2 = new SqlCommand(commandText2, connection2);
                  command2.ExecuteNonQuery();
                }
              }
    
              // The Complete method commits the transaction. If an exception has been thrown, 
              // Complete is not  called and the transaction is rolled back.
              scope.Complete();
    
            }
    
          }
          catch (TransactionAbortedException ex)
          {
            //handle it
          }
          catch (ApplicationException ex)
          {
            //handle it
          }
    
          return blnSuccess;
        }

    Hope this helps.

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 11, 2015 5:56 AM

All replies

  • User269602965 posted

    You got the general idea.

    But I do not see a question.

    Wednesday, February 25, 2015 3:06 PM
  • User1029764681 posted

    I can do the comit/rollback for each transaction as I move through... however, I want to do it once at the end if they are all successful.

    That's what I have not been able to see any examples or anyone doing anything similar (or I am searching on the wrong terms).  This way I do not get any orphaned records if an issue happens mid way through.

    Thanks again for your help.

    Wednesday, February 25, 2015 3:58 PM
  • User269602965 posted

    My understanding is once COMMIT is done, Rollback is not possible.

    But if you are using Oracle, you can create SAVEPOINTS after each insert within the Transaction

    and Rollback to a specific SAVEPOINT at the end if there is a problem.

    Other databases may have similar function.

    Wednesday, February 25, 2015 6:08 PM
  • User1644755831 posted

    Hi fmrock164,

    If you are using Entity Framework you can do it like this

    public static bool DoDatabaseOperation()
        {
          bool blnSuccess = false;
          try
          {
    
            using (DataBaseContext db = new DataBaseContext())
            {
              //Begin a new transaction
              using (DbContextTransaction transaction = db.Database.BeginTransaction())
              {
                try
                {
                  // Do Insert // Update //Delete
    
                  //Complete the transaction
                  transaction.Commit();
                  blnSuccess = true;
                }
                catch (Exception)
                {
                  //exception occurred roll back the database
                  transaction.Rollback();
                  throw;
                }
              }
            }
          }
          catch (Exception ex)
          {
            throw;
          }
          return blnSuccess;
        }

    If you are not using entity framework and using sqlconnection directly then you can use Transaction Scope across multiple connections.

    https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx

      public static bool DoDatabaseOperation()
        {
          bool blnSuccess = false;
          try
          {
            string commandText1 = "somecommand";
            string commandText2 = "somecommand2";
            string connectString1 = "firstdbconnectinostring";
            string connectString2 = "seconddbconnectionstring";
            // Create the TransactionScope to execute the commands, guaranteeing 
            // that both commands can commit or roll back as a single unit of work. 
            using (TransactionScope scope = new TransactionScope())
            {
              using (SqlConnection connection1 = new SqlConnection(connectString1))
              {
                // Opening the connection automatically enlists it in the  
                // TransactionScope as a lightweight transaction.
                connection1.Open();
    
                // Create the SqlCommand object and execute the first command.
                SqlCommand command1 = new SqlCommand(commandText1, connection1);
                command1.ExecuteNonQuery();
    
                // If you get here, this means that command1 succeeded. By nesting 
                // the using block for connection2 inside that of connection1, you 
                // conserve server and network resources as connection2 is opened 
                // only when there is a chance that the transaction can commit.    
                using (SqlConnection connection2 = new SqlConnection(connectString2))
                {
                  // The transaction is escalated to a full distributed 
                  // transaction when connection2 is opened.
                  connection2.Open();
    
                  // Execute the second command in the second database.
                  SqlCommand command2 = new SqlCommand(commandText2, connection2);
                  command2.ExecuteNonQuery();
                }
              }
    
              // The Complete method commits the transaction. If an exception has been thrown, 
              // Complete is not  called and the transaction is rolled back.
              scope.Complete();
    
            }
    
          }
          catch (TransactionAbortedException ex)
          {
            //handle it
          }
          catch (ApplicationException ex)
          {
            //handle it
          }
    
          return blnSuccess;
        }

    Hope this helps.

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 11, 2015 5:56 AM