Open one connection vs Open several

Another question i asked..

Closing a connec开发者_开发技巧iton in the "unload" method

Has piqued my interest in the subject. When relating to asp.net/c# I understand that there is an underlying expense in opening/closing database connections, I'm interested in whether it's more expensive to either...

• Open DB connection at the start of a request
• Do several operations
• close connection at end or the request

vs

• Open connection directly before any database command
• Close straight away (thus opening and closing connections several time during a request)

If anyone could provide any insight, or point me in the direction of some reading material on the matter, that would be awesome.

---

You may find the following links useful. They point to Microsoft's best practices implementing Data Access Layer:

1. Data Layer guidelines: http://msdn.microsoft.com/en-us/library/ee658127.aspx
2. Data Components design: http://msdn.microsoft.com/en-us/library/ee658119.aspx
3. About Connections specifically: http://msdn.microsoft.com/en-us/library/ee658127.aspx#Connections

I agree with Steve that, in most cases, you should keep connection open for the shortest time possible.

From my experience, I used to re-work code that was using opened connections for extended periods of time and that caused licensing problems (number of concurrent users).

---

You don't necessarily constrain the use of a database connection to single commands, but keep the duration between the open and the close relatively short.

The connections aren't really closed, but are released into the connection pool for re-use elsewhere. By quickly releasing the connection, you're actually reducing the number of connections that are opened.

---

It is not about whether opening connection for each request is more or less expensive. It is about best practices for the data access layer. And, it is not just about ASP.NET or C#. I would say your second option (generally only!) seems more reasonable. This way, you can have more control over closing the connection. Otherwise, you are running at risk of having too many connections open [idling]