C#中的事务

Transactions in C#

提问by user29964

In addition to this question: Preorder tree traversal copy folderI was wondering if it is possible to create a transaction that contains different calls to the database.

除了这个问题：Preorder tree traversal copy folder我想知道是否可以创建一个包含对数据库的不同调用的事务。

ex:

前任：

public bool CopyNode(int nodeId, int parentNode)
    { 
        // Begin transaction.
        try
        {
            Method1(nodeId);
            Method2(nodeId, parentNode);
            Method3(nodeId);
        }
        catch (System.Exception ex)
        { 
            //rollback all the methods
        }
    }

I don't know if this is possible. We are using subsonic to do the database calls. This is really important, not only for the tree traversal problem but also for some other stuff we do.

我不知道这是否可能。我们正在使用亚音速进行数据库调用。这真的很重要，不仅对于树遍历问题，而且对于我们做的其他一些事情也是如此。

The main idea is that we can't let our dabase get corrupted with uncomplete data.

主要思想是我们不能让我们的数据库被不完整的数据损坏。

采纳答案by Bruno Costa

That is possible, you can find a example here

这是可能的，你可以在这里找到一个例子

Or perhaps a transaction scope...

或者也许是交易范围......

http://msdn.microsoft.com/en-us/library/ms172152.aspx

http://msdn.microsoft.com/en-us/library/ms172152.aspx

回答by John Maloney

BeginTransaction is called off a ADO.NET collection object. The Command object needs this transaction (SqlTransaction object) assigned to it. Commit and Rollback are only called in the outer method.

从 ADO.NET 集合对象调用 BeginTransaction。Command 对象需要分配给它的这个事务（SqlTransaction 对象）。Commit 和 Rollback 只在外部方法中调用。

Check out this code. It works by re-using the SqlConnection and SqlTransaction objects. This is a classic Master>Details type of set up. The master type is ColumnHeaderSetwhich contains a property of

看看这个代码。它通过重新使用 SqlConnection 和 SqlTransaction 对象来工作。这是经典的 Master>Details 类型的设置。主类型ColumnHeaderSet包含一个属性

List<ColumnHeader>

List<ColumnHeader>

, which is the details (collection).

，这是详细信息（集合）。

Hope this helps. -JM

希望这可以帮助。-JM

public static int SaveColumnHeaderSet(ColumnHeaderSet set)
      //save a ColumnHeaderSet
      {       
         string sp = ColumnSP.usp_ColumnSet_C.ToString();    //name of sp we're using
         SqlCommand cmd = null;
         SqlTransaction trans = null;
         SqlConnection conn = null;

         try
         {
            conn = SavedRptDAL.GetSavedRptConn();  //get conn for the app's connString
            cmd = new SqlCommand(sp, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();
            trans = conn.BeginTransaction();
            cmd.Transaction = trans;   // Includes this cmd as part of the trans

            //parameters
            cmd.Parameters.AddWithValue("@ColSetName", set.ColSetName);
            cmd.Parameters.AddWithValue("@DefaultSet", 0);
            cmd.Parameters.AddWithValue("@ID_Author", set.Author.UserID);   
            cmd.Parameters.AddWithValue("@IsAnonymous", set.IsAnonymous);         
            cmd.Parameters.AddWithValue("@ClientNum", set.Author.ClientNum);
            cmd.Parameters.AddWithValue("@ShareLevel", set.ShareLevel);
            cmd.Parameters.AddWithValue("@ID_Type", set.Type);

            //add output parameter - to return new record identity
            SqlParameter prm = new SqlParameter();
            prm.ParameterName = "@ID_ColSet";
            prm.SqlDbType = SqlDbType.Int;
            prm.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(prm);

            cmd.ExecuteNonQuery();
            int i = Int32.Parse(cmd.Parameters["@ID_ColSet"].Value.ToString());
            if (i == 0) throw new Exception("Failed to save ColumnHeaderSet");
            set.ColSetID = i; //update the object

            //save the ColumnHeaderList (SetDetail)
            if (ColumnHeader_Data.SaveColumnHeaderList(set, conn, trans)==false) throw new Exception("Failed to save ColumnHeaderList");
            trans.Commit();

            // return ID for new ColHdrSet
            return i;
         }
         catch (Exception e){
            trans.Rollback();
            throw e;
         }
         finally{
            conn.Close();
         }
      }

 public static bool SaveColumnHeaderList(ColumnHeaderSet set, SqlConnection conn, SqlTransaction trans)
      //save a (custom)ColHeaderList for a Named ColumnHeaderSet
      {
         // we're going to accept a SqlTransaction to maintain transactional integrity
         string sp = ColumnSP.usp_ColumnList_C.ToString();    //name of sp we're using 
         SqlCommand cmd = null;
         try
         {
            cmd = new SqlCommand(sp, conn);     // re-using the same conection object
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Transaction = trans;            // includes the cmd in the transaction

            //build params collection (input)
            cmd.Parameters.Add("@ID_ColSet", SqlDbType.Int);
            cmd.Parameters.Add("@ID_ColHeader", SqlDbType.Int);
            cmd.Parameters.Add("@Selected", SqlDbType.Bit);
            cmd.Parameters.Add("@Position", SqlDbType.Int);

            //add output parameter - to return new record identity
            //FYI - @return_value = @ID_SavedRpt
            SqlParameter prm = new SqlParameter();
            prm.ParameterName = "@ID";
            prm.SqlDbType = SqlDbType.Int;
            prm.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(prm);

            //Loop
            foreach (ColumnHeader item in set.ColHeaderList)
            {
               //set param values
               cmd.Parameters["@ID_ColSet"].Value = set.ColSetID; 
               cmd.Parameters["@ID_ColHeader"].Value = item.ColHeaderID; 
               cmd.Parameters["@Selected"].Value = item.Selected;
               cmd.Parameters["@Position"].Value = item.Position;

               cmd.ExecuteNonQuery();
               int i = Int32.Parse(cmd.Parameters["@ID"].Value.ToString());
               if (i == 0) throw new Exception("Failed to save ColumnHeaderSet");
            } 
            return true;
         }
         catch (Exception e)
         {
            throw e;            
         }

      }