Orphaned Oracle database sessions with OracleDataAdapter

12 Jan 2006 2:47 PM

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

12 Jan 2006 6:43 PM

Cowboy (Gregory A. Beamer) - MVP

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!
***************************

Show quote

"Tom Wells" wrote:

> 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
>
>
>

12 Jan 2006 8:22 PM

Paul Clement

On Thu, 12 Jan 2006 08:47:58 -0600, "Tom Wells" <twe***@les.com> wrote:

¤ 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)