Closing Oracle connection in VB.NET

In my program I got multiple spots where I open a Connection to an Oracle database, read some lines via a stored procedure which returns me a cursor, put them in an IDataReader, close the connection and go on.

Now everything works fine till the point of closing the connection. I've watched the connections opened to the database via开发者_JAVA技巧 TOAD for Oracle and I figured out, that the database seems to keep an connection opened after I said m_Connection.Close and m_Connection.Dispose.

I use Oracle.DataAccess and get an error after a short time, that there are to many connections. I debuged my session and made sure, that vb performs the close() command and it does it, but the database not. Anyone got an idea how to fix this?

In .Net, you need to wrap database connections in a Try/Catch/Finally block, and always close the connections in the Finally section. There is a shorthand for this called a Using block. This means keeping a connection as member of a class (as you seem to be doing) is almost always the wrong way to go about it. .Net is optimized so that it's better to create a new connection and command object for each query.

DataReaders are a little special: if you return a datareader out of using block, the connection can be closed before your DataReader is done with it. In C#, I normally get around the problem with an iterator (yield return). Since VB.Net lacks support for this construct, I might use an Action(Of IDataRecord) instead, like so:

Public Sub MyOracleQuery(ByVal id As Integer, ByVal ProcessRecord As Action(Of IDataRecord))
    Dim sql As String = "SELECT <columns> FROM MyTable WHERE ID= @Id"
    Using cn As New OracleConnection("connection string"), _
          cmd As New OracleCommand(sql, cn)

        cmd.Parameters.Add("@Id", SqlDbTypes.Int).Value = id
        cn.Open()

        Using (rdr As IDataReader = cmd.ExecuteReader())
            While rdr.Read()
                ProcessRecord(rdr)
            End While
        End Using
    End Using
End Sub

You can now pass an anonymous method to this code when you call it:

Dim id As Integer
If Integer.TryParse(IDTextBox.Text, id) Then
    MyOracleQuery(id, _
        Function(r)
            ''#... Do something with each "r" here
        End Function _
    )
Else
    ''# Complain to user about invalid ID
End If

Note that this requires Visual Studio 2010 /.Net 4 for the mutli-line anonymous method. For older platforms, you'll want to declare the method.