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

Have a look here

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

"Lit"  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"  wrote in message 
news:l7mdnYVpI8eZSSfbRVnyiwA@bt.com...

> Have a look here
>
> http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/#S6
>
> "Lit"  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
>>
>>
>
> 

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

"Lit"  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"  wrote in message 
> news:l7mdnYVpI8eZSSfbRVnyiwA@bt.com...
>> Have a look here
>>
>> http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/#S6
>>
>> "Lit"  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,

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

Thanks for your help.

Lit

"Aidy"  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"  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"  wrote in message 
>> news:l7mdnYVpI8eZSSfbRVnyiwA@bt.com...
>>> Have a look here
>>>
>>> http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/#S6
>>>
>>> "Lit"  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
>>>>
>>>>
>>>
>>>
>>
>>
>
> 

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"  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"  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"  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"  wrote in message 
>>> news:l7mdnYVpI8eZSSfbRVnyiwA@bt.com...
>>>> Have a look here
>>>>
>>>> http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/#S6
>>>>
>>>> "Lit"  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,

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"  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"  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"  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"  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"  wrote in message 
>>>> news:l7mdnYVpI8eZSSfbRVnyiwA@bt.com...
>>>>> Have a look here
>>>>>
>>>>> http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/#S6
>>>>>
>>>>> "Lit"  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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
>