Link to home
Start Free TrialLog in
Avatar of ullfindsmit
ullfindsmitFlag for United States of America

asked on

.Net ADO.Net SqlClient.SqlConnection connection pool clear

I have a .net winform app where the sqlclient.sqlconnection objects are not being disposed.
Hence I have a bunch of open connections, which after some time cause the application to get the "Timeout expired.  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." error

What I want to do, is in my error handler, when I get this message, force the connection pool to be cleared. All open connections for the application be closed, and then continue.

Any ideas on how to do it ?
Avatar of shahprabal
shahprabal
Flag of United States of America image

ADO.NET 2.0 provides two static methods for doing this.

SqlConnection.ClearPool( SqlConnectionObject ).
SqlConnection.ClearAllPools().
Avatar of ullfindsmit

ASKER

forgot to mention, this is in 1.1
is there a way to force the connection object to dispose after a certain time ?
ASKER CERTIFIED SOLUTION
Avatar of shahprabal
shahprabal
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
myConnection.Close()
 myConnection = Nothing
prabal.
here is the problem

I have more than 100 forms which open a connectin and never close
I can go and manually close connection on all pages, I already know that
but I am thinking of a simpler solution where I just add the close condition on error handler
or when the connection gets created

I know the '100 place change' fix
I'm looking for an easier fix
Avatar of dstanley9
dstanley9

If they are not being closed then there is a reference to them in scope somewhere, which is preventing the Garbage Collector from finalizing them.  Perhaps a static member?  Or a property of a form that is left open for long periods of time.  

The concern with forcibly disposing connections that are still in scope is that you must always check for an open connection before doing anything with it, otherwise you'll get an exception.  

Best bet is to see who is holding on to those connection objects and dispose of them properly.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Other option is not to declare a different connection on 100 forms if they are connecting with the same parameters... just delcare a global connection with the same name as in all your forms and find and replace the declaration line of the connection in the forms...
I currently disabled connection pooling