Transactions

[martin]

Hi,

I have three database Inserts that must be proformed as part of a
transaction.
I already have three seperate functions that perform each database insert
seperatly. The trouble is each function opens and closes it's own database
connection.
does this mean that transaction can't be used in this senario, from my basic
tests it would appear they can't.

I guess what I am asking is

does a database transaction have to be performed as part of the same
connection
for example open connection -- do three seperste inserts to seperate
tabels -- close the connection

or is it possible to have three seperate inserts each performed within its
own seperate connection as part of a tranaction.
after all how can a transaction be "rolled back" if it has taken place
inside a connection that is now closed.

may be a better design is called for in the future.

any help and advice is appreciated.

cheers

martin.

 

[Ivan Demkovitch]

How about Stored Procedure?
This would be the best way to do it naturally (if you use SQL Server)

 

[Jeff Siver]

You do have a few choices but all will require some degree of coding change:

1) Open the connection & transaction and pass them to the routines that do
the three database Inserts. After the last routine is complete, commit the
transaction, and close the connection.

2) Create a sub-class that provides command objects to your other classes.
This subclass would open the connection (once), create the transaction,
createa command object and associate the command object with the connection
& transaction objects. You will need to make this object available to the
three routines that do the inserts.

3) As Ivan said, do the work inside of a stored proc (available in most
databases other than Access).

HTH,

Jeff