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);
}
