Create application level SQL connection, HOW?

[Guest]

Is there a way to create an application wide connection object in C# so I
don’t have to create a new one for every page? My issue is that while I can
do this within the scope of a single aspx page. It can really drag down
performance because each portion of the page is made up of user controls,
each with a connection object within its scope. I like to have one public
connection object per user session and just open and close it as I need it,
but scope is preventing the pages from seeing the connection object

SqlConnection objConnection = new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);

 

[Damien]

Is there a way to create an application wide connection object in C# so I
don't have to create a new one for every page? My issue is that while I can
do this within the scope of a single aspx page. It can really drag down
performance because each portion of the page is made up of user controls,
each with a connection object within its scope. I like to have one public
connection object per user session and just open and close it as I need it,
but scope is preventing the pages from seeing the connection object

SqlConnection objConnection = new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Connec­tionString"]);

Don't do it! Your application won't scale. Trust the connection pool.
What you should do is share your connection string (create a singleton
class that loads it from your config file and exposes it - so that
config is only accessed once). Consider what happens if 1000 users are
using you site simultaneously. Or 10000. Or the fact that the user can
just close the browser, giving you no clue when to close the
connection.

For your user controls, expose a property called ConnectionString (or
similar), which it is the pages responsibility to hook up (so that the
user controls are not dependent on how the underlying connection
string is stored).

Only thing to note is to make sure you close any connections you open
- think Catch blocks.

Damien

 

[Guest]

I came across several articles that said a global connection was a very bad
idea so I wont do it. Just wigs me out to watch SQL generate 30+ connections
in the pool for a single user request post back when I know for a fact I’m
creating, opening, closing, and disposing of all the connections. There are
8 user controls on my page, why 30 connections are created when I only need 8
or 10 is beyond me. I figured it was b/c each user control has a
SqlConnection objConnection = new

SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);.


--
JP
..NET Software Developer

Is there a way to create an application wide connection object in C# so I
don't have to create a new one for every page? My issue is that while I can
do this within the scope of a single aspx page. It can really drag down
performance because each portion of the page is made up of user controls,
each with a connection object within its scope. I like to have one public
connection object per user session and just open and close it as I need it,
but scope is preventing the pages from seeing the connection object

SqlConnection objConnection = new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Connec­tionString"]);

Don't do it! Your application won't scale. Trust the connection pool.
What you should do is share your connection string (create a singleton
class that loads it from your config file and exposes it - so that
config is only accessed once). Consider what happens if 1000 users are
using you site simultaneously. Or 10000. Or the fact that the user can
just close the browser, giving you no clue when to close the
connection.

For your user controls, expose a property called ConnectionString (or
similar), which it is the pages responsibility to hook up (so that the
user controls are not dependent on how the underlying connection
string is stored).

Only thing to note is to make sure you close any connections you open
- think Catch blocks.

Damien

 

[Damien]

I came across several articles that said a global connection was a very bad
idea so I wont do it. Just wigs me out to watch SQL generate 30+ connections
in the pool for a single user request post back when I know for a fact I'm
creating, opening, closing, and disposing of all the connections. There are
8 user controls on my page, why 30 connections are created when I only need 8
or 10 is beyond me. I figured it was b/c each user control has a
SqlConnection objConnection = new

SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Connec­tionString"]);.

Yeah, but how many connections does it open for 100 users? Optimizing
for 1 user is rarely a model for a succesful website I'd just
emphasize again to make sure that every opened connection does get
explicitly closed/disposed. Went mad once in one project trying to
find the connection leak once lots of users were using the site.
Course, couldn't reproduce it on my machine with only me using it.

Damien