Web Form and ADO.NeT Transaction

[Lit]

Hi,

using ADO.NET 2.0, VS.NET 2005, SQL2005
I need to execute several Stored Procedures under one transaction. Can I
use just one command/transaction or have to use multiple ( then commit or
rollback all )
How does it work with ADO.NET

Ado.Tran
SP1 Called
loop
SP2 Called
SP3 Called
loop
SP4 Called
loop
Sp5 Called
Etc... etc...

if any error
rollback transaction ( or all transactions )
else
committe Transaction ( or all Transactions )

Thanks for any help or other Ideas, needs also code links etc..


Lit

 

[Aidy]

Have a look here

http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/#S6

 

[Lit]

Aidy,
I have one question, but first thanks for the great link.

in Figure 8, I see the following code
...
oCmd1.Transaction = oTran;
oCmd1.ExecuteNonQuery();

oCmd2.Transaction = oTran;
oCmd2.ExecuteNonQuery();
...

Question is: Can I reuse the same command object? by setting the
command.Text to a different SP and Different Parameters instead of creating
different command objects.
would that work and is it more efficient.

Thanks again,

Lit

 

[Aidy]

Don't see what you can't re-use the same command object but I don't think
you'll particularly gain anything.

 

[Lit]

Aidy,

By reusing the Command Object I can reduce the number of objects in the heap
memory?

Thanks for your help.

Lit

 

[Aidy]

Yeah, but you'll need to clear out the old parameters collection. Maybe you
are using slightly less space, but you need to execute more commands.
Swings and roundabouts really.

 

[Lit]

Aidy,

you got a good point there. It is going to be a balancing game.
I will have to think about my domain of contexts ( small infinity ) and see
what would be best.

Thank you for the enlightenment.

Lit