CODEWITHSUNDEEP
c#databaseoracle-databaseoptimizationconceptual
Frank
Frank

Reputation: 71

Best Practices using OracleConnection Objects

As I develop a C# app which uses an Oracle database to store and retrieve data, I see two possible ways to use OracleConnection objects:

Is one of these generally considered a better approach? Is there a third, more preferable option? Does one type of situation call for one and another another?

Upvotes: 1

Views: 1222

Answers (1)

WSC
WSC

Reputation: 1002

You don't need to worry about this too much if you utilize ConnectionPooling (enabled by default).

ConnectionPooling will add and remove connections as they're required or not used. You can configure how rapidly this happens and how many connections to maintain in the pool.

See this for reference: https://docs.oracle.com/cd/B19306_01/win.102/b14307/featConnecting.htm

In short, use your "Multiple Instances" description. I would do something like this for each function:

using (OracleConnection cnx = new OracleConnection(conn))
{
    cnx.Open();
    //Do stuff with connection/
}

As long as you don't explicitly disable ConnectionPooling in your conn connection string, you'll be using the pool.

Additionally, maintaining an open connection for a long time may run into issues if the connection every drops out (even when not being used), so you'd have to handle that and check if the connection is open within every function.

Upvotes: 2

Related Questions