Re: Opening / Closing SQL Server connections

Discussion in 'ASP .Net' started by Patrice, Sep 21, 2004.

  1. Patrice

    Patrice Guest

    This is also currently what I do (though the connection is private to the
    the layer).

    I'm looking at this thread as I've done some investigation about this once.

    When connection pooling is enabled the time for opening/closing a connection
    is really very short. I considered then to open/close the connection on a
    per call basis.

    The benefit I see is that you don't have to include in your debug release a
    check to see if all connections are properly closed. Also it allows to close
    the connection generally earlier in the page (or at least as soon as
    possible) and you could even imagine in some cases a lengthy thing that
    don't need the DB and another page could use a connection in between.

    IMO definitely worth to consider. I would like to ear from someone that
    already open/close on each call...

    TIA

    Patrice

    --

    "GrantMagic" <> a écrit dans le message de
    news:...
    > A method i like using is to open the connection once, then pass that
    > connection to the various methods being called for that process.
    > Thus avoiding reopening and closing of the database as much as possible.
    >
    > The main process is then rapped in a try statement to watch for failure

    and
    > close the connection if an exception occurs.
    >
    > "Daniel Malcolm" <> wrote in message
    > news:...
    > > Just looking for further info on managing opening / closing of SQL

    Server
    > > connections in an n-tier ADO.Net application.
    > >
    > > A business layer component method might make multiple calls to various
    > > methods within the data access layer. Is it sensible to open / close a
    > > database connection within each method in the data access layer, even if
    > > there will be multiple calls to data access methods in series?
    > >
    > > The following suggests that time required to open / close a pooled
    > > connection is negligible:
    > >
    > >

    http://msdn.microsoft.com/library/d...p?frame=true#daag_managingdatabaseconnections
    > >
    > >
    > > Alternatively, each business layer method could call a "cleanup" method

    in
    > > the data access layer to close any open connections when it had finished
    > > calls to the data access layer..
    > >
    > > Any thoughts?
    > >
    > > Thanks
    > >
    > >
    > >

    >
    >
    Patrice, Sep 21, 2004
    #1
    1. Advertising

  2. If you understand the ADO.NET internals, it is better to open and close for
    each call. However, you can also work smart by grabbing multiple info sets
    (DataTables from SELECT statements, for example) on one pass (single stored
    procedure).

    When you hold a connection, on a busy app esp., you can end up using tons
    more memory, increase licensing costs and even slow down the apps, as you
    force new connections instead of using the pool effectively. While saving a
    microsecond for each process, you rob ADO.NET of its highly efficient pooling
    mechanism and implement your own connection control process.

    There are times where holding a connection is wiser, but I would shy away
    from this methodology, as a rule.

    ---

    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    ***************************
    Think Outside the Box!
    ***************************

    "Patrice" wrote:

    > This is also currently what I do (though the connection is private to the
    > the layer).
    >
    > I'm looking at this thread as I've done some investigation about this once.
    >
    > When connection pooling is enabled the time for opening/closing a connection
    > is really very short. I considered then to open/close the connection on a
    > per call basis.
    >
    > The benefit I see is that you don't have to include in your debug release a
    > check to see if all connections are properly closed. Also it allows to close
    > the connection generally earlier in the page (or at least as soon as
    > possible) and you could even imagine in some cases a lengthy thing that
    > don't need the DB and another page could use a connection in between.
    >
    > IMO definitely worth to consider. I would like to ear from someone that
    > already open/close on each call...
    >
    > TIA
    >
    > Patrice
    >
    > --
    >
    > "GrantMagic" <> a écrit dans le message de
    > news:...
    > > A method i like using is to open the connection once, then pass that
    > > connection to the various methods being called for that process.
    > > Thus avoiding reopening and closing of the database as much as possible.
    > >
    > > The main process is then rapped in a try statement to watch for failure

    > and
    > > close the connection if an exception occurs.
    > >
    > > "Daniel Malcolm" <> wrote in message
    > > news:...
    > > > Just looking for further info on managing opening / closing of SQL

    > Server
    > > > connections in an n-tier ADO.Net application.
    > > >
    > > > A business layer component method might make multiple calls to various
    > > > methods within the data access layer. Is it sensible to open / close a
    > > > database connection within each method in the data access layer, even if
    > > > there will be multiple calls to data access methods in series?
    > > >
    > > > The following suggests that time required to open / close a pooled
    > > > connection is negligible:
    > > >
    > > >

    > http://msdn.microsoft.com/library/d...p?frame=true#daag_managingdatabaseconnections
    > > >
    > > >
    > > > Alternatively, each business layer method could call a "cleanup" method

    > in
    > > > the data access layer to close any open connections when it had finished
    > > > calls to the data access layer..
    > > >
    > > > Any thoughts?
    > > >
    > > > Thanks
    > > >
    > > >
    > > >

    > >
    > >

    >
    >
    >
    =?Utf-8?B?Q293Ym95IChHcmVnb3J5IEEuIEJlYW1lcikgLSBN, Sep 21, 2004
    #2
    1. Advertising

  3. I am definitely biased in this but if you want my opinion I am a huge fan of
    opening a connection per call. There are only three things you need to do:

    Use the "using" or a try .. finally connection.Dispose() construct to
    _guarantee_ that your connection is disposed.
    Open the connection as late as possible. //(if the next statement is not an
    Execute why do you need an open connection?)
    Close the connection as early as possible.//(don't place anything between
    open and close that could be done after close)

    This not only results in very easy to read and maintain code, it will
    guarantee very close to the best performance while giving your application
    the room to become scalable. If you use multiple threads in your application
    this becomes even more critical since ado.net objects are not thread safe.

    The only exception I can think of would be a Winforms single threaded app
    where you want to control your connections manually.
    --
    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.
    I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/




    "Patrice" <> wrote in message
    news:...
    > This is also currently what I do (though the connection is private to the
    > the layer).
    >
    > I'm looking at this thread as I've done some investigation about this

    once.
    >
    > When connection pooling is enabled the time for opening/closing a

    connection
    > is really very short. I considered then to open/close the connection on a
    > per call basis.
    >
    > The benefit I see is that you don't have to include in your debug release

    a
    > check to see if all connections are properly closed. Also it allows to

    close
    > the connection generally earlier in the page (or at least as soon as
    > possible) and you could even imagine in some cases a lengthy thing that
    > don't need the DB and another page could use a connection in between.
    >
    > IMO definitely worth to consider. I would like to ear from someone that
    > already open/close on each call...
    >
    > TIA
    >
    > Patrice
    >
    > --
    >
    > "GrantMagic" <> a écrit dans le message de
    > news:...
    > > A method i like using is to open the connection once, then pass that
    > > connection to the various methods being called for that process.
    > > Thus avoiding reopening and closing of the database as much as possible.
    > >
    > > The main process is then rapped in a try statement to watch for failure

    > and
    > > close the connection if an exception occurs.
    > >
    > > "Daniel Malcolm" <> wrote in message
    > > news:...
    > > > Just looking for further info on managing opening / closing of SQL

    > Server
    > > > connections in an n-tier ADO.Net application.
    > > >
    > > > A business layer component method might make multiple calls to various
    > > > methods within the data access layer. Is it sensible to open / close a
    > > > database connection within each method in the data access layer, even

    if
    > > > there will be multiple calls to data access methods in series?
    > > >
    > > > The following suggests that time required to open / close a pooled
    > > > connection is negligible:
    > > >
    > > >

    >

    http://msdn.microsoft.com/library/d...p?frame=true#daag_managingdatabaseconnections
    > > >
    > > >
    > > > Alternatively, each business layer method could call a "cleanup"

    method
    > in
    > > > the data access layer to close any open connections when it had

    finished
    > > > calls to the data access layer..
    > > >
    > > > Any thoughts?
    > > >
    > > > Thanks
    > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Angel Saenz-Badillos[MS], Sep 21, 2004
    #3
  4. Re: Opening / Closing SQL Server connections -- licencing costs?

    "Cowboy (Gregory A. Beamer) - MVP" <> wrote
    in message news:...
    >
    > increase licensing costs


    Has there been a change in the SQL Server license that allows middleware to
    multiplex a CAL? If not, how does connection pooling decrease licensing
    costs?

    --
    Michael D. Long
    Michael D. Long, Sep 22, 2004
    #4
    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. Michael D. Long

    Re: Opening / Closing SQL Server connections

    Michael D. Long, Sep 22, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    474
    Michael D. Long
    Sep 24, 2004
  2. Michael D. Long

    Re: Opening / Closing SQL Server connections

    Michael D. Long, Sep 22, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    474
    Michael D. Long
    Sep 22, 2004
  3. bannaman

    Closing SQL Connections

    bannaman, Mar 21, 2006, in forum: ASP .Net
    Replies:
    10
    Views:
    5,165
    Juan T. Llibre
    Mar 21, 2006
  4. jobs
    Replies:
    2
    Views:
    868
  5. arbpen

    Opening/Closing Connections global.asa

    arbpen, May 17, 2006, in forum: ASP General
    Replies:
    4
    Views:
    438
    arbpen
    May 17, 2006
Loading...

Share This Page