Transaction on functions

[mukesh]

Hi,
Suppose I have four different functions. All four update four
different tables. I want to execute these functions in a transaction.

TRANSACTION START
CALL A=FUNCTION1()
CALL B=FUNCTION2()
CALL C=FUNCTION3()
CALL D=FUNCTION4()
TRANSACTION END

Is it possible to do?

Thanks
Mukesh

 

[Marc Gravell]

Yup; old-school way is to pass an ADO.Net transaction (IDbTransaction) to
each method and have them enlist; a pain.
A more convenient way (in 2.0) is to use TransactionScope (refer to MSDN2),
which creates an ambient transaction; you use ("using") the transaction
scope around the 4 calls, and the ADO.Net calls enlist automatically; then
you commit. If an exception happens, then typically the scope is disposed
without commit, and so is rolled back.

Basically it is DTC without the overhead of COM+/MTS. Very tidy. Of course,
it needs DTC running, so not reliable on a client, but very handy at the
server. It also works best on SqlServer 2005 as it can then use promoteable
transactions - i.e. they start out as ADO.Net transactions, then become DTC
transactions if (and only if) a second server gets involved. With SqlServer
2000 it panics and jumps straight to DTC.

Hope this helps,

Marc

 

[Barry Kelly]

Suppose I have four different functions. All four update four
different tables. I want to execute these functions in a transaction.

TRANSACTION START
CALL A=FUNCTION1()
CALL B=FUNCTION2()
CALL C=FUNCTION3()
CALL D=FUNCTION4()
TRANSACTION END

In C#:

---8<---
using (TransactionScope scope = new TransactionScope())
{
Function1();
Function2();
Function3();
Function4();
scope.Complete();
}
--->8---

-- Barry

 

[mukesh]

Hi,
Thanks it works. Is there any way to do the same in .NET 1.1
framework?

Thanks
Mukesh

 

[Barry Kelly]

Thanks it works. Is there any way to do the same in .NET 1.1
framework?

See the other poster. It's more awkward in .NET 1.1.

-- Barry