is this a good way if I make a sql connection in global.aspx?

[Quentin Huo]

Hi:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Marina]

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users of
your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one of the
users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and then
all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

 

[Quentin Huo]

Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always open, and
never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users of
your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one of the
users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and then
all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Steve C. Orr [MVP, MCSD]]

The main problem is you're trying to funnel all your database communication
for all pages and all users through a single database connection. That is
one heck of a bottleneck.
ADO.NET has built in connection pooling that is actually quite efficient.
Let it do its job.
The best practice in ASP.NET is to open a connection just before you use it,
and to close it immediately afterward.
You might want to put your connection string into application state from
your Global.asax though.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always open, and
never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users of
your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one of the
users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and then
all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Rudi Ahlers]

But this is quite a valid question:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

What happens if you do have a website is is DB driven only. Each page makes
DB constant calls, thus not really releaving the server of it's load?
Does it really free up some reasources if you op the page (which does the DB
quesries) view it, and the click on another page, which queries the DB yet
again?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always open, and
never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users of
your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one of the
users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and then
all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Marina]

But in between the page calls there is a lot of time where the connection
isn't doing anything. And let's say you have 200 users going to your site -
you are going to keep 200 connections open at all times? This would be if
you were doing it by user, not one global connection that was being done
here (as that would fail for thread concurrency reasons, as one connection
can only execute one query at a time). And you would have to clean it up in
Session_End - but what is the length of the session? The default 20 minutes?
Or 2 hours? So you have a user come in, view a page, and then leave, but
the session is still there. So for the next 2 hours you have an open
database connection just sitting there, while others users are trying to
connect? Which they won't be able to, if you are giving hundreds of users
their own personal database connection that remains open for the lifetime of
the session.

But this is quite a valid question:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

What happens if you do have a website is is DB driven only. Each page makes
DB constant calls, thus not really releaving the server of it's load?
Does it really free up some reasources if you op the page (which does the DB
quesries) view it, and the click on another page, which queries the DB yet
again?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always open, and
never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users of
your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one of the
users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and then
all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Rudi Ahlers]

Ok, good point
and good education

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
But in between the page calls there is a lot of time where the connection
isn't doing anything. And let's say you have 200 users going to your site -
you are going to keep 200 connections open at all times? This would be if
you were doing it by user, not one global connection that was being done
here (as that would fail for thread concurrency reasons, as one connection
can only execute one query at a time). And you would have to clean it up in
Session_End - but what is the length of the session? The default 20 minutes?
Or 2 hours? So you have a user come in, view a page, and then leave, but
the session is still there. So for the next 2 hours you have an open
database connection just sitting there, while others users are trying to
connect? Which they won't be able to, if you are giving hundreds of users
their own personal database connection that remains open for the lifetime of
the session.

But this is quite a valid question:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

What happens if you do have a website is is DB driven only. Each page makes
DB constant calls, thus not really releaving the server of it's load?
Does it really free up some reasources if you op the page (which does the DB
quesries) view it, and the click on another page, which queries the DB yet
again?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always open, and
never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users of
your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one of the
users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and then
all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Quentin Huo]

Hi, thanks all!

According to you, if there is an opened session and the session has an
always-opened connection to the database, nobody else can access the website
because nobody can setup the connection to the database. But the fact is I
can access the website even there have been 150 sessions existing.

Maybe I misunderstood you.

Maybe my website is working on ASP, not ASP.NET.

Global page is written by somebody else, and now I am trying to move to
ASP.NET. I think you are right, but the fact makes me feel a little bit
confusing, in fact, there is one always-opened connection in Application
level and one always-opened connection in session level. I believe There
should be the same issue in either ASP or ASP.NET.

Q.

But in between the page calls there is a lot of time where the connection
isn't doing anything. And let's say you have 200 users going to your site -
you are going to keep 200 connections open at all times? This would be if
you were doing it by user, not one global connection that was being done
here (as that would fail for thread concurrency reasons, as one connection
can only execute one query at a time). And you would have to clean it up in
Session_End - but what is the length of the session? The default 20 minutes?
Or 2 hours? So you have a user come in, view a page, and then leave, but
the session is still there. So for the next 2 hours you have an open
database connection just sitting there, while others users are trying to
connect? Which they won't be able to, if you are giving hundreds of users
their own personal database connection that remains open for the lifetime of
the session.

But this is quite a valid question:



What happens if you do have a website is is DB driven only. Each page makes
DB constant calls, thus not really releaving the server of it's load?
Does it really free up some reasources if you op the page (which does

the

DB
quesries) view it, and the click on another page, which queries the DB yet
again?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always open, and
never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users of
your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one of the
users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and then
all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good way that
open a sql connection when application starts in the global.aspx, like
this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Marina]

No, according to me, if there is one connection shared for the whole site
(not per session), then if two requests come in simultaneously, one of them
will error out because they will both be trying to run queries on the same
connection at the same exact time.

Hi, thanks all!

According to you, if there is an opened session and the session has an
always-opened connection to the database, nobody else can access the website
because nobody can setup the connection to the database. But the fact is I
can access the website even there have been 150 sessions existing.

Maybe I misunderstood you.

Maybe my website is working on ASP, not ASP.NET.

Global page is written by somebody else, and now I am trying to move to
ASP.NET. I think you are right, but the fact makes me feel a little bit
confusing, in fact, there is one always-opened connection in Application
level and one always-opened connection in session level. I believe There
should be the same issue in either ASP or ASP.NET.

Q.

But in between the page calls there is a lot of time where the connection
isn't doing anything. And let's say you have 200 users going to your site -
you are going to keep 200 connections open at all times? This would be if
you were doing it by user, not one global connection that was being done
here (as that would fail for thread concurrency reasons, as one connection
can only execute one query at a time). And you would have to clean it up in
Session_End - but what is the length of the session? The default 20 minutes?
Or 2 hours? So you have a user come in, view a page, and then leave, but
the session is still there. So for the next 2 hours you have an open
database connection just sitting there, while others users are trying to
connect? Which they won't be able to, if you are giving hundreds of users
their own personal database connection that remains open for the

lifetime

of
the session.

But this is quite a valid question:



What happens if you do have a website is is DB driven only. Each page makes
DB constant calls, thus not really releaving the server of it's load?
Does it really free up some reasources if you op the page (which does

the

DB
quesries) view it, and the click on another page, which queries the DB yet
again?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always open, and
never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL

users
of

your application. Invariably, two users will do something that will cause
the server to try to use that connection at the same time, and one

of
the

users will get an error.
2) The connection is always open, and never closed. You should keep the
connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data,

and
then

all the pages call these routines to do work for the. Each routine creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good

way
that

open a sql connection when application starts in the global.aspx, like
this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.

 

[Quentin Huo]

Thank you very much!

Q.

No, according to me, if there is one connection shared for the whole site
(not per session), then if two requests come in simultaneously, one of them
will error out because they will both be trying to run queries on the same
connection at the same exact time.

Hi, thanks all!

According to you, if there is an opened session and the session has an
always-opened connection to the database, nobody else can access the website
because nobody can setup the connection to the database. But the fact is I
can access the website even there have been 150 sessions existing.

Maybe I misunderstood you.

Maybe my website is working on ASP, not ASP.NET.

Global page is written by somebody else, and now I am trying to move to
ASP.NET. I think you are right, but the fact makes me feel a little bit
confusing, in fact, there is one always-opened connection in Application
level and one always-opened connection in session level. I believe There
should be the same issue in either ASP or ASP.NET.

Q.

be

if

you were doing it by user, not one global connection that was being done
here (as that would fail for thread concurrency reasons, as one connection
can only execute one query at a time). And you would have to clean it

up
in

Session_End - but what is the length of the session? The default 20 minutes?
Or 2 hours? So you have a user come in, view a page, and then leave, but
the session is still there. So for the next 2 hours you have an open
database connection just sitting there, while others users are trying to
connect? Which they won't be able to, if you are giving hundreds of users
their own personal database connection that remains open for the

lifetime

of
the session.


But this is quite a valid question:



What happens if you do have a website is is DB driven only. Each page
makes
DB constant calls, thus not really releaving the server of it's load?
Does it really free up some reasources if you op the page (which

does
the

DB
quesries) view it, and the click on another page, which queries the

DB
yet

again?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)
Hi,

Thank you very much for your quick answer.

I want to know what the problem is if "The connection is always

open,
and

never closed". Does it reduce the performance of the server?

Q.

No, that's a terrible horrible way to do it.

1) You have one instances of the connection, being shared by ALL users
of
your application. Invariably, two users will do something that will
cause
the server to try to use that connection at the same time, and one of
the
users will get an error.
2) The connection is always open, and never closed. You should

keep
the

connection open for just as long as you need it, and then close.

A better way would be to have common routines that get/save data, and
then
all the pages call these routines to do work for the. Each routine
creates
and opens a connection, does its work, and then closes it.

Hi:

Almost all my pages get data from the database, so is it a good way
that
open a sql connection when application starts in the

global.aspx,
like

this:

protected void Application_Start(Object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("......");

conn.Open();

Application["myConnection"] = conn;

}

so that I can use this connection in each page, like:

SqlDataAdapter myAdapter =

new SqlDataAdapter(sqlString,
(SqlConnection)Application["myConnection"]);



or I make a connection in each page?



Which one is better?



Thanks

Q.