Sqlclient - connection pooling

[Antimon]

Hi,

I'am working on an asp.net web project. was looking at some sources and
as i can see ppl creates new "sqlconnection" instances for each query
(or for every module). I know there's a pooling mechanism but isn't it
better to have a connection created at "page_load" and closed when
request handling completes?
Shall i create a new connection for every single module or create just
one for every page request?

Thanks.

[Nicholas Paldino [.NET/C# MVP]]

Antimon,

You should create your connection when you need it, and dispose of it
when you are done with your operation, no more, no less.

In an ASP.NET application, I can't see why you would need to maintain an
open connection beyond the processing of your page (in other words, on the
class level, and not disposing of it).

My recommendation would be to have a static method which will create
your connection for you, unopened (it will set up the connection string, so
you can change the details of your connection everywhere when needed later
on). You can store this on the page level, getting it on the Load event of
the page.

You would have to make sure that you close it in the Unload event on
your page as well.

This way, you can share the database connection throughout the methods
on your page. If you want your classes to use the same database connection,
you will have to pass it to them somehow.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Antimon" <an*****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi,

I'am working on an asp.net web project. was looking at some sources and
as i can see ppl creates new "sqlconnection" instances for each query
(or for every module). I know there's a pooling mechanism but isn't it
better to have a connection created at "page_load" and closed when
request handling completes?
Shall i create a new connection for every single module or create just
one for every page request?

Thanks.

[Antimon]

Thanks for the response Nicholas,

I can manage to have a database connection opened till the page render
ends but i'M not sure if it is the best way. I mean, every single page
in my project will do a session validation on database, check for any
incoming messages to user, and do some other stuff with database. In
some open source projects, it creates a conn and validates session
data, disposes it. creates a new one (in another method ofcourse),
checks for any incoming messages and disposes it. creates a new one for
the main reason of that page, uses that conn and disposes. These all
could have done in just one connection with your approach.

But i thought there might be an advantage on creating a connection for
the minimal time possible even if it means creating 2 more connections
for the same page... Is it?

[Nicholas Paldino [.NET/C# MVP]]

Antimon,

Well, it's a matter of scope. You ^know^ that processing isn't going to
go beyond your page, so as a (possibly slight) optmization, you can
establish that the connection is obtained from "somewhere" (that somewhere
being your page) and then use that.

This is one option. Another option would be to use connection pooling.
Assuming that it is one user that is accessing the database, connection
pooling can be a big boon and allow you to compartmentalize your design
(assuming that you get your connections from the same source).
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Antimon" <an*****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Thanks for the response Nicholas,

I can manage to have a database connection opened till the page render
ends but i'M not sure if it is the best way. I mean, every single page
in my project will do a session validation on database, check for any
incoming messages to user, and do some other stuff with database. In
some open source projects, it creates a conn and validates session
data, disposes it. creates a new one (in another method ofcourse),
checks for any incoming messages and disposes it. creates a new one for
the main reason of that page, uses that conn and disposes. These all
could have done in just one connection with your approach.

But i thought there might be an advantage on creating a connection for
the minimal time possible even if it means creating 2 more connections
for the same page... Is it?