.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 ?
LVL 12
ullfindsmitAsked:
Who is Participating?
 
shahprabalCommented:
ADO.NET 2.0 provides two static methods for doing this.

SqlConnection.ClearPool( SqlConnectionObject ).
SqlConnection.ClearAllPools().
0
 
ullfindsmitAuthor Commented:
forgot to mention, this is in 1.1
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ullfindsmitAuthor Commented:
is there a way to force the connection object to dispose after a certain time ?
0
 
shahprabalCommented:
myConnection.Close()
 myConnection = Nothing
0
 
ullfindsmitAuthor Commented:
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
0
 
dstanley9Commented:
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.
0
 
dstanley9Commented:
You're going to have to change 100 places either way - if you forcibly close all connections, then when your forms try to use the connection it will be closed and it will throw an exception.

Better design would be to not have every form hold a connection object, but create and dispose them as needed.  Connection pooling is used here by not creating a new underlying database connection each time, but taking one from the pool.
0
 
shahprabalCommented:
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...
0
 
ullfindsmitAuthor Commented:
I currently disabled connection pooling
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.