Transactions and Closing Connection Question 1

[BG12424]

I have a function which I open a connection object and assign to a command object. Does the connection object need to also be closed in addition to the command object's connection or is the iCon closing by reference of closing the iCmd.Connection.Close() method? Thanks (see below)

Public Function ExecuteNonQuery(ByVal dict As IDictionary) As Boolean Implements IDataAccess.ExecuteNonQuery

Dim success As Boolean = True
Dim iCon As IDbConnection = GetConnection(FWPortal.Globals.Configuration.Profile.GetUserAccountConnectionString, DataProvider.SQL)
Dim iCmd As IDbCommand
iCon.Open()

Dim iTxn As IDbTransaction
Dim result As Integer
iTxn = iCon.BeginTransaction

Try
Dim iEnum As IEnumerator = dict.GetEnumerator
While iEnum.MoveNext
iCmd = CType(CType(iEnum.Current, DictionaryEntry).Value, IDbCommand)
iCmd.Connection = iCon
iCmd.Transaction = iTxn
iCmd.ExecuteNonQuery()
End While

iTxn.Commit()
Catch ex As Exception
iTxn.Rollback()
Throw ex
success = False
Finally
====================================================================================
We open a connection “iCon” so we can assign it to a transaction, then assign it to the iCmd object, which we close here in the Finally block
Do we need to also close the “iCon” object or are doing this (by reference) when closing the iCmd.Connection.Close()?
If iCon.State <> ConnectionState.Closed Then
iCon.Close()
End If
====================================================================================

If iCmd.Connection.State <> ConnectionState.Closed Then
iCmd.Connection.Close()
End If
End Try

Return success
End Function


regards,
Brian

http://www.newleaftechinc.com

 

[jmeckley]

the command requires a reference to the database connection, but the command itself should only be concerned with executing sql, not managing the life of the connection.

My signature has a link to an FAQ about db connection management for web apps. this may help shed some light on the subject for you.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[BG12424]

Thanks Jason, I did review your FAQ. Very nice.

Closing the connection through the command object seems to be closing the connection (by reference) though, even though it might not be best practice? As a result, should I be calling iCon.Dispose() afterward?




regards,
Brian

http://www.newleaftechinc.com

 

[jmeckley]

yes, you can close the connection via the command, but that's not recommended. it's like flying from NY City to Miami via LAX. you could, but why would you?

This also opens the topic of design principles like Law of Demeter, DRY (Don't Repeat Yourself) and Single Responsibility.

There are also concepts about who/when/how to manage the disposal of objects.
If a service is injected into the current object the current object should not manage it's scope. That is done by another object.
If a service is instantiated within the object the object will control it's scope.
If a service uses a factory class/method to instantiate the object it can control it's scope.

GetConnection() implies it is only retrieving the current connection (not creating a new one). there for you the consuming code should not close/dispose the connection because another object may also be consuming the GetConnection() member. managing the creation/opening/disposing of the connection would be managed by another object.

if the member was named CreateConnection() managing the scope of the connection within this method would be reasonable (although not recommend). It's a minor semantic change, but has a large impact on reading the code and understanding how it works.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[BG12424]

Thanks! I have connection piling up in a pool, so onto other research on when connections are not being closed properly.

regards,
Brian

http://www.newleaftechinc.com

 

[jmeckley]

disposing of a connection should clean any potential messes. I'm not 100% sure on connection pooling. All I know is if the connection is disposed, the .net environment will handle the rest.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190