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

Question

当提交新表单时，我有一些存储过程可以对一些表进行各种更新和插入。它们都是从我的 C# 应用程序中调用的。

现在所有内容都采用try catch 格式，有没有一种方法可以确保在实际将更改提交到数据库之前它们都已成功通过？

假设前 3 个存储过程一切正常，但第 4 个失败，我想反转前 3 个中已经完成的操作。

全有或全无类型的交易。

Answer 1

您需要使用 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.
   }