Transactions in C #

Question

In addition to this question: copy folder traversing the preorder tree I was wondering if it was possible to create a transaction containing different calls in the database.

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 do not know if this is possible. We use subsonic code for database calls. This is really important not only for the problem of traversing a tree, but also for some other things that we do.

The basic idea is that we cannot allow our dabase to receive corrupted incomplete data.

Answer 1

It is possible you can find an example here

Or perhaps a transaction scope ...

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

Answer 2

BeginTransaction is called from an ADO.NET collection object. The Command object needs this transaction (SqlTransaction object) assigned to it. Commit and Rollback are called only by an external method.

Check out this code. It works by reusing SqlConnection and SqlTransaction objects. This is a classic wizard> Detailed type of customization. The main type ColumnHeaderSet that contains the property

List<ColumnHeader>

which represents the details (collection).

Hope this helps. -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 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; } }