Web Form and ADO.NeT Transaction

  • Thread starter Thread starter Lit
  • Start date Start date
L

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,
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
 
Don't see what you can't re-use the same command object but I don't think
you'll particularly gain anything.
 
Aidy,

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

Thanks for your help.

Lit
 
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.
 
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
 
Back
Top