open connections

[Mark]

When I develop a web app, I open and close connections on each page,
executing multiple commands as necessary within the single open connection.
However, I recently ran into a group of people that create a global
connection for their web applications and share it across
sessions/users/pages. Both models "work", but the second method makes me
cringe ... unfortunately I can't seem to put my cringe into words as to why
one should open and close connections locally.

I'd appreciate your feedback.

The reasons I can come up with:
1. Global variables should be avoided in general
2. Why keep a connection open all the time? Close it when it's not in use.
3. Multiple connections could potentially result in better performance for a
large number of concurrent users.

Others?

Thanks in advance!
mark

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi Mark,

In MSDN is clearly stated that you should open your connection at late as
possible and close it as soon as possible.

You should not keep a connection open and use it for all the threads of a
web app, this is TERRIBLE for performance, the simplest example I can think
of is the use of a SqlDataReader, in MSDN you can read :

While the SqlDataReader is in use, the associated SqlConnection is busy
serving the SqlDataReader. While in this state, no other operations can be
performed on the SqlConnection other than closing it. This is the case until
the Close method of the SqlDataReader is called.

In other words only one DataReader can be used the connection, if one page
is using a datareader with that connection all other pages will have to
wait, in other words this solution does not escalate at all !!!


Do as you do now and close the connection at soon as possible!!!

Hope this help,

 

[Jeremy Ames]

One reason for leaving the connections open is pooling. You can pool the
connection and one user uses it a time. This works for companies that have
to buy licenses per seat instead of by processor to save money.

When I develop a web app, I open and close connections on each page,
executing multiple commands as necessary within the single open connection.
However, I recently ran into a group of people that create a global
connection for their web applications and share it across
sessions/users/pages. Both models "work", but the second method makes me
cringe ... unfortunately I can't seem to put my cringe into words as to why
one should open and close connections locally.

I'd appreciate your feedback.

The reasons I can come up with:
1. Global variables should be avoided in general
2. Why keep a connection open all the time? Close it when it's not in use.
3. Multiple connections could potentially result in better performance for a
large number of concurrent users.

Others?

Thanks in advance!
mark