«

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/is...DataPoints/#S6

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