Good practice to open/close connections in an asp.net application?

Question

I've been working on a web application in ASP.net. My application has several pages and all of them need to display tables that are populated by a database. Right now what I'm doing is, on each page, I'm opening a database connection, executing the query specific to that page, and closing the db connection. So this happens each time the user clicks a link to go to a new page or clicks a form control like the grid page.

I was wondering if this was a disaster from the performance point of view. Is there any better way to do this?

Answer 1

Almost universally, database connections should be handled as follows: Open as late as possible, and close as soon as possible. Open and close for multiple queries/updates... don't think leaving it open saves you anything. Because connection pooling generally does a very good job for you of managing the connections.

It is perfectly fine to have a couple/few connections opened/closed in the production of a single page. Trying to keep a single connection open between page views would be quite bad... don't do that under any circumstances.

Basically, with connection pooling (enabled by default for almost all providers), "closing" a connection actually just releases it back to the pool to be reused. Trying to keep it open yourself will tie up valuable connections.

Answer 2

That is exactly how you want it to be. A database connection should only be opened when necessary and closed immediately after use.

What you may want to look at, especially if performance is a big issue for you, is caching. You may want to cache the entire page, or just parts of a page, or just the data that you would like displayed on your pages. You will save a lot of database trips this way, but you would have to now consider other things like when to update your cache, caching for different users, etc.