Perhaps just some open doors being kicked in agian, but :
Thanks to the ado.net connection pool, connecting to a database is asp.net 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, asp.net 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