Another question i asked.. Closing a conneciton 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: Data Layer guidelines: http://msdn.microsoft.com/en-us/library/ee658127.aspx Data Components design: http://msdn.microsoft.com/en-us/library/ee658119.aspx 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).

c#asp.netsql

Mikey Hogarth

Reputation: 4702

Open one connection vs Open several

Another question i asked..

Closing a conneciton 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

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.

Upvotes: 3

Answers (3)

user847210

Reputation:

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]

Upvotes: 1

Igor Turman

Reputation: 2205

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

Data Layer guidelines: http://msdn.microsoft.com/en-us/library/ee658127.aspx
Data Components design: http://msdn.microsoft.com/en-us/library/ee658119.aspx
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).

Upvotes: 3

Steve Morgan

Reputation: 13091

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.

Upvotes: 3

Open one connection vs Open several

Answers (3)

Related Questions