Q: SqlConnection, Open/Close advantages / disadvantages

Discussion in 'ASP .Net' started by Martin Arvidsson, Visual Systems AB, Apr 2, 2008.

  1. Hi!

    I am currently developing my first webapplication using .net (2.x)

    In a couple examples seen in the helpfile that came with VS2008
    i see that they are opening a connection to the sql server.
    Issue a command or two, then close the connection.

    My question is, wouldn't it be better to have One connection open during the
    current session and when the session end, close the connection.

    Or perhaps not.

    Pros. / Cons gladley taken for me to learn, links, comments etc.

    Regards

    Martin
     
    Martin Arvidsson, Visual Systems AB, Apr 2, 2008
    #1
    1. Advertising

  2. Martin Arvidsson, Visual Systems AB

    sloan Guest

    Re: SqlConnection, Open/Close advantages / disadvantages

    Open Late.
    Use Quickly.
    Close Soon.

    ...........

    Do not wire up a connection to a session or any other kind of trick.

    ...

    Look up "Connection Pooling" "Asp.Net" and you'll have 1,000 thing to read
    about it.



    "Martin Arvidsson, Visual Systems AB" <> wrote in
    message news:...
    > Hi!
    >
    > I am currently developing my first webapplication using .net (2.x)
    >
    > In a couple examples seen in the helpfile that came with VS2008
    > i see that they are opening a connection to the sql server.
    > Issue a command or two, then close the connection.
    >
    > My question is, wouldn't it be better to have One connection open during
    > the
    > current session and when the session end, close the connection.
    >
    > Or perhaps not.
    >
    > Pros. / Cons gladley taken for me to learn, links, comments etc.
    >
    > Regards
    >
    > Martin
    >
     
    sloan, Apr 2, 2008
    #2
    1. Advertising

  3. Re: SqlConnection, Open/Close advantages / disadvantages

    Thanx, will surley dig into it.

    /Martin

    "sloan" <> skrev i meddelandet
    news:...
    >
    > Open Late.
    > Use Quickly.
    > Close Soon.
    >
    > ..........
    >
    > Do not wire up a connection to a session or any other kind of trick.
    >
    > ..
    >
    > Look up "Connection Pooling" "Asp.Net" and you'll have 1,000 thing to read
    > about it.
    >
    >
    >
    > "Martin Arvidsson, Visual Systems AB" <> wrote in
    > message news:...
    >> Hi!
    >>
    >> I am currently developing my first webapplication using .net (2.x)
    >>
    >> In a couple examples seen in the helpfile that came with VS2008
    >> i see that they are opening a connection to the sql server.
    >> Issue a command or two, then close the connection.
    >>
    >> My question is, wouldn't it be better to have One connection open during
    >> the
    >> current session and when the session end, close the connection.
    >>
    >> Or perhaps not.
    >>
    >> Pros. / Cons gladley taken for me to learn, links, comments etc.
    >>
    >> Regards
    >>
    >> Martin
    >>

    >
    >
     
    Martin Arvidsson, Visual Systems AB, Apr 2, 2008
    #3
  4. Re: SqlConnection, Open/Close advantages / disadvantages

    How many users do you want to have in your Web application?
    One.... You can do anything you want....

    1000.... You can not have 1000 connections to SQL server. That is sure way
    to kill server...

    Do not worry about it. .NET does not close connections internally. It pools
    them so when you open connection it grabs already opened one before from the
    pool. When you close it - connection is simply returned to the pool

    Search ".NET pooling connection" in google to get more info.


    George.



    "Martin Arvidsson, Visual Systems AB" <> wrote in
    message news:...
    > Hi!
    >
    > I am currently developing my first webapplication using .net (2.x)
    >
    > In a couple examples seen in the helpfile that came with VS2008
    > i see that they are opening a connection to the sql server.
    > Issue a command or two, then close the connection.
    >
    > My question is, wouldn't it be better to have One connection open during
    > the
    > current session and when the session end, close the connection.
    >
    > Or perhaps not.
    >
    > Pros. / Cons gladley taken for me to learn, links, comments etc.
    >
    > Regards
    >
    > Martin
    >
     
    George Ter-Saakov, Apr 2, 2008
    #4
  5. Re: SqlConnection, Open/Close advantages / disadvantages

    I'm not sure that is entirely accurate. If you .Close() a SqlConnection
    object, this returns it to the pool but not in an opened state.
    -- Peter
    Site: http://www.eggheadcafe.com
    UnBlog: http://petesbloggerama.blogspot.com
    Short Urls & more: http://ittyurl.net


    "George Ter-Saakov" wrote:

    > How many users do you want to have in your Web application?
    > One.... You can do anything you want....
    >
    > 1000.... You can not have 1000 connections to SQL server. That is sure way
    > to kill server...
    >
    > Do not worry about it. .NET does not close connections internally. It pools
    > them so when you open connection it grabs already opened one before from the
    > pool. When you close it - connection is simply returned to the pool
    >
    > Search ".NET pooling connection" in google to get more info.
    >
    >
    > George.
    >
    >
    >
    > "Martin Arvidsson, Visual Systems AB" <> wrote in
    > message news:...
    > > Hi!
    > >
    > > I am currently developing my first webapplication using .net (2.x)
    > >
    > > In a couple examples seen in the helpfile that came with VS2008
    > > i see that they are opening a connection to the sql server.
    > > Issue a command or two, then close the connection.
    > >
    > > My question is, wouldn't it be better to have One connection open during
    > > the
    > > current session and when the session end, close the connection.
    > >
    > > Or perhaps not.
    > >
    > > Pros. / Cons gladley taken for me to learn, links, comments etc.
    > >
    > > Regards
    > >
    > > Martin
    > >

    >
    >
    >
     
    Peter Bromberg [C# MVP], Apr 2, 2008
    #5
  6. Re: SqlConnection, Open/Close advantages / disadvantages

    Of course it's in opened state... (in terms of SQL server).
    Next time you 'open' it from .NET its reinitializing it though just in case
    there was a transaction level (or staff like that) set on that
    connection...(not sure what exactly is gets reinitialized though but
    defently ut's much faster than to open new connection)

    That is why you need to be very careful when using temp tables in SQL when
    work from ASP.NET. Connections are never closed (per say) and temp table is
    not deleted even if you have con.Close() statement. So next time you do
    something like (pseducode)
    con.Open(..)
    con.Execute("'CREATE TABLE #tmpTable..");
    it might blow up... cause tmpTable already exists....


    PS: Connection do get closed sometimes... Connection pool supports so called
    connection's "age" (I think it's a 5 minute by default) and they do get
    closed after reaching that age. It's pretty much same idea as with pool
    recycling.




    PPS: If you are not using distributed transaction it's recommended to add
    Enlist=False to connection strings. Saves some runtime by not joining
    connection to MTS.



    George.





    "Peter Bromberg [C# MVP]" <> wrote in message
    news:...
    > I'm not sure that is entirely accurate. If you .Close() a SqlConnection
    > object, this returns it to the pool but not in an opened state.
    > -- Peter
    > Site: http://www.eggheadcafe.com
    > UnBlog: http://petesbloggerama.blogspot.com
    > Short Urls & more: http://ittyurl.net
    >
    >
    > "George Ter-Saakov" wrote:
    >
    >> How many users do you want to have in your Web application?
    >> One.... You can do anything you want....
    >>
    >> 1000.... You can not have 1000 connections to SQL server. That is sure
    >> way
    >> to kill server...
    >>
    >> Do not worry about it. .NET does not close connections internally. It
    >> pools
    >> them so when you open connection it grabs already opened one before from
    >> the
    >> pool. When you close it - connection is simply returned to the pool
    >>
    >> Search ".NET pooling connection" in google to get more info.
    >>
    >>
    >> George.
    >>
    >>
    >>
    >> "Martin Arvidsson, Visual Systems AB" <> wrote
    >> in
    >> message news:...
    >> > Hi!
    >> >
    >> > I am currently developing my first webapplication using .net (2.x)
    >> >
    >> > In a couple examples seen in the helpfile that came with VS2008
    >> > i see that they are opening a connection to the sql server.
    >> > Issue a command or two, then close the connection.
    >> >
    >> > My question is, wouldn't it be better to have One connection open
    >> > during
    >> > the
    >> > current session and when the session end, close the connection.
    >> >
    >> > Or perhaps not.
    >> >
    >> > Pros. / Cons gladley taken for me to learn, links, comments etc.
    >> >
    >> > Regards
    >> >
    >> > Martin
    >> >

    >>
    >>
    >>
     
    George Ter-Saakov, Apr 2, 2008
    #6
    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. pratham
    Replies:
    3
    Views:
    740
    sloan
    Aug 31, 2006
  2. Replies:
    2
    Views:
    1,565
    John Ersatznom
    Jan 4, 2007
  3. Replies:
    5
    Views:
    36,403
    asifpmna
    Dec 9, 2011
  4. sumankumar
    Replies:
    0
    Views:
    470
    sumankumar
    Aug 25, 2007
  5. Iulian Ilea
    Replies:
    1
    Views:
    347
    pcx99
    Dec 21, 2006
Loading...

Share This Page