> 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
>
>

> 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
>>

>         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

> > 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 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
>

> 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?