SqlConnection Object in Application Variable OR Session Variable OR open/close

[pratham]

Hi and regards

What is best method to have an connection to sql, iam using sqlserver

1. have an sqlconnection open when the application start event in
global.asax so that i dont have to open it in each method where i
require to open an connection

2. open an connection in seesion so that, it is available in session of
the user thorugh out

3. open an connection in each method and close after the requried
operation is completed

which is better

as per the 3rd method ... doest openning and closing in each method is
a bad??

Comments expected

 

[Marina Levit [MVP]]

1. This is the most horrible method. All users would share the same
connection object, which means, you are going to have multiple threads
trying to use it at once, which means your app will crash instance under any
kind of load.

2. This is slightly better, but still really bad. First off, it means you
have an open connection the entire lifetime of a session. This severely
limits the number of concurrent users, and performs badly.

3. This is how you should handle it. Connections are pooled by default,
opening and closing them is relatively inexpensive. Additionally, you will
get much better performance by not keeping unecessary connections open, 99%
of which time they are not being used.

So yes, #3 is the way to go.

 

[pratham]

Thanks Marina for that fast and detailed reply Cheers

 

[sloan]

Correct (on an earlier post) that #1 is really bad. #2 isn't good either.


An environment like the web, you especially want to

"Open Late" and "Close asap" model.

Open and close as fast as you can.