Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Using one instance of SqlConnection in an ASP.NET page

Reply
Thread Tools

Using one instance of SqlConnection in an ASP.NET page

 
 
Bob
Guest
Posts: n/a
 
      04-08-2004
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.Close() 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?


 
Reply With Quote
 
 
 
 
Alex Papadimoulis
Guest
Posts: n/a
 
      04-08-2004
Bob, IIRC, so long as you open the connection with the same connection
string, it'll be pooled.

Alex Papadimoulis
" Bob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.Close() 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?
>
>



 
Reply With Quote
 
 
 
 
Bob
Guest
Posts: n/a
 
      04-08-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob, IIRC, so long as you open the connection with the same connection
> string, it'll be pooled.
>
> Alex Papadimoulis
> " Bob" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.Close() 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?
> >
> >

>
>



 
Reply With Quote
 
Brad Williams
Guest
Posts: n/a
 
      04-08-2004
" Bob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


Someone can correct me if I'm wrong, but I believe getting and returning
connections from managed connection pool is very efficient -- otherwise it
wouldn't be much of a pooling/caching mechanism!

Brad Williams


 
Reply With Quote
 
Alex Papadimoulis
Guest
Posts: n/a
 
      04-08-2004
Bob,

I don't believe there would be any noticible difference. You may be giving
up a few cycles to do that, but it really is a better practice. Write the
simpler, easier to manage code now. If you see a performance problem, then
optimize.

-- Alex Papadimoulis

" Bob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Bob, IIRC, so long as you open the connection with the same connection
> > string, it'll be pooled.
> >
> > Alex Papadimoulis
> > " Bob" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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.Close() 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?
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Bob
Guest
Posts: n/a
 
      04-08-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Bob, IIRC, so long as you open the connection with the same

connection
> > string, it'll be pooled.
> >> Alex Papadimoulis

> > " Bob" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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.Close() 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?
> >>>>>>



 
Reply With Quote
 
Joe Fallon
Guest
Posts: n/a
 
      04-08-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Bob, IIRC, so long as you open the connection with the same

> connection
> > > string, it'll be pooled.
> > >> Alex Papadimoulis
> > > " Bob" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> 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.Close() 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?
> > >>>>>>

>
>



 
Reply With Quote
 
Bob
Guest
Posts: n/a
 
      04-09-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Bob, IIRC, so long as you open the connection with the same

> > connection
> > > > string, it'll be pooled.
> > > >> Alex Papadimoulis
> > > > " Bob" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > >> 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.Close() 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?
> > > >>>>>>

> >
> >

>
>



 
Reply With Quote
 
Pablo Castro [MS]
Guest
Posts: n/a
 
      04-10-2004
Pooling tends to be very efficient, particularly if you're not using
DTC/COM+ transactions. Integrated security also has an small impact, but is
has been greatly optimized in .NET 1.1.

That said, if you want to squeeze every last bit of performance, keeping the
connection open for the execution of the entire page is certainly better.
While pooling is efficient, there is quite a bit of code that needs to run
in order to grab a connection, and we can potentially hit a lock because of
other threads accessing the pooler.

Regarding the scalability implications of this: is most cases a page is
processed very quickly and "all together" -meaning that there are no long
pauses during the page rendering process. Given that assumption, there is no
benefit on opening/closing the connection at every use within the same page.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


" Bob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.Close() 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?
>
>



 
Reply With Quote
 
Rick Spiewak
Guest
Posts: n/a
 
      04-10-2004
As a third option, consider using the Microsoft Data Access Application
Block, and let it worry about the rest <g>!

" Bob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > Bob, IIRC, so long as you open the connection with the same
> > > connection
> > > > > string, it'll be pooled.
> > > > >> Alex Papadimoulis
> > > > > " Bob" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > >> 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.Close() 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?
> > > > >>>>>>
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
close SqlConnection when Page is disposed of Steve Richter ASP .Net 4 04-27-2005 02:18 PM
Custom Control design time access to SQLConnection on Web Page Robin Shaw ASP .Net Datagrid Control 1 08-12-2004 02:19 PM
Custom Control design time access to SQLConnection on Web Page Robin Shaw ASP .Net 1 08-12-2004 02:19 PM
Custom Control design time access to SQLConnection on Web Page Robin Shaw ASP .Net Building Controls 1 08-12-2004 02:19 PM
Custom controls instance problem on PostBack - two controls, one instance ??? Dejan Vesic ASP .Net Web Controls 0 08-20-2003 02:43 PM



Advertisments