Close connection in webApp explicitly?

[Klaus Jensen]

Hi!

If I have a class, and I have a connection property, which I use to access
the database...

How do you make sure, the connection is returned to the connection pool?
Must I make the connection public and explicitly close it, when I am done
with the class? Or is it enough to add Me.connection.close in Protected
Overrides Sub Finalize()??

Fx:

Public Class Forhandler

Private _strConnectionString As String =
ConfigurationSettings.AppSettings("Connectionstring")

Private Shared _cnnConnection As SqlConnection

Private Shared Property Connection() As SqlConnection

Get

If Not _cnnConnection.State = ConnectionState.Open Then

_cnnConnection.Open()

End If

Return _cnnConnection

End Get

Set(ByVal cnnConnection As SqlConnection)

_cnnConnection = cnnConnection

End Set

End Property

'..Some properties

Public Sub DoStuffInTheDatabase()

''Using me.connection

End Sub

Public Sub DoMoreStuffInTheDatabase()

''Using me.connection

End Sub

Protected Overrides Sub Finalize()

Me.Connection.Close()

MyBase.Finalize()

End Sub

End Class

 

[Norm Yuan]

I'd open and close Connection in method level, That is, object itself does
not hold a connection open, instead it only holds a ConnectionString.

IMO it is OK, in web app, to hold a connection open in a object and close it
when you are done with the object, IF the object's lifespan is limited in a
page. After all, when a page is sent out, the object is gone and the
connection is handed back to pool. But if you are going to store your object
in Session/Cache, the object may live long time before Session/Cache
expires, thus, your object hold a connection unnecessarily long. It
obviously is a bad thing.