Create application level SQL connection, HOW?

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"]);

--
JP
.NET Software Developer

Damien
Thu Jul 12 01:27:57 CDT 2007

On Jul 11, 8:22 pm, JP <J...@discussions.microsoft.com> wrote:
> 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 c=
an
> 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 i=
t,
> but scope is preventing the pages from seeing the connection object =20
>
> SqlConnection objConnection =3D new
> SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Conn=
ec=ADtionString"]);
>
> --
> JP
> .NET Software Developer

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

JP
Thu Jul 12 11:08:10 CDT 2007

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


"Damien" wrote:

> On Jul 11, 8:22 pm, JP <J...@discussions.microsoft.com> wrote:
> > 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"]);
> >
> > --
> > JP
> > .NET Software Developer
>
> 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
Fri Jul 13 02:37:39 CDT 2007

On Jul 12, 5:08 pm, JP <J...@discussions.microsoft.com> wrote:
> I came across several articles that said a global connection was a very b=
ad
> idea so I wont do it. Just wigs me out to watch SQL generate 30+ connecti=
ons
> 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 ne=
ed 8
> or 10 is beyond me. I figured it was b/c each user control has a
> SqlConnection objConnection =3D new
>
> SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Conn=
ec=ADtionString"]);.
>

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