我怎么能ROLLBACK所有的程序 [英] How can I ROLLBACK all the procedure

问题描述

我有三个程序包括sql server中三个表中的三个INSERT语句。

如果其中任何一个错误发生，我如何ROLLBACK所有程序。



换句话说，我想调用procedure1，然后调用一些c＃代码，然后调用procedure2，然后调用另一个c＃代码，然后调用procedure3。我只是想在其中任何一个发生错误的情况下ROLLBACK所有程序。

请注意，我不能用三个INSERT语句编写程序。

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.

推荐答案

将你的代码放在TRY ... CATCH块里面。

里面的TRY块把你的插入放在END之后; TRY Block中放入了COMMIT命令。

在你的CATCH中放入你的错误信息和你的ROLLBACK命令。

见此主题 [ ^ ] 。另一篇有用的文章这里 [ ^ ]。



AFAIK，您只能在PROCEDURE中使用Rollback。

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.

您可以使用 TransactionScope 回滚你的交易。

查看这些文章



所有关于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 [ ^ ]

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[^]

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

这篇关于我怎么能ROLLBACK所有的程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！