Best way to open connection in COM+ environment?

[ShIzO]

hi, here is the scenario:
we have Data.dll that has few data classes and a module w/ global
functions like the one to update database, lets call is: "UpdateDB"
(it takes SQL statement as parameter).

im looking for the best possible way to do this now, so is it better
to:

1) have that global function (UpdateDB) open and close DB connection
within itself

2) open connection at the begining of one of the data functions and
close it at the end, pass connection object to that global function
(UpdateDB)

thanks,
wojo.

[brucevde]

I prefer #2. Why? Well, at some point you are going to need to perform two (or more) updates in one of your data functions. If one of these updates fail you might need to rollback the ones that were successfull. Using #2 you now have the ability to make use of database Transactions.

There is a third option that you might want to consider as well. Its a combination of options 1 and 2. Your global update procedure can accept an Optional Connection object as a parameter. The procedure would then check to see if the Connection was valid. If not, open the connection (option 1) then proceed to do the update (option 2).

Hope this gives you some ideas.
Bruce

[alex_read]

We have a setup like this. This way a connection is opened in the procedure itself and you're not relying on the fact that there is an open connection for a start, secondly, the active connection object is only held in memory for the short time it is needed - not throughout the life of the application.

You can declare all your connection string etc as constants in a common module for each procedure to call which would cut down some coding for you...

Function xyz(b as variable) as variable
    open connection 
    do stuff to database
    close connection
End Function
 
Function zyx(a as variable) as variable
    open connection 
    do stuff to database
    close connection
End Function