Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

.Net ADO.Net SqlClient.SqlConnection connection pool clear

Posted on 2007-07-25
10
Medium Priority
?
1,127 Views
Last Modified: 2008-09-12
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 ?
0
Comment
Question by:ullfindsmit
  • 4
  • 4
  • 2
10 Comments
 
LVL 14

Expert Comment

by:shahprabal
ID: 19565385
ADO.NET 2.0 provides two static methods for doing this.

SqlConnection.ClearPool( SqlConnectionObject ).
SqlConnection.ClearAllPools().
0
 
LVL 12

Author Comment

by:ullfindsmit
ID: 19565413
forgot to mention, this is in 1.1
0
 
LVL 12

Author Comment

by:ullfindsmit
ID: 19565442
is there a way to force the connection object to dispose after a certain time ?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 14

Accepted Solution

by:
shahprabal earned 1000 total points
ID: 19565453
0
 
LVL 14

Expert Comment

by:shahprabal
ID: 19565463
myConnection.Close()
 myConnection = Nothing
0
 
LVL 12

Author Comment

by:ullfindsmit
ID: 19565517
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
 
LVL 25

Expert Comment

by:dstanley9
ID: 19565575
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
 
LVL 25

Assisted Solution

by:dstanley9
dstanley9 earned 1000 total points
ID: 19565607
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
 
LVL 14

Expert Comment

by:shahprabal
ID: 19566499
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
 
LVL 12

Author Comment

by:ullfindsmit
ID: 19566667
I currently disabled connection pooling
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Loops Section Overview
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question