ADO.net Connection Pooling?

After testing different connection providers, I've found my answer The OleDb provider maintains its connection pool different than the SqlClient provider. Generally the SqlClient provider should create a new SqlConnection object each time you want to connect to the database. The connection should be closed or disposed.

This releases the underlying connection into the pool The OleDb provider however handled differently. Instead of instantiating a new connection each time, the same connection object should be used and disposed at the end of the application. If used the same way as the SqlClient connection, and error is thrown "Unspecified error".

Each OleDbCommand can be assigned the same OleDbConnection instance. If it is already being use, the underlying provider will assign a new connection to it. Doing this with the SqlClient provider it will generate an exception My original connection string was this: Data Source=....mdb;Provider=Microsoft.Jet.OLEDB.4.0; This turned out to fail after 128 connections... With a very friendly "Unspecified Error".

After researching connection pooling (not the error), this parameter needed to be added to turn on connection pooling OLE DB Services = -1 With this turned on, the connections behaved as expected and no limit was reached.

After testing different connection providers, I've found my answer. The OleDb provider maintains its connection pool different than the SqlClient provider. Generally the SqlClient provider should create a new SqlConnection object each time you want to connect to the database.

The connection should be closed or disposed. This releases the underlying connection into the pool. The OleDb provider however handled differently.

Instead of instantiating a new connection each time, the same connection object should be used and disposed at the end of the application. If used the same way as the SqlClient connection, and error is thrown "Unspecified error". Each OleDbCommand can be assigned the same OleDbConnection instance.

If it is already being use, the underlying provider will assign a new connection to it. Doing this with the SqlClient provider it will generate an exception. My original connection string was this: Data Source=....mdb;Provider=Microsoft.Jet.OLEDB.4.0; This turned out to fail after 128 connections... With a very friendly "Unspecified Error".

After researching connection pooling (not the error), this parameter needed to be added to turn on connection pooling. OLE DB Services = -1; With this turned on, the connections behaved as expected and no limit was reached.

I would honestly try to refactor such that you don't need to have two open readers at one time. You're opening yourself up to increasing the chances of deadlocks in your database by holding on to resources for longer than it takes it actually to read. This a problem sometimes referred to as SELECT 1 + N: for each row returned in the first select, you're performing an additional select.

You will need to refactor to fix this, but it all depends on what kind of data you're trying to read.

Actually, this was just a contrived example to focus on the connection. The real world scenario is that I have a long running background process accessing the database. While the user is doing searches.

I believe this example captures the focus of dealing with releasing connections manually. – Sam Jul 27 '10 at 16:51.

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.