Get connected, but don’t stay connected (A connection pooling error has occured)

Perhaps just some open doors being kicked in agian, but :

Thanks to the connection pool, connecting to a database is is fast. A sqlConnection.Open() gets you to the database in a snap and a sqlConnection.Close() will return the connection to the pool. This is all default behaviour, no code or configuration required. But the feature has a downside when your app does not behave well. If your app keeps a connection open after handling a request you can get into trouble. For instance : Requesting the same page again (and trying to reopen a connection which wasn’t closed in the former request) can produce a A connection pooling error has occured error message when your page uses several connections.

This is what works well in my apps

  • Open the connection in the page_load event.

  • Close the connection in the page_unload

If your app accesses its data in a componenet (which it should..)

  • Create the component in the page load

  • Dispose the componenet in the page_unload

  • Open the connection in the constructor of the component

  • Close the coneection in the componenets Dispose method

This is pretty simple and works very well. The connections will be open a very short time span, only in the prcess of creating a response. When your webserver has no requests to process no connections are open. To check if my apps behave well I do the following:

  • My working machine is not very powerfull, it has a limited disk and has to run VS, IE, outlook, IIS localhost,  and sql server all at the same time. When the app is behaving well this is not a problem. When the app does not close its connections the machine is brought to its knees very fast.

  • Use the windows performance monitor (perfmon.exe) to monitor the count of open connections. When the app is not well behaved you will see the count of open connections grow and grow.

Blog on, Peter

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Peter van Ooijen

    My DAL conists of component’s (the .net class). They expose data and the way to update that via xmldatasets and methods using them. The nice things about componenets is that you can drop dataadapters and the like on them. Good enough for me. For the time being..

  • Paul Wilson

    Or better yet, use a DAL that totally shields you from connections and closes them for you ASAP automatically. You may get a connection this way more than once on a page, but with pooling there is nothing but a miniscule amount of overhead, and you gain much more reliability and a better designed application since the GUI should not know anything about connections anyhow. Another possibility, just as good, is to use an O/R mapper and avoid all of the usual data constructs.