c# - 确保所有更新均已成功完成？

我有一些存储过程，可以在提交新表单时进行各种更新并插入到一些表中。它们都是从我拥有的C＃应用​​程序中调用的。

现在，所有内容都是try catch格式，有没有办法可以确保在将更改真正提交到数据库之前，它们都已成功通过？

因此，假设一切顺利进行到前三个存储过程，但是第四个存储过程失败了，我想撤消前三个存储过程中已经完成的工作。

全部交易或全无交易。

最佳答案

您需要使用TransactionScope类（System.Transactions.TransactionScope）

//assuming Table1 has a single INT column, Column1 and has one row with value 12345
//and connectionstring contains a valid connection string to the database.
    //this automatically starts a transaction for you
try
{
            using (TransactionScope ts = new TransactionScope())
            {
        //you can open as many connections as you like within the scope although they need to be on the same server. And the transaction scope goes out of scope if control leaves this code.
               using (SqlConnection conn = new SqlConnection(connectionstring))
               {
                  conn.Open();
                  using (SqlCommand comm = new SqlCommand("Insert into Table1(Column1) values(999)")
                  {
                    comm.ExecuteNonQuery();
                  }
                   using (SqlCommand comm1 = new SqlCommand("DELETE from Table1 where Column1=12345"))
                   {
                     comm1.ExecuteNonQuery();
                   }
                }//end using conn
               ts.Complete() ; //commit the transaction; Table1 now has 2 rows (12345 and 999)
            }//end using ts

}
  catch(Exception ex)
   {
     //Transaction is automatically rolled back for you at this point, Table1 retains original row.
   }