Using one instance of SqlConnection in an ASP.NET page

[Bob]

In our new .NET web applications, we try to limit the use of SqlConnection
to just one instance per page, even if there are multiple accesses to
various queries. The thinking behind is that this reduces the need to
getting and returning connections to the pool repeatedly if a page has
multiple calls to the DB, and each one manages its own connection. However,
this does requires more deliberate coding, like calling the
SqlConnection.C lose() method in the page's Dispose() method so it's
garanteed that the connection is closed when the page processing is done,
and also is not closed too early. What I'm thinking is whether this is
actually necessary because passing the Connection object into child controls
becomes a pretty big hassle when there are several user controls or custom
controls on the page. If the connection pool management is very efficient
then opening and closing connections repeatly in the code (which is really
getting and returning connections to the pool) wouldn't be a big deal, and
keeping the code simple would be more important. Could anyone give some
suggestions?

[Jason Coyne Gaijin42]

The second option is much better. You should open up a connection for
each function and close it as soon as possible. You will get the same
connection back from the pool 99.9% of the time. More importantly,
OTHER sessions will most likely get that SAME connection, meaning your
database is only seeing one connection. This is infinetly more
scalable.
Regardless of scalability, having one connection and just keeping it
open the whole time is not technically possible for any but trivial
applications, as you can only have one recordset open at a time per
connection. This means that while you are looping through a
datareader, you cannot run any other queries or get any other
datareaders using that connection. This seems like a big step
backwards from classic ADO, but in reality it is not. ADO had the
same limitation, it just opened up a second connection for you
automatically under the covers. The same total number of connections
open is the same, but in ado.net you are aware that you are using two
connections, so perhaps you can redisign code to work more
efficiently.

In my applicaitons I have a connect() function that creates and opens
then returns a connection. I call that at the beginning of each of my
data functions. Then if I am using a dataset, or doing an
executenonquery , I close right inside that funciton. If I am returning
a datareader outside of the function, I always use
CommandBehaviou r.CloseConnecti on and pass it out that way to minimize
impact on my connection pool.
" Bob" <bo*******@yaho o.com> wrote in message news:<Og******* ******@TK2MSFTN GP11.phx.gbl>.. .

In our new .NET web applications, we try to limit the use of SqlConnection
to just one instance per page, even if there are multiple accesses to
various queries. The thinking behind is that this reduces the need to
getting and returning connections to the pool repeatedly if a page has
multiple calls to the DB, and each one manages its own connection. However,
this does requires more deliberate coding, like calling the
SqlConnection.C lose() method in the page's Dispose() method so it's
garanteed that the connection is closed when the page processing is done,
and also is not closed too early. What I'm thinking is whether this is
actually necessary because passing the Connection object into child controls
becomes a pretty big hassle when there are several user controls or custom
controls on the page. If the connection pool management is very efficient
then opening and closing connections repeatly in the code (which is really
getting and returning connections to the pool) wouldn't be a big deal, and
keeping the code simple would be more important. Could anyone give some
suggestions?

[Angel Saenz-Badillos[MS]]

Bob,
A confusing thread, but the message is loud and clear (and completely
correct) Open the connection as late as possible, close it as soon as you
can and in a Finally block (NOT on the page dispose method). The try finally
or "using" construct is the only real way to guarantee your connection will
be closed if an exception happens while your connection is open.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

" Bob" <bo*******@yaho o.com> wrote in message
news:uC******** ******@TK2MSFTN GP10.phx.gbl...

Thanks a lot guys. sounds like you all are saying the 2nd option is better, that is, open and close immediately as needed, even if it means doing it
several times in one request. I also read some other articles which lead to similar conclusions.

Thanks again for the help.

Bob

"Joe Fallon" <jf******@nospa mtwcny.rr.com> wrote in message
news:Ol******** ******@TK2MSFTN GP09.phx.gbl...

Bob,
You should use a single connection and just open and close as needed.

The connection in the pool is not really closed. It is just sitting there

waiting for you to ask for it again.
So you lose nothing to "close" a connection and the "open" it. It is already

open.

In my Business Objects I open a datareader to populate the BO and then

close

it and re-open a 2nd dr to populate any contained BOs or collections. The dr.close also "closes" the connection but then I re-open it and poulate

the

next object.

HTH
--
Joe Fallon

" Bob" <bo*******@yaho o.com> wrote in message
news:eZ******** ******@tk2msftn gp13.phx.gbl...

Hmm, that's still not what I was asking. I understand how and where to properly close a connection, why it should be done, where to set the pool size and timeout etc. What I'm trying to get at is that, if I have a page that needs to make a series of calls to the database, say, call SP 1 to get

some data to populate a drop down, then call SP 2 to get some other

data

to

populate a datagrid, in the same page a user control on the page calls SP

3

to get some status data to display on the sidebar. So I face a design
decision here. One way is to create one instance of the

SqlConnection object (could be handled by a middle tier but for discussion purpose

let's say I'm doing this directly from the aspx.cs class) and use it throughout the page, and put the .Close() in the page Dispose() method so the
connection is closed at the end and only at the end of the page

processing.

Alternatively, I can design it to let each call create its own instance of

the SqlConnection object and close it immediate after the call is

done. This way, I can wrap each call in its self contained function, and
particularly for the user control on the page, I don't have to get the
connection instance from the main page. This makes the code simpler and the

logic a lot easier to follow. However, the second option would use 3
different connections. Assuming the connections are already in the

pool so

it doesn't have to create brand new ones, it would be getting and

returning

connections to the pool 3 times, rather than 1 in the first option.

So back

to my original questions, if getting and returning connections to the pool has become so efficient that doing it 2 times more is no big deal at

all then I probably should go with option 2, otherwise I should still do

option

1, which has more complicated coding particularly if a middle tier is
involved.

Thanks
"Bin Song, MCP" <an*******@disc ussions.microso ft.com> wrote in message
news:2C******** *************** ***********@mic rosoft.com...
> Hi, Bob
>
> I understand your question now.
> The returning of Connection to pool depend on when you close the
connection or the connection time out. The pool size and timeout can be defined in the connection string.
> It is recommended that you always close the Connection when you are
finished using it in order for the connection to be returned to the

pool. This can be done using either the Close or Dispose methods of the

Connection

object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the
connection pool if the maximum pool size has been reached and the

connection

is still valid.
>
> Please see the following article:
>

http://msdn.microsoft.com/library/de...taProvider.asp

>
> Bin Song, MCP
>
> ----- Bob wrote: -----
>
> Alex, thanks for your reply. I understand that the same

connection

string
> reults in connections in the same pool. My question is whether
getting and
> returning connections to the pool have become so efficient in

ADP.NET

and
> Sql Data Provider that there is no need to put in extra code in

order

to
> reduce the number of getting and returning from say, 3 to 1.
>
> Thanks
> Bob
>
> "Alex Papadimoulis" <al***@papadimo ulis.com> wrote in message
> news:10******** *****@corp.supe rnews.com...
> > Bob, IIRC, so long as you open the connection with the same
connection
> > string, it'll be pooled.
> >> Alex Papadimoulis
> > " Bob" <bo*******@yaho o.com> wrote in message
> > news:Og******** *****@TK2MSFTNG P11.phx.gbl...
> >> In our new .NET web applications, we try to limit the use of
> SqlConnection
> >> to just one instance per page, even if there are multiple

accesses

to
> >> various queries. The thinking behind is that this reduces the need to
> >> getting and returning connections to the pool repeatedly if a page

has
> >> multiple calls to the DB, and each one manages its own

connection.

> > However,
> >> this does requires more deliberate coding, like calling the
> >> SqlConnection.C lose() method in the page's Dispose() method

so it's
> >> garanteed that the connection is closed when the page

processing is
> done,
> >> and also is not closed too early. What I'm thinking is whether this is
> >> actually necessary because passing the Connection object into child
> > controls
> >> becomes a pretty big hassle when there are several user controls or
> custom
> >> controls on the page. If the connection pool management is very > efficient
> >> then opening and closing connections repeatly in the code (which is
> really
> >> getting and returning connections to the pool) wouldn't be a big deal,
> and
> >> keeping the code simple would be more important. Could anyone give some
> >> suggestions?
> >>>>>>