keeping one connection to DB or opening closing per need

Question

I looking for a best practice in following case, with reasons why each way is better.

I have one DB with about 10~20 client applications that connecting to one main DB server.

There can be about 200 calls from one client to the DB per minute, in really rare cases.

The application are multithreaded, about 20 threads per application.

What will be best practice here to keep only one connection to the DB per application and reuse it per application . OR opening new connections per needed call and close them fast.

We are working with oracle and sql-server.

Answer 1

The .NET oracle provider has built-in connection-pooling capabilities. Whenever you need a DB connection, create a new one do the work and release it immediately. The connection pooling will take care of reusing connections efficiently.

The best way to release the connection is through the using construct which will ensure that the connection is disposed, even if exceptions occur.

using(OracleConnection connection = ConnectionFactory.Create())
{
    connection.DoStuff();

} //connection.Dispose() called here.

Answer 2

The best practice is to dispose connection as soon as possible. Technically connection will not be closed, just returned to the pool and will be reused by other threads.

SQL Server Connection Pooling (ADO.NET)

Quote from MSDN:

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement.