How to clear connection pool?

[Srinivas Kollipara]

Hello all,
I am wondering how to clear the connection pools from C# program, because my
program everytime it runs , it needs 70 connections... so whenever my
project fails i wanted to reset connection pools, or else after 3 to 4 runs,
i am getting an error "The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached". then if i reboot it
will be ok, i dont want to rebbot machine for this reason.. i heard a while
back that this feature is included in .NET 2.0, can any one knows that it
has that feature. can some one help me on this, how to do it.

Thanks
Srinivas

 

[Patrice]

I don't see this behavior. Are you sure all connections are properly
returned to the pool ?

Patrice

 

[William \(Bill\) Vaughn]

I expect that when you're working with VS, the IDE "owns" the connections so
you need to end Visual Studio to get the connections to be released.
In ADO.NET 2.0 you can programmatically clear the pool.
However, that said, I question what you're doing that requires 70
connections! Most production applications I've seen use 2 to 4
connections--no more. If you aren't closing connections correctly, your
application is likely leaking connections...
See my article on using the connection pool on my site.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Srinivas Kollipara]

Hello,
Thanks for ur instant reply.. i am waorking on a multi threaded
application, which has 35 threads and each thread requires 2 connections.. i
am going to look into ur website, now..

thanks
srinivas

 

[Sahil Malik [MVP]]

Your app is leaking open connections. Fixing that is the solution.

..NET 1.1 cannot clear the connection pool. Sowwie !!

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync

 

[michaeltorus]

Make sure you are disposing of any connections, even if they fail. You should try wrap anything with a .Disopose() method in a "Using" statement as it gaurantees to call .Dispose, even if there is an error :

using (SQLConnection s = new SQLConnection)
{
// Do Stuff ...

}//dispose is called no matter what happens



or you could inplement it using "try", "catch" and "Finally", where you would dispose of your connection in the finally block ...

If you get this part right, connections will alwayus be releaased back into the pool regardless of whether they passed or failed ...

 

[Jeff Clausius]

Srinivas:

I can see where some people would *think* you are losing connections, but
from your description, that does not seem to be the case. One possible
solution is controlling the max size of your pool through the connect
string.

For instance, we have some customers using Vault server for about 150-200
users. In a couple instances, there have been pool connection problems
when 100+ people have been connecting to the database within the same
time frame. There are no connection leaks in the server. This was just
an instance of a large amount of users accessing the database.

HTH
Jeff Clausius
SourceGear

 

[Guest]

Jeff, how does one control the max size of a pool are you saying limit the
output results? Also, is there a max of results that can be returned? How do
I measure that if so.

 

[Jeff Clausius]

Srinivas:

This feature may or may not be configurable based on your back end
database.

For instance, in the example I gave, modifying the connect string used by
SQLConnection (SQL Server database), the pool size can be configured. It
can be turned on/off (Pooling), as well as controlled by 'Max Pool Size'
and 'Min Pool Size' attributes. For the SQL Server example, see
SQLConnection.ConnectionString within .Net Help.

HTH
Jeff Clausius
SourceGear

 

[Mark Ashton]

In V1.1, you can't force the SqlClient connection pools to be cleared. The
most you can do is return the connections to the pool and wait for them to
expire.