Global sqlconnection

  • Thread starter Thread starter Matt MacDonald
  • Start date Start date
M

Matt MacDonald

Hi all,
I have an asp.net application that is going to be hit by a lot of users
and we want to minimize the # of database connection opens and closes.
Here's what I'm thinking I can do. I want to declare a connection object in
the global.asax file as public shared and open it once (in the
application_start method) and close it once (in the application_end) method.
On MSDN is claims that public shared connections are thread safe, so I'm
thinking this should work. If anyone knows one way or the other please let
me know and if there is a better way to do it please let me know that too.
I just don't want to deploy it this way and then find out in the middle of
the night that users are getting thrown off the site because the sessions
are clashing.

Thanks in advance,
Matt
 
¤ Hi all,
¤ I have an asp.net application that is going to be hit by a lot of users
¤ and we want to minimize the # of database connection opens and closes.
¤ Here's what I'm thinking I can do. I want to declare a connection object in
¤ the global.asax file as public shared and open it once (in the
¤ application_start method) and close it once (in the application_end) method.
¤ On MSDN is claims that public shared connections are thread safe, so I'm
¤ thinking this should work. If anyone knows one way or the other please let
¤ me know and if there is a better way to do it please let me know that too.
¤ I just don't want to deploy it this way and then find out in the middle of
¤ the night that users are getting thrown off the site because the sessions
¤ are clashing.

Unfortunately this type of implementation doesn't scale and a single global connection object isn't
going to cut it.

I would advise that you use the built-in implementation of connection pooling instead.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
You can't do that.

If 3 users hit your site at the same time, they will all try to use the
connection at the same time. One of them will be able to do it - the other 2
will get an error saying the connection is in use.

Connection pooling is very efficient. In each method that needs a
connection, create and open a new one, then close it. You will still have
great performance from the SQL access code.
 
I totally agree. For ASP applications the connection pooling mechanism does
what you're going to try to do with your own code--it leaves the connection
open so it's just a matter of reconnecting it to the new client. While your
application "closes" the connection, you're really just putting it back into
the pool.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Would it work any better if I put it in the session_start and end methods?
Or would you still recommed using the connection pools?

Thanks,
Matt
 
Matt MacDonald said:
Would it work any better if I put it in the session_start and end methods?
Or would you still recommed using the connection pools?

Scoping connections to sessions would be wasteful. You may well have
thousands of concurrent sessions, and you shouldn't allocate a SqlConnection
to each one.

Again, just use the built-in connection pool.

David
 
That would be a terrible idea - it has way too many downsides. Just use
connection pooling, i.e. indiscriminately open and close SqlConnection
objects as long as you follow the following 2 rules

a) The connection string never changes
b) You open as late as you can and close as early as you can. // of course,
don't forget to explicitly close SqlConnection.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Gotcha, thanks
David Browne said:
Scoping connections to sessions would be wasteful. You may well have
thousands of concurrent sessions, and you shouldn't allocate a
SqlConnection to each one.

Again, just use the built-in connection pool.

David
 
Matt,

Do you know what gives obviously so many misunderstanding in this newsgroup.

If you are talking about a global sqlconnection than everybody is thinking
about a really open connection.

In my opinion is creating a global sqlconnection object not any problem as
long as you open and close that directly when it is used

Creating a shared sqlconnection is not any problem in winforms, however not
such a good idea in a webform application

Just my thought,

Cor
 
Actually, they are right. I wanted to only open the connection in
session_start, and close it in session_end. But I see now that that is not
a good idea.

Thanks though,
Matt
 
Back
Top