I have one BLL object X with method Y. Inside method Y (*) I call
DAL object(s).
Operation Y can include more then one DAL object (dalObj1.Write,
dalObj2.Write) so I create some kind of "token" with sql connection
reference and pass it to DAL object(s) involved in operation Y (**).
At some point in time (ambient) transaction will finish (with success or
failure) and emit TransactionCompleted event.
I am asking if I can grab that token reference with sqlconnection
reference in TransactionCompleted eventhandler and close connection
without side effects and problems?
(*) call(s) to DAL object(s) is/are inside transaction scope which is
configured to use ambient transaction
(**) I know that it is possible to write this code with "(using
SqlConnection) { write db }" inside each DAL object write method and
don't bother with connection closing problem but this will result in
distributed transaction (because I open N connections to the same SQL
server). Am I correct?
I hope explanation is better this time :)
Alvin Bruney [ASP.NET MVP] wrote:
Sorry for misunderstanding you. I don't understand what you are doing.
You seem to be sharing one connection object with clients as opposed to
object pooling a connection object among clients. There's a huge
difference here if I've understood you correctly. I don't know what the
behavior would be at this point.
To your design point, you shouldn't be passing opened connection around
because you place the closing responsibility on the caller - caller
doesn't have to honor it which implies a resource leak. The better
pattern is to grab the data from the connection, close the connection,
and return the data in a custom business object inside your DAL. There's
no possibility of a resource leak in that pattern.
