471,350 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,350 software developers and data experts.

Sqlclient - connection pooling

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.

Jan 18 '06 #1
3 1373
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.

Jan 19 '06 #2
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?

Jan 19 '06 #3
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?

Jan 19 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Rob Nicholson | last post: by
1 post views Thread by ugurceng | last post: by
1 post views Thread by Lenny Shprekher | last post: by
3 posts views Thread by fniles | last post: by
9 posts views Thread by fniles | last post: by
1 post views Thread by fniles | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.