Basic question about database connection in ASP.NET

Discussion in 'ASP .Net' started by Lau Lei Cheong, Jan 13, 2006.

  1. Hello,

    Actually I think I should have had asked it long before, but somehow
    I haven't.

    Here's the scenerio: Say we have a few pages in an ASP.NET project,
    each of them needs to connect to the database(Does not really matter but,
    in case you need to know, either MSSQL or ODBC ones) a few times when it
    loads to do the CURD things. How there's 3 ways proposed to do so:

    1) Store the connection object in a class as static object. Each
    time the page loads, it opens(in case connection disconnected) and wait
    until the connection is avaliable. Not closing it and use it through the
    session.

    2) Store the connection object in the Page, open the connection when
    the page loads, and close as the page renders.

    3) New the connection object on "per use" basis. Declare the object
    in the function blocks, Open and Close within try{}finally{} control block,
    and let the GC frees it when the procedure exits.

    Of the above designs, which one is most sound to you? And will that
    impose heavier burden to the database? I don't have clear idea about it and
    wish to clarify that before carrying on the next project. (Currently (3) is
    selected as it seems neater)

    Thanks for any inputs.

    Regards,
    Lau Lei Cheong
    Lau Lei Cheong, Jan 13, 2006
    #1
    1. Advertising

  2. Hi Lau Lei,

    why not make a base class and create/"store" there the connection?

    Regards, Roland

    Lau Lei Cheong schrieb:
    > Hello,
    >
    > Actually I think I should have had asked it long before, but somehow
    > I haven't.
    >
    > Here's the scenerio: Say we have a few pages in an ASP.NET project,
    > each of them needs to connect to the database(Does not really matter but,
    > in case you need to know, either MSSQL or ODBC ones) a few times when it
    > loads to do the CURD things. How there's 3 ways proposed to do so:
    >
    > 1) Store the connection object in a class as static object. Each
    > time the page loads, it opens(in case connection disconnected) and wait
    > until the connection is avaliable. Not closing it and use it through the
    > session.
    >
    > 2) Store the connection object in the Page, open the connection when
    > the page loads, and close as the page renders.
    >
    > 3) New the connection object on "per use" basis. Declare the object
    > in the function blocks, Open and Close within try{}finally{} control block,
    > and let the GC frees it when the procedure exits.
    >
    > Of the above designs, which one is most sound to you? And will that
    > impose heavier burden to the database? I don't have clear idea about it and
    > wish to clarify that before carrying on the next project. (Currently (3) is
    > selected as it seems neater)
    >
    > Thanks for any inputs.
    >
    > Regards,
    > Lau Lei Cheong
    >
    >
    =?ISO-8859-15?Q?Roland_M=FCller?=, Jan 13, 2006
    #2
    1. Advertising

  3. So you supports method 1?

    All I've concerned is about server loading, and because the database is
    running on the same server, I wish to strike the balance between web and
    database server's resources conservation.

    For method 1, it's good. But I have no idea
    a) open a connection a lot of time;
    b) have one connection persist through the session;
    which of the above will place more burden to the server?

    Since I don't know enough, I can't make a sure choice.

    "Roland Müller" <>
    ???????:...
    > Hi Lau Lei,
    >
    > why not make a base class and create/"store" there the connection?
    >
    > Regards, Roland
    >
    > Lau Lei Cheong schrieb:
    >> Hello,
    >>
    >> Actually I think I should have had asked it long before, but
    >> somehow I haven't.
    >>
    >> Here's the scenerio: Say we have a few pages in an ASP.NET
    >> project, each of them needs to connect to the database(Does not really
    >> matter but, in case you need to know, either MSSQL or ODBC ones) a few
    >> times when it loads to do the CURD things. How there's 3 ways proposed to
    >> do so:
    >>
    >> 1) Store the connection object in a class as static object. Each
    >> time the page loads, it opens(in case connection disconnected) and wait
    >> until the connection is avaliable. Not closing it and use it through the
    >> session.
    >>
    >> 2) Store the connection object in the Page, open the connection
    >> when the page loads, and close as the page renders.
    >>
    >> 3) New the connection object on "per use" basis. Declare the
    >> object in the function blocks, Open and Close within try{}finally{}
    >> control block, and let the GC frees it when the procedure exits.
    >>
    >> Of the above designs, which one is most sound to you? And will
    >> that impose heavier burden to the database? I don't have clear idea about
    >> it and wish to clarify that before carrying on the next project.
    >> (Currently (3) is selected as it seems neater)
    >>
    >> Thanks for any inputs.
    >>
    >> Regards,
    >> Lau Lei Cheong
    >>
    Lau Lei Cheong, Jan 13, 2006
    #3
  4. Lau Lei Cheong

    Hans Kesting Guest

    Re: =?UTF-8?B?QmFzaWMgcXVlc3Rpb24gYWJvdXQgZGF0YWJhc2UgY29ubmVjdGlv?==?UTF-8?B?biBpbiBBU1AuTkVU?=

    > Hello,
    >
    > Actually I think I should have had asked it long before, but somehow
    > I haven't.
    >
    > Here's the scenerio: Say we have a few pages in an ASP.NET project,
    > each of them needs to connect to the database(Does not really matter but, in
    > case you need to know, either MSSQL or ODBC ones) a few times when it loads
    > to do the CURD things. How there's 3 ways proposed to do so:
    >
    > 1) Store the connection object in a class as static object. Each time
    > the page loads, it opens(in case connection disconnected) and wait until the
    > connection is avaliable. Not closing it and use it through the session.
    >

    When you use a static connection object, you effectively have just a
    single connection for all your requests (of *all* users). This will be
    a bottleneck!

    > 2) Store the connection object in the Page, open the connection when
    > the page loads, and close as the page renders.
    >


    In this scenario it's difficult to make sure the connection is closed
    even when the normal program-flow is not followed.

    > 3) New the connection object on "per use" basis. Declare the object
    > in the function blocks, Open and Close within try{}finally{} control block,
    > and let the GC frees it when the procedure exits.
    >


    This is how we do it. .Net has built-in caching for the "real"
    connections, so there is no performance penalty in using Open and Close
    a lot. And because the connection is closed soon, it's quickly
    available for other requests, so you lower the total number of
    concurrent connections to the database.
    Here you can use finally to make sure the connection is closed as soon
    as you are done with it (even on crashes).

    > Of the above designs, which one is most sound to you? And will that
    > impose heavier burden to the database? I don't have clear idea about it and
    > wish to clarify that before carrying on the next project. (Currently (3) is
    > selected as it seems neater)
    >
    > Thanks for any inputs.
    >
    > Regards,
    > Lau Lei Cheong
    Hans Kesting, Jan 13, 2006
    #4
  5. Lau Lei Cheong

    Damien Guest

    Hans Kesting wrote:
    > > Hello,
    > >
    > > Actually I think I should have had asked it long before, but somehow
    > > I haven't.
    > >
    > > Here's the scenerio: Say we have a few pages in an ASP.NET project,
    > > each of them needs to connect to the database(Does not really matter but, in
    > > case you need to know, either MSSQL or ODBC ones) a few times when it loads
    > > to do the CURD things. How there's 3 ways proposed to do so:
    > >
    > > 1) Store the connection object in a class as static object. Each time
    > > the page loads, it opens(in case connection disconnected) and wait until the
    > > connection is avaliable. Not closing it and use it through the session.
    > >

    > When you use a static connection object, you effectively have just a
    > single connection for all your requests (of *all* users). This will be
    > a bottleneck!
    >
    > > 2) Store the connection object in the Page, open the connection when
    > > the page loads, and close as the page renders.
    > >

    >
    > In this scenario it's difficult to make sure the connection is closed
    > even when the normal program-flow is not followed.
    >
    > > 3) New the connection object on "per use" basis. Declare the object
    > > in the function blocks, Open and Close within try{}finally{} control block,
    > > and let the GC frees it when the procedure exits.
    > >

    >
    > This is how we do it. .Net has built-in caching for the "real"
    > connections, so there is no performance penalty in using Open and Close
    > a lot. And because the connection is closed soon, it's quickly
    > available for other requests, so you lower the total number of
    > concurrent connections to the database.
    > Here you can use finally to make sure the connection is closed as soon
    > as you are done with it (even on crashes).
    >
    > > Of the above designs, which one is most sound to you? And will that
    > > impose heavier burden to the database? I don't have clear idea about it and
    > > wish to clarify that before carrying on the next project. (Currently (3) is
    > > selected as it seems neater)
    > >
    > > Thanks for any inputs.
    > >
    > > Regards,
    > > Lau Lei Cheong


    I can only agree to that. We used to share a single connection (with
    Synclock protection around all uses). One test project had twenty
    concurrent threads all working against the database as fast as they
    could (they were using the underlying business objects which talked to
    the database). Took them four hours to complete all of the work.
    Switched to opening database connections for each seperate operation.
    The twenty threads now take ~7 minutes to complete exactly the same
    work. And it does simplify matters - you don't have to know whether the
    connection has already been opened, whether it's in use, etc. You just
    grab a connection, use it and close it.

    I would however recommend that you load the connection string once and
    share that around, if obtaining your connection string may be a slow
    operation (e.g. if it's stored encrypted in your config file). In our
    solution, we have a single global function (called CloneConnection)
    that just returns you a connection to the database. The connection
    isn't open when it's returned, so that the time for which the
    connection is opened is minimized (i.e. in cases where the connection
    is attached to the command several statements before the command is
    executed). Typical use would be (VB):

    Dim cmd As New SqlClient.SqlCommand
    cmd.Connection = Data.CloneConnection()
    cmd.CommandText = "DMS.NewDataAvailable"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandTimeout = 0
    cmd.Parameters.Add(New
    SqlClient.SqlParameter("@DataAvailable", SqlDbType.Bit, 1,
    ParameterDirection.Output, False, 0, 0, "DataAvailable",
    DataRowVersion.Current, False))
    cmd.Connection.Open()
    cmd.ExecuteNonQuery()
    cmd.Connection.Close()


    Damien
    Damien, Jan 13, 2006
    #5
  6. Lau Lei Cheong

    Dan Guest

    I also go with 3, its the way i do it. Although maybe you guys can help me
    too, and hopefully this will help as well.

    I approached this by making a database class, that class holds a string for
    the sqlconnection, the connection info etc.

    Then to make a dbconnection i just do something like

    dbObj _db = new dbObj();

    string sql = "select * from myTable";
    _db.sql = sql;

    _db.run();

    //Then inside my dbObj class i hold a temporary store of the database info
    that was returned and access it via
    // a 2 dimensional array that acts like a map of a table, so the first part
    relates to the row and the second to the column

    string _myVar = _db.getData(0,1); //this would return a string of the data
    in row 0, column 1 of my table that was returned

    or to cycle through i would do this

    for(int i=0; i<_db.MaxRows; i++)
    {
    Response.Write( _dbObj.getData(i,1); //this would loop through
    row to row to row printing out just column 1 for example
    }

    And so my conenction opens, closes nice and quick and i have one central
    place to manage all my database functionality.

    Anyway my issue i have had is to do with concurrent users, doing this my way
    it should not be possible that one user can effect another, however when 2
    people were changing some data using the same form one of them set a var
    that overrided the others.

    So how did the connections somehow cross with the methodology above? As i
    always make a new instance surely this is not possible? Have i missed
    something?
    Dan, Jan 13, 2006
    #6
  7. Lau Lei Cheong

    Damien Guest

    Dan wrote:
    > I also go with 3, its the way i do it. Although maybe you guys can help me
    > too, and hopefully this will help as well.
    >
    > I approached this by making a database class, that class holds a string for
    > the sqlconnection, the connection info etc.
    >
    > Then to make a dbconnection i just do something like
    >
    > dbObj _db = new dbObj();
    >
    > string sql = "select * from myTable";
    > _db.sql = sql;
    >
    > _db.run();
    >
    > //Then inside my dbObj class i hold a temporary store of the database info
    > that was returned and access it via
    > // a 2 dimensional array that acts like a map of a table, so the first part
    > relates to the row and the second to the column
    >
    > string _myVar = _db.getData(0,1); //this would return a string of the data
    > in row 0, column 1 of my table that was returned
    >
    > or to cycle through i would do this
    >
    > for(int i=0; i<_db.MaxRows; i++)
    > {
    > Response.Write( _dbObj.getData(i,1); //this would loop through
    > row to row to row printing out just column 1 for example
    > }
    >
    > And so my conenction opens, closes nice and quick and i have one central
    > place to manage all my database functionality.
    >
    > Anyway my issue i have had is to do with concurrent users, doing this my way
    > it should not be possible that one user can effect another, however when 2
    > people were changing some data using the same form one of them set a var
    > that overrided the others.
    >
    > So how did the connections somehow cross with the methodology above? As i
    > always make a new instance surely this is not possible? Have i missed
    > something?


    Hi Dan,

    Without seeing the code for the dbObj class, it's going to be quite
    tricky to debug this one. By the way, what benefits do you perceive
    this method has over using, say, a dataset?

    Damien
    Damien, Jan 13, 2006
    #7
  8. Thanks Hans and Damien. :)

    The information provided by both of you is very helpful.

    "Damien" <>
    ???????:...
    > Hans Kesting wrote:
    >> > Hello,
    >> >
    >> > Actually I think I should have had asked it long before, but
    >> > somehow
    >> > I haven't.
    >> >
    >> > Here's the scenerio: Say we have a few pages in an ASP.NET
    >> > project,
    >> > each of them needs to connect to the database(Does not really matter
    >> > but, in
    >> > case you need to know, either MSSQL or ODBC ones) a few times when it
    >> > loads
    >> > to do the CURD things. How there's 3 ways proposed to do so:
    >> >
    >> > 1) Store the connection object in a class as static object.
    >> > Each time
    >> > the page loads, it opens(in case connection disconnected) and wait
    >> > until the
    >> > connection is avaliable. Not closing it and use it through the session.
    >> >

    >> When you use a static connection object, you effectively have just a
    >> single connection for all your requests (of *all* users). This will be
    >> a bottleneck!
    >>
    >> > 2) Store the connection object in the Page, open the connection
    >> > when
    >> > the page loads, and close as the page renders.
    >> >

    >>
    >> In this scenario it's difficult to make sure the connection is closed
    >> even when the normal program-flow is not followed.
    >>
    >> > 3) New the connection object on "per use" basis. Declare the
    >> > object
    >> > in the function blocks, Open and Close within try{}finally{} control
    >> > block,
    >> > and let the GC frees it when the procedure exits.
    >> >

    >>
    >> This is how we do it. .Net has built-in caching for the "real"
    >> connections, so there is no performance penalty in using Open and Close
    >> a lot. And because the connection is closed soon, it's quickly
    >> available for other requests, so you lower the total number of
    >> concurrent connections to the database.
    >> Here you can use finally to make sure the connection is closed as soon
    >> as you are done with it (even on crashes).
    >>
    >> > Of the above designs, which one is most sound to you? And will
    >> > that
    >> > impose heavier burden to the database? I don't have clear idea about it
    >> > and
    >> > wish to clarify that before carrying on the next project. (Currently
    >> > (3) is
    >> > selected as it seems neater)
    >> >
    >> > Thanks for any inputs.
    >> >
    >> > Regards,
    >> > Lau Lei Cheong

    >
    > I can only agree to that. We used to share a single connection (with
    > Synclock protection around all uses). One test project had twenty
    > concurrent threads all working against the database as fast as they
    > could (they were using the underlying business objects which talked to
    > the database). Took them four hours to complete all of the work.
    > Switched to opening database connections for each seperate operation.
    > The twenty threads now take ~7 minutes to complete exactly the same
    > work. And it does simplify matters - you don't have to know whether the
    > connection has already been opened, whether it's in use, etc. You just
    > grab a connection, use it and close it.
    >
    > I would however recommend that you load the connection string once and
    > share that around, if obtaining your connection string may be a slow
    > operation (e.g. if it's stored encrypted in your config file). In our
    > solution, we have a single global function (called CloneConnection)
    > that just returns you a connection to the database. The connection
    > isn't open when it's returned, so that the time for which the
    > connection is opened is minimized (i.e. in cases where the connection
    > is attached to the command several statements before the command is
    > executed). Typical use would be (VB):
    >
    > Dim cmd As New SqlClient.SqlCommand
    > cmd.Connection = Data.CloneConnection()
    > cmd.CommandText = "DMS.NewDataAvailable"
    > cmd.CommandType = CommandType.StoredProcedure
    > cmd.CommandTimeout = 0
    > cmd.Parameters.Add(New
    > SqlClient.SqlParameter("@DataAvailable", SqlDbType.Bit, 1,
    > ParameterDirection.Output, False, 0, 0, "DataAvailable",
    > DataRowVersion.Current, False))
    > cmd.Connection.Open()
    > cmd.ExecuteNonQuery()
    > cmd.Connection.Close()
    >
    >
    > Damien
    >
    Lau Lei Cheong, Jan 14, 2006
    #8
    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. Newbie

    Basic question b/w ASP & ASP .NET

    Newbie, Jul 29, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    1,844
    Steve C. Orr, MCSD
    Jul 30, 2003
  2. prasanna

    asp.net mysql database connection

    prasanna, Oct 15, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    577
    prasanna
    Oct 15, 2003
  3. Louise Hadley

    OLEDB database connection from ASP.NET

    Louise Hadley, Apr 16, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    7,501
    Louise Hadley
    Apr 17, 2004
  4. Mythran
    Replies:
    5
    Views:
    4,939
    Mythran
    Oct 5, 2005
  5. Jennifer

    ASP & Database Connection Question

    Jennifer, May 19, 2006, in forum: ASP General
    Replies:
    1
    Views:
    114
    Ron Hinds
    May 19, 2006
Loading...

Share This Page