Web Form and ADO.NeT Transaction

Discussion in 'ASP .Net' started by Lit, Aug 9, 2007.

  1. Lit

    Lit Guest

    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
     
    Lit, Aug 9, 2007
    #1
    1. Advertising

  2. Lit

    Aidy Guest

    Have a look here

    http://msdn.microsoft.com/msdnmag/issues/05/05/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, Aug 9, 2007
    #2
    1. Advertising

  3. Lit

    Lit Guest

    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/issues/05/05/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
    >>
    >>

    >
    >
     
    Lit, Aug 9, 2007
    #3
  4. Lit

    Aidy Guest

    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/issues/05/05/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, Aug 10, 2007
    #4
  5. Lit

    Lit Guest

    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/issues/05/05/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
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Lit, Aug 10, 2007
    #5
  6. Lit

    Aidy Guest

    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/issues/05/05/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, Aug 10, 2007
    #6
  7. Lit

    Lit Guest

    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/issues/05/05/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
    >>>>>>
    >>>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>
    >>

    >
    >
     
    Lit, Aug 10, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nita
    Replies:
    1
    Views:
    904
    Saravana
    Nov 20, 2004
  2. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    491
    Kevin Spencer
    Dec 17, 2004
  3. Replies:
    0
    Views:
    1,345
  4. Vencz Istv?n
    Replies:
    2
    Views:
    306
  5. CJM
    Replies:
    0
    Views:
    132
Loading...

Share This Page