public void Transaction_ScopeCommit_Test() { SessionFactory sf = new SessionFactory("ConnectionString"); if (sf != null) { using (ISessionTX ses = sf.OpenSession() as ISessionTX) { string str_val = DateTime.Now.TimeOfDay.ToString(); SqlClientCommand sqlCmdPadre= new SqlClientCommand("PadreUpdate", new SqlParameter[] { new SqlParameter("@Id", DbType.Int32) { Value = 0 }, new SqlParameter("@Nombre", DbType.String) { Value = "P0-" + str_val }}); List<SqlClientCommand> lst_SqlCmdUpd = new List<SqlClientCommand>(new SqlClientCommand[] { new SqlClientCommand("HijoUpdate", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = 0 }, new SqlParameter("@Id", DbType.Int32) { Value = 0 }, new SqlParameter("@Nombre", DbType.String) { Value = "P0.h0-" + str_val }}), new SqlClientCommand("HijoUpdate", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = 0 }, new SqlParameter("@Id", DbType.Int32) { Value = 1 }, new SqlParameter("@Nombre", DbType.String) { Value = "P0.h1-" + str_val }}), new SqlClientCommand("HijoUpdate", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = 0 }, new SqlParameter("@Id", DbType.Int32) { Value = 2 }, new SqlParameter("@Nombre", DbType.String) { Value = "P0.h2-" + str_val }}), new SqlClientCommand("HijoUpdate", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = 0 }, new SqlParameter("@Id", DbType.Int32) { Value = 3 }, new SqlParameter("@Nombre", DbType.String) { Value = "P0.h3-" + str_val }}), new SqlClientCommand("HijoUpdate", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = 0 }, new SqlParameter("@Id", DbType.Int32) { Value = 4 }, new SqlParameter("@Nombre", DbType.String) { Value = "P0.h4-" + str_val }}) }); int idPadre = 0; int idBase = 5; List<SqlClientCommand> lst_SqlCmdIns = new List<SqlClientCommand>(new SqlClientCommand[] { new SqlClientCommand("HijoInsert", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = idPadre }, new SqlParameter("@Id", DbType.Int32) { Value = idBase }, new SqlParameter("@Nombre", DbType.String) { Value = string.Format("P{0}.h{1}-{2}",idPadre, idBase, str_val) }}), new SqlClientCommand("HijoInsert", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = idPadre }, new SqlParameter("@Id", DbType.Int32) { Value = idBase+1 }, new SqlParameter("@Nombre", DbType.String) { Value = string.Format("P{0}.h{1}-{2}",idPadre, idBase+1, str_val) }}), new SqlClientCommand("HijoInsert", new SqlParameter[] { new SqlParameter("@Padre", DbType.Int32) { Value = idPadre }, new SqlParameter("@Id", DbType.Int32) { Value = idBase+2 }, new SqlParameter("@Nombre", DbType.String) { Value = string.Format("P{0}.h{1}-{2}",idPadre, idBase+2, str_val) }}) }); //using(TransactionScope ts = ses.GetTransactScope()) try { SqlClientUtility.ExecuteNonQuery(ses, sqlCmdPadre); SqlClientUtility.ExecuteNonQuery(ses, lst_SqlCmdUpd); SqlClientUtility.ExecuteNonQuery(ses, lst_SqlCmdIns); //ts.Complete(); // Transaction Manager will commit entire scope while disposing... Otherwise, will be RollBacked. } catch(SqlException ex) { System.Diagnostics.Trace.WriteLine("SqlException into Scope: " + ex.ToString()); } } sf.Close(); } }
public static void ExecuteNonQuery(ISessionTX p_sessionTX, SqlClientCommand p_command) { TraceLog.LogEntry("ExecuteNonQuery(): Command {0} {1}", p_command.CommandText, p_command.ParametersDescr); int res = -1; bool m_mustCloseSqlCnn = true; string m_tx = "Transact NONE"; IDbConnection m_sqlCnn = null; string strInfoExcep = String.Empty; long ticks_t0 = DateTime.Now.Ticks; if (Transaction.Current == null) m_sqlCnn= p_sessionTX.Connection; //p_sessionTX.ConnectionManager.GetConnection() as SqlConnection; // Root session connection will be used... else { TraceLog.LogEntry("ExecuteNonQuery(): Transaction.Current: LocalId {0}\t - Status {1}", Transaction.Current.TransactionInformation.LocalIdentifier, Transaction.Current.TransactionInformation.Status); m_sqlCnn = SqlClientUtility.GetTransactedSqlConnection(p_sessionTX); m_mustCloseSqlCnn = false; } SqlCommand sqlc = SqlClientUtility.CreateCommand(m_sqlCnn as SqlConnection, CommandType.StoredProcedure, p_command.CommandText, p_command.Parameters); if (p_sessionTX.IsInActiveTransaction) { ITransaction tx = (p_sessionTX as SessionExt).Transaction; TraceLog.LogEntry("ExecuteNonQuery(): p_sessionTX has an active Transaction (0x{0:X} WasCommitted= {1} WasRolledBack= {2}) Command will be enlisted to...", tx.GetHashCode(), tx.WasCommitted, tx.WasRolledBack); tx.Enlist(sqlc); m_tx = "Transact 0x" + sqlc.Transaction.GetHashCode().ToString("X"); m_mustCloseSqlCnn = false; } res = sqlc.ExecuteNonQuery(); if (m_mustCloseSqlCnn && sqlc.Connection.State == ConnectionState.Open) sqlc.Connection.Close(); double mseg = TimeSpan.FromTicks(DateTime.Now.Ticks - ticks_t0).TotalMilliseconds; TraceLog.LogEntry("ExecuteNonQuery(): Command Executed, {0} SqlConn 0x{1:X}. Time {2} mseg.", m_tx, sqlc.Connection.GetHashCode(), mseg); }