Web Form and ADO.NeT Transaction

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..
LitHave a look here
http://msdn.microsoft.com/msdnmag/i.../DataPoints/#S6
"Lit" <sql_agentman@.hotmail.com> wrote in message
news:O0RbXAh2HHA.5316@.TK2MSFTNGP04.phx.gbl...
> 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
"Aidy" <aidy@.xxnoemailxx.com> wrote in message
news:l7mdnYVpI8eZSSfbRVnyiwA@.bt.com...
> Have a look here
> http://msdn.microsoft.com/msdnmag/i.../DataPoints/#S6
> "Lit" <sql_agentman@.hotmail.com> wrote in message
> news:O0RbXAh2HHA.5316@.TK2MSFTNGP04.phx.gbl...
>
Don't see what you can't re-use the same command object but I don't think
you'll particularly gain anything.
"Lit" <sql_agentman@.hotmail.com> wrote in message
news:eopM0eq2HHA.1188@.TK2MSFTNGP04.phx.gbl...
> 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" <aidy@.xxnoemailxx.com> wrote in message
> news:l7mdnYVpI8eZSSfbRVnyiwA@.bt.com...
>
Aidy,
By reusing the Command Object I can reduce the number of objects in the heap
memory?
Thanks for your help.
Lit
"Aidy" <aidy@.xxnoemailxx.com> wrote in message
news:euSdnX_2uokNuyHbnZ2dnUVZ8qqlnZ2d@.bt
.com...
> Don't see what you can't re-use the same command object but I don't think
> you'll particularly gain anything.
> "Lit" <sql_agentman@.hotmail.com> wrote in message
> news:eopM0eq2HHA.1188@.TK2MSFTNGP04.phx.gbl...
>
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" <sql_agentman@.hotmail.com> wrote in message
news:uFU30112HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Aidy,
> By reusing the Command Object I can reduce the number of objects in the
> heap memory?
> Thanks for your help.
> Lit
> "Aidy" <aidy@.xxnoemailxx.com> wrote in message
> news:euSdnX_2uokNuyHbnZ2dnUVZ8qqlnZ2d@.bt
.com...
>
>
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
"Aidy" <aidy@.xxnoemailxx.com> wrote in message
news:GM2dnfzruJAS4SHbnZ2dnUVZ8saonZ2d@.bt
.com...
> 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" <sql_agentman@.hotmail.com> wrote in message
> news:uFU30112HHA.3900@.TK2MSFTNGP02.phx.gbl...
>