0

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:

  • One Instance: Initialize and open an instance at the beginning of the program, pass that object to every function which stores or retrieves data from the database, and close and dispose the instance at the end of the program.
  • Multiple Instances: Initialize and open an instance at the beginning of every function which stores or retrieves data from the database and close and dispose it at the end of the function.

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?

Frank
  • 61
  • 4

1 Answers1

1

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.

WSC
  • 903
  • 10
  • 30
  • Should I instantiate on every query the connection? `= new OracleConnection(conn)` – JRichardsz Mar 23 '22 at 22:10
  • Generally yes, as long as you wrap it in a `using` block. ConnectionPooling means you aren't really spinning up a new connection each time so it's not a big performance hit. The common exception is if you need to do multiple queries as part of a transaction, in which case you'll need to use the same `OracleConnection` object. You could also use the same connection if you've got some queries that you always do in sequence for a minor optimization. But don't worry about keeping a connection open in case you need it. Use it and close it. – WSC Mar 24 '22 at 23:10
  • I tried to reuse a connection as a singleton instance but after some successful executions, randomly appear this error https://stackoverflow.com/questions/33616302/odac-object-reference-not-set-to-an-instance-of-an-object - To fix that I had to back to instantiate on every query `new OracleConnection`. Thanks for your time. – JRichardsz Mar 25 '22 at 04:55