[Matrix28]

In an effort to speed up my pages I thought about setting up a static database connection in a subroutines page so I wouldn't have to connect and disconnect on each page, something like

Code:

public partial class SubsEngine : System.Web.UI.Page {
            public static MySqlConnection MySQLCon;
public static int ConnectToMySQL() {
                  MySQLCon = new MySqlConnection(blah;");

and then on each page I can use subs_aspx.SubsEngine.MySQLCon which would still be open.

Is this a good idea or not? In theory if 2 people requested a page at the same time would they get an "open data reader assosciated with this connection" while the first page is loading?

[Yeroon]

Hi,

It is not a good idea. The scenario you describe with the Open Reader will occur. It will be very hard to accomodate all scenarios. .Net actually uses connection pooling

Connection pooling allocates actual connections and everything is managed for you. If many sessions need access, multiple connections are allocated and shared, as needed. As long as the connection strings are the same, you will get optimized allocation for multiple users.

Keeping static connection migth might make it unavaliable for the next command, or another command in another session.

So connection pooling is a fundamental part of the framework. Make use of it. Don't try to negate it's purpose.

[digioz]

I have to agree with Yeroon on that one. What you could do however, assuming you are trying to reduce the number of queries you run on the database (if the same data such as navigation menu items, Latest News, etc is repeating from page to page), is to cache that dataset by assigning it to a session variable and then when the page reloads or changes you can simply grab the dataset from the session variable without re-querying of the database.

Pete

[Yeroon]

Please note though that Session usually is user based. If you want to keep it global for all user, use the Cache.

[digioz]

Originally Posted by Yeroon

Please note though that Session usually is user based. If you want to keep it global for all user, use the Cache.

Correct. My assumption was that the displayed content changes from user to user, which is why I recommended Session Variable. If it does not, you would use cache.

Pete