Use of connection pooling, what is the right way?

You should always dispose of connections when you no longer need them. Disposing them doesn't close the underlying connection in the connection pool, rather it releases it back to the pool. Otherwise the connection will remain unavailable until the Garbage Collector gets around to clean the orphaned SqlConnection objects In order to utilize the connection pool to the max you should always include your connections in a using statement: using (SqlConnection con = new SqlConnection(someConnectionString) { ... } There is no penalty from frequently disposing the SqlConnection objects as the underlying connection remains open.

Next time you create a SqlConnection it will pick one of the connections available in the pool Passing the connection around in a multithreaded app is a bad idea, as multiple threads will try to use it at the same time with unpredictable results. For one thing, SqlConnection instance methods are NOT thread-safe. Worse, transactions are specific to connections and you can end up with one thread messing another's transaction.

Opening a new connection in each thread is much easier and safe As far as the overhead is concerned, you will always have an overhead when opening a new connection, even with a connection pool. A pool saves you the overhead of creating a new connection when there an unused open connection is already available. If you make sure you release your connections as soon as possible, you will not have to create too many connections You may be surprised how few connections your application really needs once you start using a connection pool.

You should always dispose of connections when you no longer need them. Disposing them doesn't close the underlying connection in the connection pool, rather it releases it back to the pool. Otherwise the connection will remain unavailable until the Garbage Collector gets around to clean the orphaned SqlConnection objects.In order to utilize the connection pool to the max you should always include your connections in a using statement: using (SqlConnection con = new SqlConnection(someConnectionString) { ... } There is no penalty from frequently disposing the SqlConnection objects as the underlying connection remains open.

Next time you create a SqlConnection it will pick one of the connections available in the pool. Passing the connection around in a multithreaded app is a bad idea, as multiple threads will try to use it at the same time with unpredictable results. For one thing, SqlConnection instance methods are NOT thread-safe.

Worse, transactions are specific to connections and you can end up with one thread messing another's transaction. Opening a new connection in each thread is much easier and safe.As far as the overhead is concerned, you will always have an overhead when opening a new connection, even with a connection pool. A pool saves you the overhead of creating a new connection when there an unused open connection is already available.

If you make sure you release your connections as soon as possible, you will not have to create too many connections. You may be surprised how few connections your application really needs once you start using a connection pool.

Thank you for an answer. However, I was not passing a connection object, only reference to the class where each method creates the SQLConnection. Also each thread that calls any method will create its own connection object.

– Snake Jul 13 '10 at 12:44 In this case it doesn't matter whether the connections are created inside one class or the other. – Panagiotis Kanavos Jul 13 '10 at 13:17.

The SQL client, whether; ODBC, JDBC, ADO. Net etc. Will control the pooling based on the connection string used to initiate a connection. The connection strings have a number of parameters that control how those connections are managed including pooling.

Although . Net specific the concepts discussed here apply across databases and programming languages: Connection Pooling for the . NET Framework Data Provider for SQL Server.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.