Database connection pooling in ASPx

[fniles]

In the Windows application (using VB.NET 2005) I use connection pooling like
the following:
In the main form load I open a connection using a connection string that I
stored in a global variable g_sConnectionString and leave this connection
open and not close it until it exits the application.
Then on each thread/each subsequent sub that needs the connection I create a
local OleDBConnection variable, open the connection using the exact same
connection string as the main form (stored
in global variable g_sConnectionString), and close it after populating a
DataSet.
Then, in the FormClosing event of the main form I close the database
connection.

In an ASPX application the user can close the browser at any page. Do I do
the connection pooling the same way in ASPX where I open the connection at
the Startup Page, leave that connection open, and at subsequent pages I open
connection (using the exact same connection string as the Startup Page) and
close the connection, then at the Startup Page Unload event close the 1st
connection ?
Since user can close the browser at any page, where is the best place to
close the 1st database connection (the one opened in the Startup Page) ?

Thank you.

 

[Patrice]

Connection pooling is done for you. Just open/close connections when needed.
They are not actually closed but just returned to the pool ready to be
returned from the pool when you "open" another connection.

This way you can server a given number of users which much less connexion
than users.

Note that I'm not sure what is the purpose of the global connexion as my
understanding is that you are opening anyway a local connection when needed.
If this is just to keep around the connection string you could keep just the
connection string rather than an opened global connection.

So in short you don't need to open a global connexion in ASP.NET (or
Windows) to benefit from connection pooling. Just open/close a connexion
when needed and the provider should take care of pooling for you (or what is
the DB you are using ?)

 

[fniles]

Thank you.
Sorry for the misunderstanding: I do not use a global connection, I use a
global connection string variable, so that on every form/page I can open
connection using that connection string.
One of our application uses SQL Server 2000, another SQL Server 2005,
another MS Access.

I thought that in the main form/startup page I need to open a database
connection (using a local connection variable), leave that open, then in
other subsequent forms when I open the db connection (also using a local
connection variable) it uses the connection pooling in the 1st form/page ?
Then I close the connection in this subsequent forms. I thought that if on
every page/form (including the 1st page/startup form) I open and close the
connection I won't be using connection pooling ? Do I understand it wrong ?
Are you saying that I can open/close connections on every form/page
(including the 1st page/startup form) as long as I open the connection using
the exact same connectiong string, and it knows to use connection pooling
everytime I open/close the connection?

Thanks again.

 

[Paul Clement]

¤ I thought that in the main form/startup page I need to open a database
¤ connection (using a local connection variable), leave that open, then in
¤ other subsequent forms when I open the db connection (also using a local
¤ connection variable) it uses the connection pooling in the 1st form/page ?
¤ Then I close the connection in this subsequent forms. I thought that if on
¤ every page/form (including the 1st page/startup form) I open and close the
¤ connection I won't be using connection pooling ? Do I understand it wrong ?
¤ Are you saying that I can open/close connections on every form/page
¤ (including the 1st page/startup form) as long as I open the connection using
¤ the exact same connectiong string, and it knows to use connection pooling
¤ everytime I open/close the connection?
¤

Yes. As was mentioned it's enabled by default. Connection pools are created per process or app pool
and per connection string.


Paul
~~~~
Microsoft MVP (Visual Basic)