Q: SqlConnection, Open/Close advantages / disadvantages

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Martin Arvidsson, Visual Systems AB
    #1

    Q: SqlConnection, Open/Close advantages / disadvantages

    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


    Tags: None
    • sloan
      #2
      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" <martin.arvidss on@vsab.netwrot e in
      message news:ev6SoBNlIH A.5160@TK2MSFTN GP05.phx.gbl...
      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
      >

        Comment

        • George Ter-Saakov
          #3
          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" <martin.arvidss on@vsab.netwrot e in
          message news:ev6SoBNlIH A.5160@TK2MSFTN GP05.phx.gbl...
          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
          >

            Comment

            • =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
              #4
              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" <martin.arvidss on@vsab.netwrot e in
              message news:ev6SoBNlIH A.5160@TK2MSFTN GP05.phx.gbl...
              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
              >
              >
              >

                Comment

                • George Ter-Saakov
                  #5
                  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...(n ot 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("'C REATE 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]" <pbromberg@yaho o.NoSpamMaam.co mwrote in message
                  news:4EA541F4-8CE4-4CAE-82E5-F957C72B39D7@mi crosoft.com...
                  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" <martin.arvidss on@vsab.netwrot e
                  >in
                  >message news:ev6SoBNlIH A.5160@TK2MSFTN GP05.phx.gbl...
                  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
                  >
                  >>
                  >>
                  >>

                    Comment

                    Previous template Next
                    Working...