How can I ROLLBACK all the procedure

Question

5.00/5 (1 vote)

See more:

I have three procedure include three INSERT statements in three table in sql server.
How can I ROLLBACK all the procedure if error happened for any of them.

In other words, I want call procedure1, then some c# code, then call procedure2, then another c# code and then call procedure3. I just want to ROLLBACK all the procedures if error happened for any of them.
Please note that, I can not write a procedure with three INSERT statements.

Posted 18-Jul-14 5:37am

sahar11

Add a Solution

2 solutions

Solution 1

Put your code inside TRY...CATCH block.
Inside TRY block put your Inserts and after the END; of the TRY Block put your COMMIT command.
Inside your CATCH put your Error message and your ROLLBACK command.
See this thread[^]. Another useful article here[^].

AFAIK, you only can use Rollback inside a PROCEDURE.

Posted 18-Jul-14 5:52am

Christian Amado

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dilan Shaminda · Accepted Answer · 2014-07-18T05:54:00

You can use TransactionScope to rollback your transactions.
Check these articles

All About TransactionScope[^]

http://www.c-sharpcorner.com/UploadFile/1326ef/transactionscope-in-C-Sharp/[^]

http://codingcramp.blogspot.com/2009/06/how-to-setup-and-use-transactionscope.html[^]

[EDIT]

static public int CreateTransactionScope(string connectString1)
       {

           int returnValue = 0;

           System.IO.StringWriter writer = new System.IO.StringWriter();

           try
           {
               using (TransactionScope scope = new TransactionScope())
               {
                   using (SqlConnection connection1 = new SqlConnection(connectString1))
                   {

                       connection1.Open();

                       returnValue = 0;
                       SqlCommand command1 = new SqlCommand("insertTab1", connection1);
                       returnValue = command1.ExecuteNonQuery();
                       writer.WriteLine("Rows to be affected by command1: {0}", returnValue);


                       SqlCommand command2 = new SqlCommand("insertTab2", connection1);
                       returnValue = command2.ExecuteNonQuery();
                       writer.WriteLine("Rows to be affected by command2: {0}", returnValue);

                       SqlCommand command3 = new SqlCommand("insertTab3", connection1);
                       returnValue = command3.ExecuteNonQuery();
                       writer.WriteLine("Rows to be affected by command3: {0}", returnValue);

                   }
                   scope.Complete();
                   MessageBox.Show("Success");
               }

           }
           catch (TransactionAbortedException ex)
           {
               writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
               MessageBox.Show(ex.Message);
           }
           catch (ApplicationException ex)
           {
               writer.WriteLine("ApplicationException Message: {0}", ex.Message);
               MessageBox.Show(ex.Message);
           }
           catch(Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
           Console.WriteLine(writer.ToString());

           return returnValue;
       }