what is the best way to manage about 2000 database connection / mssql+ asp.net

[laziers]

Hi there
Anybody knows what is the best way to manage creation 2000 of the
database connections at the same time?

Now Im doing it somethink like this:

using ( Connection conn = Connection.Create )
{
conn.Open();
//...
}

....but I thinks that isn't a good solution because every (of 2000)
visitor who enters to the site always gets a new connection. Every
refresing page creates a new connection.

In my opinion this solve isn't good. So, what is the best way to do it
right? Am I always must create a new connection for every page
refresing? Could I cache it...? or .. what?

 

[Guest]

In my opinion this solve isn't good. So, what is the best way to do it
right? Am I always must create a new connection for every page
refresing? Could I cache it...? or .. what?

You should open the connection as late as possible, and close the
connection as early as possible.

..NET has internal connection pooling, so there is no need for you to manage
the connection pool unless you're doing something out of the ordinary.

 

[Scott Roberts]

The SqlConnection class (System.Data.SqlClient.SqlConnection) provides
automatic connection pooling. The other "built-in" connections may as well.
Check the help files for details then post again if you have specific
questions.

If you are using some sort of custom data connection, then you may need to
implement your own connection pooling. I imagine a quick Google search on
"asp.net connection pooling" will turn up plenty of results.

Here's a start:
http://aspalliance.com/1099_Understanding_Connection_Pooling_in_NET.1

Scott

 

[Mark Fitzpatrick]

Make sure your connection string uses pooling. This way you would pool from
a collection of connections so when you call a connection.Open() method, it
first sees if there's a connection in the pool and then will use an
available connection. A lot of this is already handled for you behind the
scenes by ADO.Net so you don't have to worry about it so much. The general
rule though, only have your connection open for the length of time you need
it then close it right away. This frees it up to be used by some other page.

Instead of caching the connections, you should be looking at where you can
use the built-in output caching for a web page or control. If you are
creating news for a site, then make sure the page caches itself so that
you're only calling for a fresh set of data every 20 minutes or so, no need
to cache the connection here, just the resultant page since it's not
changing that often.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - Expression

 

[laziers]

Make sure your connection string uses pooling.

ofcorse, it uses

This way you would pool from
a collection of connections so when you call a connection.Open() method, it
first sees if there's a connection in the pool and then will use an
available connection. A lot of this is already handled for you behind the
scenes by ADO.Net so you don't have to worry about it so much. The general
rule though, only have your connection open for the length of time you need
it then close it right away. This frees it up to be used by some other page.
ok

Instead of caching the connections, you should be looking at where you can
use the built-in output caching for a web page or control. If you are
creating news for a site, then make sure the page caches itself so that
you're only calling for a fresh set of data every 20 minutes or so, no need
to cache the connection here, just the resultant page since it's not
changing that often.

ok, so Im using it in the right way.
I use caching output, pooling, I open the connection only for the time
when I need. Great.

Thaks all of You for the answers.