CODEWITHSUNDEEP
asp.netvb.netoracle-databaseoracle11g
causita
causita

Reputation: 1708

Oracle connection pooling in ASP.net

I have an old intranet website written with vb.net and using Oracle.ManagedDataAccess mostly doing read operations from Oracle database 11g.

My db connection code is as follows. 

 Public Shared Function MyDBConnection(ByVal command_text As String, ByVal connstring As String, ByVal ParamArray parameters As OracleParameter()) As DataTable
        Dim OraCommand As New OracleCommand
        Dim tmp As New DataTable

        Using ORAconnection = New OracleConnection(ConfigurationManager.ConnectionStrings(connstring).ConnectionString)
            OraCommand.CommandText = command_text

            If parameters IsNot Nothing AndAlso parameters.Length > 0 Then
                For Each p In parameters
                    OraCommand.Parameters.Add(p.ParameterName, p.OracleDbType, p.Size).Value = p.Value
                Next p
            End If
            OraCommand.Connection = ORAconnection
            Try
                ORAconnection.Open()
                tmp.Load(OraCommand.ExecuteReader())
            Catch ex As OracleException

            End Try
        End Using

        Return tmp

    End Function

And my oracle connection string is like this

<add name="ConnectionString" connectionString="User Id=userid;Password=userpadd;Data Source=servername:port/port_dp"/>

I was testing if my connections to the database were closing properly but it looks like connections on the database stayed open after being closed on my code. Eventually they close way after my query completed about 2 minutes, 10 minutes or hours later.

Is this connection pooling at work? or if there is something wrong with my code?

After reading about oracle pooling, it looks like the application should be reusing the same opened connection on the DB but in my case it looks like is opening new connections anyway.

So my question is, should I disable pooling on my connection string to make sure all connections open/close and not have connections lingering on the DB?

Upvotes: 0

Views: 1608

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

No, you should not disable connection pooling. In .NET connection pooling is managed by a mechanism outside of your reach, and you should proceed as if it is not there: open and close your connections as you normally would (i.e. at the beginning and end of every set of operations that you wish to enroll in a transaction/every set of ops that defines a good "unit of work" such as running a report, updating a table etc

The action of opening and closing connections in your code simply leases the from/returns them to the pool. .NET will manage the rest regarding maintaining a cache of some open connections to the db

Upvotes: 1

the_lotus
the_lotus

Reputation: 12748

Yes, there's pooling. The number of connection might have to do with the default value of min and max pool size. Also, OracleCommand does have a Dispose method that you are not calling.

Added: I see your empty catch statement, you don't need it when using "Using" (or ever). The Dispose will still be called if there's an exception.

Upvotes: 0

Related Questions