Basic question about database connection in ASP.NET

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

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

Hi Lau Lei,

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

Regards, Roland

 

[Lau Lei Cheong]

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.

 

[Hans Kesting]

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

 

[Damien]

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!


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


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

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

 

[Dan]

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?

 

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

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

 

[Lau Lei Cheong]

Thanks Hans and Damien.

The information provided by both of you is very helpful.