CODEWITHSUNDEEP
vb.netoracle-databaseodp.net
WSC
WSC

Reputation: 992

Optimal way to create and use ODP.NET connection

I am trying to understand what the most efficient yet maintainable way to create and use connections with ODP.NET.

My standard approach to writing functions in VB.NET that use ODP.NET is something like this:

Function GetDataTable() As DataTable

    Using Connection = New OracleConnection(connectionStringNHH)
        Using Command As OracleCommand = Connection.CreateCommand()
            Using DataAdapter As New OracleDataAdapter()

                'Do stuff, fill dt

            End Using
        End Using
    End Using

    Return dt

End Function

However, as per the Oracle documentation:

logging in and out of the database is an extremely resource-intensive operation [1]

Does creating and disposing of the connection object log in and out of the database every time this function is called, or does the session persist application-wide somehow?

If not, should I use a public connection object through my application? That seems bad practice to me for a number of reasons.

Upvotes: 0

Views: 469

Answers (1)

Christian Shay
Christian Shay

Reputation: 2635

By default, you get a connection pool inside of OracleConnection. Connection lifetime and corresponding sessions open with the database will be controlled by the pool. See the ODP.NET doc for all the details.

The best practice is to make sure these connections and other oracle objects get closed and disposed.

Here's a best practice slide deck that should help:

https://www.oracle.com/technetwork/topics/dotnet/tech-info/oow18dotnetperfbp-5212811.pdf

Upvotes: 1

Related Questions