Is a transaction opened?

[Guest]

How can I known if an SqlConnection has or not a transaction opened? I want
some code like this:

private SqlConnection conn;

private void Methode1()
{
SqlConnection conn = new SqlConnection(connectionText);
conn.Open();
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Transaction = cmd.Connection.BeginTransaction();
Methode2();
cmd.Transaction.Commit();
conn.Close();
}

private void Methode2()
{
SqlCommand cmd = new SqlCommand(commandText, conn);
if (conn don't has an opened transaction)
{
cmd.Transaction = cmd.Connection.BeginTransaction();
// Some program
}
}

Thank you!

 

[Marc Gravell]

How can I known if an SqlConnection has or not a transaction opened?
Well, an alternative is to use declarative transactions and make life
easy for yourself...

i.e.

using(TransactionScope ts = new TransactionScope()) {
// perform some database activities, either in one method or 27
ts.Complete();
}

(note that on exception, Dispose() is called without Complete(), so
the transaction is aborted)
The important things is that declarative transactions nest correctly -
so if I call a method within a TransactionScope that *itself* creates
a TransactionScope, then the inner scope actually just enlists in the
single (outer) transaction - so all is committed as one. Additionally,
this will work correctly with both single-server and distributed
transactions.

One minor caveat; on Sql-2000 and below this will use DTC even for
single-server transactions, which has a small runtime overhead. On
Sql-2005 and above it will use an SQL transaction until it is forced
to escalate to DTC.

Marc

 

[Marc Gravell]

terminology slipup; I may have confused my words - this isn't
necessarily "declarative" - however, it is still very simple and
effective to do!

Marc