Many data components - use one transaction?

[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

 

[Guest]

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!
***************************

 

[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/

 

[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.

 

[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)