Using one instance of SqlConnection in an ASP.NET page

Discussion in 'ASP .Net' started by Bob, Apr 8, 2004.

  1. Bob

    Bob Guest

    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?
    Bob, Apr 8, 2004
    #1
    1. Advertising

  2. Bob, IIRC, so long as you open the connection with the same connection
    string, it'll be pooled.

    Alex Papadimoulis
    " Bob" <> wrote in message
    news:...
    > 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?
    >
    >
    Alex Papadimoulis, Apr 8, 2004
    #2
    1. Advertising

  3. Bob

    Bob Guest

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

    >
    >
    Bob, Apr 8, 2004
    #3
  4. " Bob" <> wrote in message
    news:...
    > 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
    Brad Williams, Apr 8, 2004
    #4
  5. 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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > Bob, IIRC, so long as you open the connection with the same connection
    > > string, it'll be pooled.
    > >
    > > Alex Papadimoulis
    > > " Bob" <> wrote in message
    > > news:...
    > > > 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?
    > > >
    > > >

    > >
    > >

    >
    >
    Alex Papadimoulis, Apr 8, 2004
    #5
  6. Bob

    Bob Guest

    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" <> wrote in message
    news:...
    > 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/d...nectionPoolingForSQLServerNETDataProvider.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" <> wrote in message
    > news:...
    > > Bob, IIRC, so long as you open the connection with the same

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

    > > " Bob" <> wrote in message
    > > news:...
    > >> 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?
    > >>>>>>
    Bob, Apr 8, 2004
    #6
  7. Bob

    Joe Fallon Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > 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/d...nectionPoolingForSQLServerNETDataProvider.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" <> wrote in message
    > > news:...
    > > > Bob, IIRC, so long as you open the connection with the same

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

    >
    >
    Joe Fallon, Apr 8, 2004
    #7
  8. Bob

    Bob Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > news:...
    > > > 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/d...nectionPoolingForSQLServerNETDataProvider.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" <> wrote in message
    > > > news:...
    > > > > Bob, IIRC, so long as you open the connection with the same

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

    > >
    > >

    >
    >
    Bob, Apr 9, 2004
    #8
  9. 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" <> wrote in message
    news:...
    > 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?
    >
    >
    Pablo Castro [MS], Apr 10, 2004
    #9
  10. Bob

    Rick Spiewak Guest

    As a third option, consider using the Microsoft Data Access Application
    Block, and let it worry about the rest <g>!

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

    > >
    > >

    >
    >
    Rick Spiewak, Apr 10, 2004
    #10
  11. 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
    CommandBehaviour.CloseConnection and pass it out that way to minimize
    impact on my connection pool.
    " Bob" <> wrote in message news:<>...
    > 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?
    Jason Coyne Gaijin42, Apr 10, 2004
    #11
  12. 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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > news:...
    > > > 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" <> wrote in message
    > > > news:...
    > > > > 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/d...nectionPoolingForSQLServerNETDataProvider.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" <> wrote in message
    > > > > news:...
    > > > > > Bob, IIRC, so long as you open the connection with the same
    > > > connection
    > > > > > string, it'll be pooled.
    > > > > >> Alex Papadimoulis
    > > > > > " Bob" <> wrote in message
    > > > > > news:...
    > > > > >> 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?
    > > > > >>>>>>
    > > >
    > > >

    > >
    > >

    >
    >
    Angel Saenz-Badillos[MS], Apr 12, 2004
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robin Shaw
    Replies:
    1
    Views:
    394
    Cowboy \(Gregory A. Beamer\) [MVP]
    Aug 12, 2004
  2. mid
    Replies:
    4
    Views:
    7,278
    Scott Allen
    Dec 14, 2004
  3. =?Utf-8?B?Q2F0YWRtaW4=?=

    ASP.Net SQLConnection leaving orphans

    =?Utf-8?B?Q2F0YWRtaW4=?=, Feb 22, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    558
    Prodip Saha
    Feb 22, 2005
  4. Steve Richter

    close SqlConnection when Page is disposed of

    Steve Richter, Apr 26, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    2,020
    =?Utf-8?B?QnJhZCBRdWlubg==?=
    Apr 27, 2005
  5. techno

    Ado.Net SqlConnection in Classic ASP

    techno, Feb 28, 2010, in forum: ASP General
    Replies:
    2
    Views:
    1,388
    Bob Barrows
    Feb 28, 2010
Loading...

Share This Page