SqlConnection Object in Application Variable OR Session Variable OR open/close

Discussion in 'ASP .Net' started by pratham, Aug 31, 2006.

  1. pratham

    pratham Guest

    Hi and regards

    What is best method to have an connection to sql, iam using sqlserver

    1. have an sqlconnection open when the application start event in
    global.asax so that i dont have to open it in each method where i
    require to open an connection

    2. open an connection in seesion so that, it is available in session of
    the user thorugh out

    3. open an connection in each method and close after the requried
    operation is completed

    which is better

    as per the 3rd method ... doest openning and closing in each method is
    a bad??

    Comments expected
     
    pratham, Aug 31, 2006
    #1
    1. Advertising

  2. 1. This is the most horrible method. All users would share the same
    connection object, which means, you are going to have multiple threads
    trying to use it at once, which means your app will crash instance under any
    kind of load.

    2. This is slightly better, but still really bad. First off, it means you
    have an open connection the entire lifetime of a session. This severely
    limits the number of concurrent users, and performs badly.

    3. This is how you should handle it. Connections are pooled by default,
    opening and closing them is relatively inexpensive. Additionally, you will
    get much better performance by not keeping unecessary connections open, 99%
    of which time they are not being used.

    So yes, #3 is the way to go.

    "pratham" <> wrote in message
    news:...
    > Hi and regards
    >
    > What is best method to have an connection to sql, iam using sqlserver
    >
    > 1. have an sqlconnection open when the application start event in
    > global.asax so that i dont have to open it in each method where i
    > require to open an connection
    >
    > 2. open an connection in seesion so that, it is available in session of
    > the user thorugh out
    >
    > 3. open an connection in each method and close after the requried
    > operation is completed
    >
    > which is better
    >
    > as per the 3rd method ... doest openning and closing in each method is
    > a bad??
    >
    > Comments expected
    >
     
    Marina Levit [MVP], Aug 31, 2006
    #2
    1. Advertising

  3. pratham

    pratham Guest

    Thanks Marina for that fast and detailed reply Cheers :)

    Marina Levit [MVP] wrote:
    > 1. This is the most horrible method. All users would share the same
    > connection object, which means, you are going to have multiple threads
    > trying to use it at once, which means your app will crash instance under any
    > kind of load.
    >
    > 2. This is slightly better, but still really bad. First off, it means you
    > have an open connection the entire lifetime of a session. This severely
    > limits the number of concurrent users, and performs badly.
    >
    > 3. This is how you should handle it. Connections are pooled by default,
    > opening and closing them is relatively inexpensive. Additionally, you will
    > get much better performance by not keeping unecessary connections open, 99%
    > of which time they are not being used.
    >
    > So yes, #3 is the way to go.
    >
    > "pratham" <> wrote in message
    > news:...
    > > Hi and regards
    > >
    > > What is best method to have an connection to sql, iam using sqlserver
    > >
    > > 1. have an sqlconnection open when the application start event in
    > > global.asax so that i dont have to open it in each method where i
    > > require to open an connection
    > >
    > > 2. open an connection in seesion so that, it is available in session of
    > > the user thorugh out
    > >
    > > 3. open an connection in each method and close after the requried
    > > operation is completed
    > >
    > > which is better
    > >
    > > as per the 3rd method ... doest openning and closing in each method is
    > > a bad??
    > >
    > > Comments expected
    > >
     
    pratham, Aug 31, 2006
    #3
  4. pratham

    sloan Guest

    Correct (on an earlier post) that #1 is really bad. #2 isn't good either.


    An environment like the web, you especially want to

    "Open Late" and "Close asap" model.

    Open and close as fast as you can.




    "pratham" <> wrote in message
    news:...
    > Hi and regards
    >
    > What is best method to have an connection to sql, iam using sqlserver
    >
    > 1. have an sqlconnection open when the application start event in
    > global.asax so that i dont have to open it in each method where i
    > require to open an connection
    >
    > 2. open an connection in seesion so that, it is available in session of
    > the user thorugh out
    >
    > 3. open an connection in each method and close after the requried
    > operation is completed
    >
    > which is better
    >
    > as per the 3rd method ... doest openning and closing in each method is
    > a bad??
    >
    > Comments expected
    >
     
    sloan, Aug 31, 2006
    #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. Steve Richter

    close SqlConnection when Page is disposed of

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

    SqlConnection sqlCon.Close () doent seem to work

    =?Utf-8?B?QWpheSBCaG9ucw==?=, Nov 22, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    456
    =?Utf-8?B?U3JlZWppdGggUmFt?=
    Nov 22, 2005
  3. Martin Arvidsson, Visual Systems AB

    Q: SqlConnection, Open/Close advantages / disadvantages

    Martin Arvidsson, Visual Systems AB, Apr 2, 2008, in forum: ASP .Net
    Replies:
    5
    Views:
    447
    George Ter-Saakov
    Apr 2, 2008
  4. Iñaki Baz Castillo
    Replies:
    7
    Views:
    877
    Iñaki Baz Castillo
    Jan 12, 2010
  5. Iulian Ilea
    Replies:
    1
    Views:
    323
    pcx99
    Dec 21, 2006
Loading...

Share This Page