如何在.net c# 中的数据库对象中应用事务回滚

Question

有谁知道如何在.net c# 中的数据库对象中应用事务回滚？

例子： 我最初有以下代码：

   protected void btnSave_Click(object sender, EventArgs e)
   {
      try {
         m_test = CreateTest();
      } catch{}
   }


   private Db.Test CreateTest()
   {
           var test= new Test();
           test.Title = sTitle.SelectedValue;
           test.FirstName = sFirstName.Text;
           test.LastName = sLastName.Text;
           test.PhoneHome = sHomePhone.Text;
           test.PhoneWork = sWorkPhone.Text;
           test.PhoneMobile = sMobile.Text;
           test.EmailAddress = sEmail.Text;
           test.Save();

           return test;
   }

然后我尝试将事务回滚操作应用到代码中，并在其中应用以下代码...然后我堆栈在方法 CreateTest 下。我不知道如何应用数据库对象的代码

       protected void btnSave_Click(object sender, EventArgs e)
       {

          SqlConnection connDB = new SqlConnection();
          SqlCommand cmdExecuting = new SqlCommand();

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

             m_test = CreateTest(cmdExecuting);

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


   private Db.Test CreateTest(SqlCommand  cmdExecuting)
   {
           var test= new Test();
           test.Title = sTitle.SelectedValue;
           test.FirstName = sFirstName.Text;
           test.LastName = sLastName.Text;
           test.PhoneHome = sHomePhone.Text;
           test.PhoneWork = sWorkPhone.Text;
           test.PhoneMobile = sMobile.Text;
           test.EmailAddress = sEmail.Text;
           test.Save();

           return test;
   }

有谁知道在这种情况下如何在 CreateTest() 中进行编码？

Answer 1

看一下 TransactionScope 类，它提供了几个好处包括

• 自动交易登记
  范围内建立的连接
• 事务自动回滚
  当异常发生时
• 我认为它也会使代码
  更清晰一些，因为它减少了
  所需的代码量

。

try    
{   
    using (TransactionScope scope = new TransactionScope())     
    {                                   
        m_test = CreateTest();          
        scope.Complete(); // Commit transaction     
    }    
}    
catch (Exception ex)    
{
    // Transaction is automatically rolled back
}

另请参阅文章在 .NET Framework 2.0 中介绍 System.Transactions一个很好的起点。

Answer 2

您可以采取以下措施，您不需要在后端处理事务，sqltransaction 对象将处理它。您可以将此代码放在您自己的事件中，无论哪个事件负责将数据插入数据库

 protected void Button1_Click(object sender, EventArgs e)
    {
      SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
      myConnection.Open();

      // Start a local transaction
      SqlTransaction myTrans = myConnection.BeginTransaction();

      SqlCommand myCommand = new SqlCommand();
      myCommand.Connection = myConnection;
      myCommand.Transaction = myTrans;
      try
      {
        myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
        myCommand.ExecuteNonQuery();
        myCommand.CommandText = "delete * from Region where RegionID=101";

        // Attempt to commit the transaction. 
        myCommand.ExecuteNonQuery();
        myTrans.Commit();
        Response.Write("Both records are written to database.");
      }
      catch (Exception ep)
      {
        // Attempt to roll back the transaction. 
        myTrans.Rollback();
        Response.Write(ep.ToString());
        Response.Write("Neither record was written to database.");
      }
      finally
      {
        myConnection.Close();
      }
    }