Orphaned Oracle database sessions with OracleDataAdapter

  • Thread starter Thread starter Tom Wells
  • Start date Start date
T

Tom Wells

I just discovered that my ASP.NET applications are leaving huge numbers of
orphaned sessions on the Oracle server even though I issue a close() command
to the connection in the dispose of all of my classes. Below is an example
of one of my classes and how I'm doing things. If someone could point out
what I'm doing wrong or explain what the problem is I would be most
grateful. Thanks!

Public Class GetDivisions

Implements IDisposable

Public IsDisposed As Boolean = False

Dim cn As OracleConnection

Sub New(ByVal sUserid as String, ByVal sPassword As String)

cn = New OracleConnection("Data Source=" & sDataBase & "; User Id=" &
sUserID & ";Password=" & sPassword & ";")

End Sub

Public Function GetData() As DataSet

Dim SQL As String

SQL = "select Distinct DIVISION_ABRV FROM EMP.EMP_WORK_INFO_VIEW"

Dim da As New OracleDataAdapter(SQL, cn)

Dim ds As New DataSet

da.Fill(ds)

Return ds

End Function

Public Sub Dispose() Implements System.IDisposable.Dispose

If IsDisposed = False Then

cn.Close()

GC.SuppressFinalize(Me)

IsDisposed = True

End If

End Sub

Protected Overrides Sub Finalize()

MyBase.Finalize()

Dispose()

End Sub

End Class
 
If you are implementing IDisposable, call Dispose on the connection, not
close.

Nice pattern:

Try
cn.Open()
'Do work here
Finally
cn.Dispose()
ENd Try

In some instances, you might also need a catch, but only if you are doing
something with the exception thrown. This pattern causes connection dispose
early on and is much better than adding all of the SuppressFinalize crap in
your classes.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
¤ I just discovered that my ASP.NET applications are leaving huge numbers of
¤ orphaned sessions on the Oracle server even though I issue a close() command
¤ to the connection in the dispose of all of my classes. Below is an example
¤ of one of my classes and how I'm doing things. If someone could point out
¤ what I'm doing wrong or explain what the problem is I would be most
¤ grateful. Thanks!
¤

If you ask me this is overkill. I don't see any reason for implementing IDisposable or Dispose with
respect to Oracle connections.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top