当存储的产品之一在.net c# 中发现失败时数据回滚

Question

有谁知道如果存储过程之一在此过程中失败，如何在 .net c# 中回滚所有数据？

示例：

 protected void btnSave_Click(object sender, EventArgs e)
   {
      int ixTest= SaveTest("123");
      CreateTest(ixTest);

   }

   protected int SaveTest(int ixTestID)
   {
      SubSonic.StoredProcedure sp = Db.SPs.TestInsert(
         null,
         ixTestID);
      sp.Execute();
      int ixTest= (int)sp.OutputValues[0];
      return ixTest;
   }


   private long CreateTest(int ixTest)
   {
      long ixTestCustomer = CreateTestCustomer();

      TestCustomer testCustomer= new TestCustomer();
      try
      {
          testCustomer.TestCustomerId = ixTest;
          testCustomer.InteractionId = ixTestCustomer ;
          testCustomer.Save();
      }
      catch
      {
          Response.Redirect("pgCallSaveFailure.aspx");
      }

      m_saleDetail = new TestSaleDetail();
      try
      {
          m_saleDetail.SaleId = sale.SaleId;
          m_saleDetail.Save();
      }
      catch
      {
          Response.Redirect("pgCallSaveFailure.aspx");
      }

      return ixTestCustomer ;
   }

我有以下代码将调用 btnSave_Click，然后它将调用另外 2 个函数 Savetest() 和 CreateTest() 将数据保存到数据库中。如果问题仅发生在 CreateTest() 中并且 Savetest() 已成功运行，如何回滚以下代码中的所有数据事务。如何回滚 Savetest() 和 CreateTest() 的所有数据？

Answer 1

使用 TransactionScope 类

Answer 2

代码执行准确如下。希望你能明白。

public void SaveData()
{
SqlConnection connDB = new SqlConnection();
SqlCommand cmdExecuting = new SqlCommand();


try {
    connDB = new SqlConnection(connection_string);
    cmdExecuting.Connection = connDB;
    connDB.Open();
    cmdExecuting.Transaction = connDB.BeginTransaction();


    int result = 0;

    result = Method1(cmdExecuting);

    if (result != 0) {
        cmdExecuting.Transaction.Rollback();
        return;
    }

    result = Method2(cmdExecuting);

    if (result != 0) {
        cmdExecuting.Transaction.Rollback();
        return;
    }

    cmdExecuting.Transaction.Commit();

} catch (Exception ex) {
    cmdExecuting.Transaction.Rollback();
} finally {
    cmdExecuting.Dispose();
    cmdExecuting = null;
    connDB.Close();
    connDB = null;
}


}

public int Method1(SqlCommand cmdExecuting)
{

cmdExecuting.Parameters.Clear();
cmdExecuting.CommandText = "stored proc 01";
cmdExecuting.CommandType = CommandType.StoredProcedure;

cmdExecuting.Parameters.Add("@para1", SqlDbType.Int);
cmdExecuting.Parameters("@para1").Value = value;

return cmdExecuting.ExecuteScalar();

}


public int Method2(SqlCommand cmdExecuting)
{

cmdExecuting.Parameters.Clear();
cmdExecuting.CommandText = "stored proc 02";
cmdExecuting.CommandType = CommandType.StoredProcedure;

cmdExecuting.Parameters.Add("@para1", SqlDbType.Int);
cmdExecuting.Parameters("@para1").Value = value;

return cmdExecuting.ExecuteScalar();

}