Many data components - use one transaction?

28 Feb 2005 4:11 AM

Marty

I need to use three data components to write to three different tables
(same database). But they must participate in the same transaction
(all must succeed or rollback). At the same time, each data component
should be able to perform an update without a transaction if desired.
Not sure how to do this... I'm thinking of passing SqlConnection
(associated with SqlTransaction) object to each method as method
paramter. Each method would check the connection parameter - if not
null, participate in transaction (let "caller" do commit & rollback,
etc) else not part of transaction (method is then responsible for
commit & rollback, etc). Is this about the only way to accomplish
this? COM+ not an option at this time. Ideas appreciated!
--Marty

28 Feb 2005 4:01 PM

Cowboy (Gregory A. Beamer) - MVP

Start the transaction higher up the stack and pass down to each object.
Either that, or you will have to create your own transaction manager.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quoteHide quote

"Marty" wrote:

> I need to use three data components to write to three different tables
> (same database). But they must participate in the same transaction
> (all must succeed or rollback). At the same time, each data component
> should be able to perform an update without a transaction if desired.
> Not sure how to do this... I'm thinking of passing SqlConnection
> (associated with SqlTransaction) object to each method as method
> paramter. Each method would check the connection parameter - if not
> null, participate in transaction (let "caller" do commit & rollback,
> etc) else not part of transaction (method is then responsible for
> commit & rollback, etc). Is this about the only way to accomplish
> this? COM+ not an option at this time. Ideas appreciated!
> --Marty
>
>

28 Feb 2005 4:15 PM

Sahil Malik

You don't need and shouldn't even use COM+ for this.

You can simply not associate the SqlCommands with a SqlTransaction object if
you wish your operation to be non transactional. Thus you can reuse the
SqlCommand logic in a non transactional scenario individually, or
transactional scenario in a joint fashion.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

Show quoteHide quote

"Marty" <mcdon***@wsdot.wa.gov> wrote in message
news:1109563901.077684.167040@f14g2000cwb.googlegroups.com...
> I need to use three data components to write to three different tables
> (same database). But they must participate in the same transaction
> (all must succeed or rollback). At the same time, each data component
> should be able to perform an update without a transaction if desired.
> Not sure how to do this... I'm thinking of passing SqlConnection
> (associated with SqlTransaction) object to each method as method
> paramter. Each method would check the connection parameter - if not
> null, participate in transaction (let "caller" do commit & rollback,
> etc) else not part of transaction (method is then responsible for
> commit & rollback, etc). Is this about the only way to accomplish
> this? COM+ not an option at this time. Ideas appreciated!
> --Marty
>

28 Feb 2005 11:56 PM

Val Mazur

Hi,

As soon as all the components use same connection with the opened
transaction against it, then all of the will participate in same
transaction. If you specify another connection for your component than it
will be treated as another client. But you should be aware that holding open
transaction for long period of time could lead to
the locking problems.

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

Show quoteHide quote

"Marty" <mcdon***@wsdot.wa.gov> wrote in message
news:1109563901.077684.167040@f14g2000cwb.googlegroups.com...
>I need to use three data components to write to three different tables
> (same database). But they must participate in the same transaction
> (all must succeed or rollback). At the same time, each data component
> should be able to perform an update without a transaction if desired.
> Not sure how to do this... I'm thinking of passing SqlConnection
> (associated with SqlTransaction) object to each method as method
> paramter. Each method would check the connection parameter - if not
> null, participate in transaction (let "caller" do commit & rollback,
> etc) else not part of transaction (method is then responsible for
> commit & rollback, etc). Is this about the only way to accomplish
> this? COM+ not an option at this time. Ideas appreciated!
> --Marty
>

3 Mar 2005 4:26 PM

Marty

I've decided to use a "manager" class called "DB" to manage things. It
is responsible for invoking each data component's method, using
transaction if required. Each data component is responsible for
building the command, then the DB executes it. Example:
class DB
AddEmployee()
     (open connection, begin transaction here)
        cmd = Employee.BuildAddCommand()
        (assign connection & transaction to command)
        cmd.ExeuteNonQuery()
        cmd[] = Dependends.BuildAddCommand()
        (assign connection & transaction to command[])
        cmd.ExecuteNonQuery() for all commands
        cmd = PayInfo.BuildAddCommand()
        (assign connection & transaction to command)
        cmd.ExecuteQuery()
     (commit transaction here)