By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,152 Members | 2,039 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,152 IT Pros & Developers. It's quick & easy.

Making a database connection global

P: n/a
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want to
reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens and
closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.
May 12 '06 #1
Share this Question
Share on Google+
35 Replies


P: n/a
Does this static database connection object stay open throughout the entire
lifetime of the application? It is typically not recommended to keep a
database connection open for an extended period of time. Usually, you want
to open the connection, execute a command, and close the connection
immediately.

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want to
reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.

May 12 '06 #2

P: n/a
"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want to
reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?


Yes - pretty much anything else would be better than what you're proposing.

If you did this you would quickly run into problems, as every user that
requests a page will be sharing the same connection. That means that only
one user at a time can request a page, or you will get an error message
because the connection is busy. Every page that uses the database needs its
own connection. This is one of the worst things you can do in ASP.NET - the
absolute LAST thing you should do is to keep a connection to your RDBMS open
for any longer than is necessary, let alone for the entire lifetime of your
web app.

Consider using a DAL (Data Access Layer) instead:
http://www.15seconds.com/issue/030317.htm
May 12 '06 #3

P: n/a
well this is design question ;)

my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object

Hope this helps

Galin Iliev[MCSD.NET]
www.galcho.com

May 12 '06 #4

P: n/a

With the web:

Open as late as possible.
Close as quick as possible.

I'd recommend looking into the EnterpriseLibrary (Data esp)
http://www.gotdotnet.com/codegallery...2-91be63527327

They have already put in alot of best practices.

But regardless, global db connection is a terrible idea.

Pooling is a seperate issue.

I'd recommend
ISBN
159059522X
Expert ASP.NET 2.0 Advanced Application Design

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want to
reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.

May 12 '06 #5

P: n/a
No the connection is only open when needed --

For instances: One class (business logic) may reference several (table)
classes, perform whatever business logic is needed and set the (table)
classes to null and close the database connection.
"Michael Bosch" <do***********@gmail.com> wrote in message
news:ES*******************@bignews2.bellsouth.net. ..
Does this static database connection object stay open throughout the
entire lifetime of the application? It is typically not recommended to
keep a database connection open for an extended period of time. Usually,
you want to open the connection, execute a command, and close the
connection immediately.

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want
to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.


May 12 '06 #6

P: n/a
Bad idea. Open and close connections as quickly as possible to leverage
built-in connection pooling properly.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want to
reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.

May 12 '06 #7

P: n/a
The connection would not be opened the lifetime of the web app. There is no
difference in having each class open the connection and close it (30+) where
as I open the connection just once -- have the business logic (which will
open many tables) perform whatever functions are necessary, set the (table)
classes to null, then close the one database connection. Does this make more
sense?
"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:e7**************@TK2MSFTNGP05.phx.gbl...
"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want
to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?


Yes - pretty much anything else would be better than what you're
proposing.

If you did this you would quickly run into problems, as every user that
requests a page will be sharing the same connection. That means that only
one user at a time can request a page, or you will get an error message
because the connection is busy. Every page that uses the database needs
its own connection. This is one of the worst things you can do in
ASP.NET - the absolute LAST thing you should do is to keep a connection to
your RDBMS open for any longer than is necessary, let alone for the entire
lifetime of your web app.

Consider using a DAL (Data Access Layer) instead:
http://www.15seconds.com/issue/030317.htm

May 12 '06 #8

P: n/a
"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
There is no difference in having each class open the connection and close
it (30+) where as I open the connection just once --
Yes there is - don't do it.
Does this make more sense?


No.
May 13 '06 #9

P: n/a
"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
No the connection is only open when needed --


And what if 100 pages need it at the same time...?
May 13 '06 #10

P: n/a
you synchronize - which you would have to do under any circumstances.
"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:eO**************@TK2MSFTNGP05.phx.gbl...
"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
No the connection is only open when needed --


And what if 100 pages need it at the same time...?

May 14 '06 #11

P: n/a
One business class may reference 10+ classes (which are tables). The
connection would onlly be open while accessing a one or more tables. The
connection would never be open if it was not accessing a table. So, you're
saying open 10+ connections -- one for each table? I could have ten
connections and ten tables open at the same time -- that doesn't make sense
to me - which the business is accessing?

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:ee**************@TK2MSFTNGP04.phx.gbl...
Bad idea. Open and close connections as quickly as possible to leverage
built-in connection pooling properly.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want
to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.


May 14 '06 #12

P: n/a
So, in your opinion it would be better to open 30+ connections (one for each
table class) even though the time of open connection would be the same (as a
one global connection). What I saying is there is a possibility all 30
tables may be open at the same time, so that would be 30 connections as
well, where as there could be one connection open with 30 tables?
"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:u%****************@TK2MSFTNGP05.phx.gbl...
"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
There is no difference in having each class open the connection and close
it (30+) where as I open the connection just once --


Yes there is - don't do it.
Does this make more sense?


No.

May 14 '06 #13

P: n/a
I like your idea and will give it thought.

Thanks!
"Galin Iliev [MCSD.NET]" <ga****@gmail.com> wrote in message
news:11**********************@d71g2000cwd.googlegr oups.com...
well this is design question ;)

my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object

Hope this helps

Galin Iliev[MCSD.NET]
www.galcho.com

May 14 '06 #14

P: n/a
"Terry Jolly" <te***@ipmas.com> wrote in message
news:w4*******************@tornado.texas.rr.com...
So, in your opinion it would be better to open 30+ connections (one for
each table class) even though the time of open connection would be the
same (as a one global connection). What I saying is there is a possibility
all 30 tables may be open at the same time, so that would be 30
connections as well, where as there could be one connection open with 30
tables?


1) You ask the group for its opinions on what you are intend doing - that's
fine.

2) Every response told you that what you intend doing is a bad idea - which
it is.

3) You remain convinced that you are right and everyone else is wrong -
that's your prerogative.
May 14 '06 #15

P: n/a
No, I'm saying that the .Net Framework uses Connection Pooling. A Connection
instance is simply an encapsulation of an underlying database connection.
The Framework manages the actual connections. You just use separate
instances. If you do not understand this, you certainly are not in a
position to do it any other way than the recommended way. Otherwise, you
will get yourself into trouble.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be

"Terry Jolly" <te***@ipmas.com> wrote in message
news:pm******************@tornado.texas.rr.com...
One business class may reference 10+ classes (which are tables). The
connection would onlly be open while accessing a one or more tables. The
connection would never be open if it was not accessing a table. So, you're
saying open 10+ connections -- one for each table? I could have ten
connections and ten tables open at the same time -- that doesn't make
sense to me - which the business is accessing?

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:ee**************@TK2MSFTNGP04.phx.gbl...
Bad idea. Open and close connections as quickly as possible to leverage
built-in connection pooling properly.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want
to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.



May 14 '06 #16

P: n/a
Using only one single database connection for a web application? Good one...

You could just as well neuter the web server by only allowing one single
thread, as the other threads only will be waiting for the connection to
get free.

What database are you using? If you are only going to use a single
connection against it you should consider downgrading to something
low-grade like MSDE or Access. Or why not anything even simpler like
storing the data in text files?
Terry Jolly wrote:
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want to
reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens and
closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.

May 14 '06 #17

P: n/a
Hi Göran

Sarcasm like this isn't neccessary. Someone asked a question to
something that he/she obviously didn't know the correct answer for.
It doesn't help answering the question in this sort of vein.

Regards
Ray

Göran Andersson wrote:
Using only one single database connection for a web application? Good
one...

You could just as well neuter the web server by only allowing one single
thread, as the other threads only will be waiting for the connection to
get free.

What database are you using? If you are only going to use a single
connection against it you should consider downgrading to something
low-grade like MSDE or Access. Or why not anything even simpler like
storing the data in text files?
Terry Jolly wrote:
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only
want to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class
opens and closes the database.

This seems to work so the question: Is there a better way to handle
the database connection?

Thanks In Advance.

May 14 '06 #18

P: n/a
I think all Jolly aked is how to leverage connection from single point
wihtout having to deal with DbConnection object in all classes.
This is reasonable question as this makes system easier to support and
could reduce effort in adding additional code to SQl connection object.
Regards
Galin Iliev[MCSD.NET]
www.galcho.com

May 14 '06 #19

P: n/a
Yes, you are probably right.

He got a lot of answers, but he didn't seem to listen to anyone who
didn't already agree with him. Mark Rae even answered him using my own
words, so there wasn't much I could add to that. I thought that I'd try
a different angle, but I might have taken it a bit too far.

Anyway, if he don't want to listen, a different angle will probably not
work anyway.

Ray Booysen wrote:
Hi Göran

Sarcasm like this isn't neccessary. Someone asked a question to
something that he/she obviously didn't know the correct answer for.
It doesn't help answering the question in this sort of vein.

Regards
Ray

Göran Andersson wrote:
Using only one single database connection for a web application? Good
one...

You could just as well neuter the web server by only allowing one
single thread, as the other threads only will be waiting for the
connection to get free.

What database are you using? If you are only going to use a single
connection against it you should consider downgrading to something
low-grade like MSDE or Access. Or why not anything even simpler like
storing the data in text files?
Terry Jolly wrote:
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only
want to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class
opens and closes the database.

This seems to work so the question: Is there a better way to handle
the database connection?

Thanks In Advance.

May 14 '06 #20

P: n/a
You're wrong - that I think anybody is wrong - I merely question the logic -
and give anyone the abiltiy (or rtaher ask anyone) to explain their
position. It's one thing to say I'm wrong, give no answer to a different way
and become upset if your way (which in not explained) is questioned.

I'm listening to every point of view, thinking it through and may question
that point of view for (my better) understanding. Don't be afraid to have
you view questioned, it's not saying you are wrong.

"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:ev**************@TK2MSFTNGP04.phx.gbl...
"Terry Jolly" <te***@ipmas.com> wrote in message
news:w4*******************@tornado.texas.rr.com...
So, in your opinion it would be better to open 30+ connections (one for
each table class) even though the time of open connection would be the
same (as a one global connection). What I saying is there is a
possibility all 30 tables may be open at the same time, so that would be
30 connections as well, where as there could be one connection open with
30 tables?


1) You ask the group for its opinions on what you are intend doing -
that's fine.

2) Every response told you that what you intend doing is a bad idea -
which it is.

3) You remain convinced that you are right and everyone else is wrong -
that's your prerogative.

May 14 '06 #21

P: n/a
No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening 30+
connections made more sense than one connection if in both cases the
connection would be opened the same amount of time (which no-one yas yet
answered). I really would like to hear another point of view. I have not
discounted anything anyone has said. Someone did say:
my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object
Which I like and am (have been) diagraph/mapping out to see if this would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.


"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl... Yes, you are probably right.

He got a lot of answers, but he didn't seem to listen to anyone who didn't
already agree with him. Mark Rae even answered him using my own words, so
there wasn't much I could add to that. I thought that I'd try a different
angle, but I might have taken it a bit too far.

Anyway, if he don't want to listen, a different angle will probably not
work anyway.

Ray Booysen wrote:
Hi Göran

Sarcasm like this isn't neccessary. Someone asked a question to something
that he/she obviously didn't know the correct answer for.
It doesn't help answering the question in this sort of vein.

Regards
Ray

Göran Andersson wrote:
Using only one single database connection for a web application? Good
one...

You could just as well neuter the web server by only allowing one single
thread, as the other threads only will be waiting for the connection to
get free.

What database are you using? If you are only going to use a single
connection against it you should consider downgrading to something
low-grade like MSDE or Access. Or why not anything even simpler like
storing the data in text files?
Terry Jolly wrote:
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want
to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.

May 14 '06 #22

P: n/a
Thank you...I will re-review connection pooling. I thought I had read
(earlier) there was a limited amount of connections available, which made me
think what I was (thinkning about) doing made more sense. The connection
would NOT be open longer in the method I was suggesting. Please do not
mis-understand I am not trying to challneging you. I'm just trying to
understand why 30 connections opened in a given time would be better than 1
connection. Again the one connection would not be opened longer than if each
class has it's own connection. Plus if you have 10 hits to the site then 10
* 30 possible connectios?

Basically, I would open one global connection, perform any work with the 30
classes (tables) and immedtaley close the global connection. If you think
this is a bad idea, then what would you do differently?

If you think i'm an idiot, thats fine, enlighted me.

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:uw**************@TK2MSFTNGP05.phx.gbl...
No, I'm saying that the .Net Framework uses Connection Pooling. A
Connection instance is simply an encapsulation of an underlying database
connection. The Framework manages the actual connections. You just use
separate instances. If you do not understand this, you certainly are not
in a position to do it any other way than the recommended way. Otherwise,
you will get yourself into trouble.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be

"Terry Jolly" <te***@ipmas.com> wrote in message
news:pm******************@tornado.texas.rr.com...
One business class may reference 10+ classes (which are tables). The
connection would onlly be open while accessing a one or more tables. The
connection would never be open if it was not accessing a table. So,
you're saying open 10+ connections -- one for each table? I could have
ten connections and ten tables open at the same time -- that doesn't make
sense to me - which the business is accessing?

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:ee**************@TK2MSFTNGP04.phx.gbl...
Bad idea. Open and close connections as quickly as possible to leverage
built-in connection pooling properly.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
Web Solution

Goal: Have a global database connection
Why: (There will be 30+ tables, represented by 30+ classes) I only want
to reference the database connection once.

I put the connection string in the web.config.
I created a class with a static database connection and the class opens
and closes the database.

This seems to work so the question: Is there a better way to handle the
database connection?

Thanks In Advance.



May 14 '06 #23

P: n/a
Ok, I'll try without sarcasm. ;)

The web server is built to process several web pages simultaneously
using mutliple threads. The database is built to process several queries
simultaneously using multiple sessions. If you use only one connection
in your application, that will become the bottle neck of the
application. Whatever you do, it will always boil down to the fact that
only one thread at a time can access the database, and all the other
threads have to wait their turn.

Open a connection for each page, and use that for the database
operations in the page. When you close the connection it will be
returned to the connection pool. Connections are only costly when you
create them the first time. When there is a connection in the connection
pool that can be reused, opening it is much faster.

Terry Jolly wrote:
No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening 30+
connections made more sense than one connection if in both cases the
connection would be opened the same amount of time (which no-one yas yet
answered). I really would like to hear another point of view. I have not
discounted anything anyone has said. Someone did say:
my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object


Which I like and am (have been) diagraph/mapping out to see if this would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl...
Yes, you are probably right.

He got a lot of answers, but he didn't seem to listen to anyone who didn't
already agree with him. Mark Rae even answered him using my own words, so
there wasn't much I could add to that. I thought that I'd try a different
angle, but I might have taken it a bit too far.

Anyway, if he don't want to listen, a different angle will probably not
work anyway.

Ray Booysen wrote:
Hi Göran

Sarcasm like this isn't neccessary. Someone asked a question to something
that he/she obviously didn't know the correct answer for.
It doesn't help answering the question in this sort of vein.

Regards
Ray

Göran Andersson wrote:
Using only one single database connection for a web application? Good
one...

You could just as well neuter the web server by only allowing one single
thread, as the other threads only will be waiting for the connection to
get free.

What database are you using? If you are only going to use a single
connection against it you should consider downgrading to something
low-grade like MSDE or Access. Or why not anything even simpler like
storing the data in text files?
Terry Jolly wrote:
> Web Solution
>
> Goal: Have a global database connection
> Why: (There will be 30+ tables, represented by 30+ classes) I only want
> to reference the database connection once.
>
> I put the connection string in the web.config.
> I created a class with a static database connection and the class opens
> and closes the database.
>
> This seems to work so the question: Is there a better way to handle the
> database connection?
>
> Thanks In Advance.

May 14 '06 #24

P: n/a
Look at it this way....

Which do you think is quicker - 30 people lined up at one ticket booth to
pay movie admission, or 30 people lined up at 30 ticket booths.

As a bonus, with 30 ticket booths, the attendants knowin advance which movie
you want to see and have your ticket ready.

Bob Lehmann

"Terry Jolly" <te***@ipmas.com> wrote in message
news:dt*****************@tornado.texas.rr.com...
Thank you...I will re-review connection pooling. I thought I had read
(earlier) there was a limited amount of connections available, which made me think what I was (thinkning about) doing made more sense. The connection
would NOT be open longer in the method I was suggesting. Please do not
mis-understand I am not trying to challneging you. I'm just trying to
understand why 30 connections opened in a given time would be better than 1 connection. Again the one connection would not be opened longer than if each class has it's own connection. Plus if you have 10 hits to the site then 10 * 30 possible connectios?

Basically, I would open one global connection, perform any work with the 30 classes (tables) and immedtaley close the global connection. If you think
this is a bad idea, then what would you do differently?

If you think i'm an idiot, thats fine, enlighted me.

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:uw**************@TK2MSFTNGP05.phx.gbl...
No, I'm saying that the .Net Framework uses Connection Pooling. A
Connection instance is simply an encapsulation of an underlying database
connection. The Framework manages the actual connections. You just use
separate instances. If you do not understand this, you certainly are not
in a position to do it any other way than the recommended way. Otherwise, you will get yourself into trouble.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be

"Terry Jolly" <te***@ipmas.com> wrote in message
news:pm******************@tornado.texas.rr.com...
One business class may reference 10+ classes (which are tables). The
connection would onlly be open while accessing a one or more tables. The connection would never be open if it was not accessing a table. So,
you're saying open 10+ connections -- one for each table? I could have
ten connections and ten tables open at the same time -- that doesn't make sense to me - which the business is accessing?

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:ee**************@TK2MSFTNGP04.phx.gbl...
Bad idea. Open and close connections as quickly as possible to leverage built-in connection pooling properly.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

"Terry Jolly" <te***@ipmas.com> wrote in message
news:44**********************@news.twtelecom.net.. .
> Web Solution
>
> Goal: Have a global database connection
> Why: (There will be 30+ tables, represented by 30+ classes) I only want> to reference the database connection once.
>
> I put the connection string in the web.config.
> I created a class with a static database connection and the class opens> and closes the database.
>
> This seems to work so the question: Is there a better way to handle the> database connection?
>
> Thanks In Advance.
>
>



May 14 '06 #25

P: n/a
> my suggestion is having one class that just handles commands to
database.
You misunderstand.

Having one class to handle *many* connection *instances* is correct. Having
one class, with one single *shared* connection, is bad.

Bob Lehmann

"Terry Jolly" <te***@ipmas.com> wrote in message
news:n1*******************@tornado.texas.rr.com... No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening 30+ connections made more sense than one connection if in both cases the
connection would be opened the same amount of time (which no-one yas yet
answered). I really would like to hear another point of view. I have not
discounted anything anyone has said. Someone did say:
my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object


Which I like and am (have been) diagraph/mapping out to see if this would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.


"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl...
Yes, you are probably right.

He got a lot of answers, but he didn't seem to listen to anyone who didn't already agree with him. Mark Rae even answered him using my own words, so there wasn't much I could add to that. I thought that I'd try a different angle, but I might have taken it a bit too far.

Anyway, if he don't want to listen, a different angle will probably not
work anyway.

Ray Booysen wrote:
Hi Göran

Sarcasm like this isn't neccessary. Someone asked a question to something that he/she obviously didn't know the correct answer for.
It doesn't help answering the question in this sort of vein.

Regards
Ray

Göran Andersson wrote:
Using only one single database connection for a web application? Good
one...

You could just as well neuter the web server by only allowing one single thread, as the other threads only will be waiting for the connection to get free.

What database are you using? If you are only going to use a single
connection against it you should consider downgrading to something
low-grade like MSDE or Access. Or why not anything even simpler like
storing the data in text files?
Terry Jolly wrote:
> Web Solution
>
> Goal: Have a global database connection
> Why: (There will be 30+ tables, represented by 30+ classes) I only want> to reference the database connection once.
>
> I put the connection string in the web.config.
> I created a class with a static database connection and the class opens> and closes the database.
>
> This seems to work so the question: Is there a better way to handle the> database connection?
>
> Thanks In Advance.


May 14 '06 #26

P: n/a
"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl...
Mark Rae even answered him using my own words, so there wasn't much I
could add to that.


LOL! Yes, I should have credited you for that - sorry...
May 14 '06 #27

P: n/a
"Göran Andersson" <gu***@guffa.com> wrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
If you use only one connection in your application, that will become the
bottle neck of the application. Whatever you do, it will always boil down
to the fact that only one thread at a time can access the database, and
all the other threads have to wait their turn.


I think we're all wasting our time with this one...
May 14 '06 #28

P: n/a
Thanks for explanation - makes sense!

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:er**************@TK2MSFTNGP03.phx.gbl...
Look at it this way....

Which do you think is quicker - 30 people lined up at one ticket booth to
pay movie admission, or 30 people lined up at 30 ticket booths.

As a bonus, with 30 ticket booths, the attendants knowin advance which
movie
you want to see and have your ticket ready.

Bob Lehmann

"Terry Jolly" <te***@ipmas.com> wrote in message
news:dt*****************@tornado.texas.rr.com...
Thank you...I will re-review connection pooling. I thought I had read
(earlier) there was a limited amount of connections available, which made

me
think what I was (thinkning about) doing made more sense. The connection
would NOT be open longer in the method I was suggesting. Please do not
mis-understand I am not trying to challneging you. I'm just trying to
understand why 30 connections opened in a given time would be better than

1
connection. Again the one connection would not be opened longer than if

each
class has it's own connection. Plus if you have 10 hits to the site then

10
* 30 possible connectios?

Basically, I would open one global connection, perform any work with the

30
classes (tables) and immedtaley close the global connection. If you think
this is a bad idea, then what would you do differently?

If you think i'm an idiot, thats fine, enlighted me.

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:uw**************@TK2MSFTNGP05.phx.gbl...
> No, I'm saying that the .Net Framework uses Connection Pooling. A
> Connection instance is simply an encapsulation of an underlying
> database
> connection. The Framework manages the actual connections. You just use
> separate instances. If you do not understand this, you certainly are
> not
> in a position to do it any other way than the recommended way. Otherwise, > you will get yourself into trouble.
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> .Net Developer
> Neither a follower nor a lender be
>
> "Terry Jolly" <te***@ipmas.com> wrote in message
> news:pm******************@tornado.texas.rr.com...
>> One business class may reference 10+ classes (which are tables). The
>> connection would onlly be open while accessing a one or more tables. The >> connection would never be open if it was not accessing a table. So,
>> you're saying open 10+ connections -- one for each table? I could have
>> ten connections and ten tables open at the same time -- that doesn't make >> sense to me - which the business is accessing?
>>
>>
>>
>> "Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
>> news:ee**************@TK2MSFTNGP04.phx.gbl...
>>> Bad idea. Open and close connections as quickly as possible to leverage >>> built-in connection pooling properly.
>>>
>>> --
>>> HTH,
>>>
>>> Kevin Spencer
>>> Microsoft MVP
>>> Professional Numbskull
>>>
>>> Hard work is a medication for which
>>> there is no placebo.
>>>
>>> "Terry Jolly" <te***@ipmas.com> wrote in message
>>> news:44**********************@news.twtelecom.net.. .
>>>> Web Solution
>>>>
>>>> Goal: Have a global database connection
>>>> Why: (There will be 30+ tables, represented by 30+ classes) I only want >>>> to reference the database connection once.
>>>>
>>>> I put the connection string in the web.config.
>>>> I created a class with a static database connection and the class opens >>>> and closes the database.
>>>>
>>>> This seems to work so the question: Is there a better way to handle the >>>> database connection?
>>>>
>>>> Thanks In Advance.
>>>>
>>>>
>>>
>>>
>>
>>
>
>



May 14 '06 #29

P: n/a
I had always planned to open a connection for each web page. That was not
the issue. The one web page may open several classes (tables) then do what's
necessary then close the tables and the connection for the page. Each and
every web page opens it's own connection, yet several tables for the one web
page (which are in classes)! If each table opened a connection per web page
and there are many tables, again less say 30, that would be 30 connections
per web page. The connection pool would soon run short. This is what I have
been asking and everyone seems to think I talking about one connection for
all web pages, which was never the case. The global connection was only
global for the one web page, not through the entire web site!

"Göran Andersson" <gu***@guffa.com> wrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
Ok, I'll try without sarcasm. ;)

The web server is built to process several web pages simultaneously using
mutliple threads. The database is built to process several queries
simultaneously using multiple sessions. If you use only one connection in
your application, that will become the bottle neck of the application.
Whatever you do, it will always boil down to the fact that only one thread
at a time can access the database, and all the other threads have to wait
their turn.

Open a connection for each page, and use that for the database operations
in the page. When you close the connection it will be returned to the
connection pool. Connections are only costly when you create them the
first time. When there is a connection in the connection pool that can be
reused, opening it is much faster.

Terry Jolly wrote:
No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening
30+ connections made more sense than one connection if in both cases the
connection would be opened the same amount of time (which no-one yas yet
answered). I really would like to hear another point of view. I have not
discounted anything anyone has said. Someone did say:
my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object


Which I like and am (have been) diagraph/mapping out to see if this would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl...
Yes, you are probably right.

He got a lot of answers, but he didn't seem to listen to anyone who
didn't already agree with him. Mark Rae even answered him using my own
words, so there wasn't much I could add to that. I thought that I'd try
a different angle, but I might have taken it a bit too far.

Anyway, if he don't want to listen, a different angle will probably not
work anyway.

Ray Booysen wrote:
Hi Göran

Sarcasm like this isn't neccessary. Someone asked a question to
something that he/she obviously didn't know the correct answer for.
It doesn't help answering the question in this sort of vein.

Regards
Ray

Göran Andersson wrote:
> Using only one single database connection for a web application? Good
> one...
>
> You could just as well neuter the web server by only allowing one
> single thread, as the other threads only will be waiting for the
> connection to get free.
>
> What database are you using? If you are only going to use a single
> connection against it you should consider downgrading to something
> low-grade like MSDE or Access. Or why not anything even simpler like
> storing the data in text files?
>
>
> Terry Jolly wrote:
>> Web Solution
>>
>> Goal: Have a global database connection
>> Why: (There will be 30+ tables, represented by 30+ classes) I only
>> want to reference the database connection once.
>>
>> I put the connection string in the web.config.
>> I created a class with a static database connection and the class
>> opens and closes the database.
>>
>> This seems to work so the question: Is there a better way to handle
>> the database connection?
>>
>> Thanks In Advance.


May 14 '06 #30

P: n/a
You can always close connections in a web page, even if there's 1,000
connections in it, as soon as you're done retrieving the data you need to request.

The number of connections in a page doesn't have a bearing
on how soon you shoud close a connection you've opened.

The answer is always the same : as soon as you're done retrieving/displaying the data.


Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espańol : http://asp.net.do/foros/
===================================
"Terry Jolly" <te***@ipmas.com> wrote in message news:yy*****************@tornado.texas.rr.com...
I had always planned to open a connection for each web page. That was not
the issue. The one web page may open several classes (tables) then do what's
necessary then close the tables and the connection for the page. Each and
every web page opens it's own connection, yet several tables for the one web
page (which are in classes)! If each table opened a connection per web page
and there are many tables, again less say 30, that would be 30 connections
per web page. The connection pool would soon run short. This is what I have
been asking and everyone seems to think I talking about one connection for
all web pages, which was never the case. The global connection was only
global for the one web page, not through the entire web site!

"Göran Andersson" <gu***@guffa.com> wrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
Ok, I'll try without sarcasm. ;)

The web server is built to process several web pages simultaneously using
mutliple threads. The database is built to process several queries
simultaneously using multiple sessions. If you use only one connection in
your application, that will become the bottle neck of the application.
Whatever you do, it will always boil down to the fact that only one thread
at a time can access the database, and all the other threads have to wait
their turn.

Open a connection for each page, and use that for the database operations
in the page. When you close the connection it will be returned to the
connection pool. Connections are only costly when you create them the
first time. When there is a connection in the connection pool that can be
reused, opening it is much faster.

Terry Jolly wrote:
No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening
30+ connections made more sense than one connection if in both cases the
connection would be opened the same amount of time (which no-one yas yet
answered). I really would like to hear another point of view. I have not
discounted anything anyone has said. Someone did say:

my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object

Which I like and am (have been) diagraph/mapping out to see if this would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl...
Yes, you are probably right.

He got a lot of answers, but he didn't seem to listen to anyone who
didn't already agree with him. Mark Rae even answered him using my own
words, so there wasn't much I could add to that. I thought that I'd try
a different angle, but I might have taken it a bit too far.

Anyway, if he don't want to listen, a different angle will probably not
work anyway.

Ray Booysen wrote:
> Hi Göran
>
> Sarcasm like this isn't neccessary. Someone asked a question to
> something that he/she obviously didn't know the correct answer for.
> It doesn't help answering the question in this sort of vein.
>
> Regards
> Ray
>
> Göran Andersson wrote:
>> Using only one single database connection for a web application? Good
>> one...
>>
>> You could just as well neuter the web server by only allowing one
>> single thread, as the other threads only will be waiting for the
>> connection to get free.
>>
>> What database are you using? If you are only going to use a single
>> connection against it you should consider downgrading to something
>> low-grade like MSDE or Access. Or why not anything even simpler like
>> storing the data in text files?
>>
>>
>> Terry Jolly wrote:
>>> Web Solution
>>>
>>> Goal: Have a global database connection
>>> Why: (There will be 30+ tables, represented by 30+ classes) I only
>>> want to reference the database connection once.
>>>
>>> I put the connection string in the web.config.
>>> I created a class with a static database connection and the class
>>> opens and closes the database.
>>>
>>> This seems to work so the question: Is there a better way to handle
>>> the database connection?
>>>
>>> Thanks In Advance.


May 14 '06 #31

P: n/a
I totally agree! Once the data is retrieved the table and connection is
closed.

One web pages has ONE class, the business class that opens the connection
(public) then the business class may open (depending on what's needed)
several tables (at the same time) (becuase a join will not work -- later
discussion), retrieves the data, closes the tables and then closes the
connection.

Are you saying you never open multiple tables on the same connection?


"Juan T. Llibre" <no***********@nowhere.com> wrote in message
news:OP**************@TK2MSFTNGP03.phx.gbl...
You can always close connections in a web page, even if there's 1,000
connections in it, as soon as you're done retrieving the data you need to
request.

The number of connections in a page doesn't have a bearing
on how soon you shoud close a connection you've opened.

The answer is always the same : as soon as you're done
retrieving/displaying the data.


Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espańol : http://asp.net.do/foros/
===================================
"Terry Jolly" <te***@ipmas.com> wrote in message
news:yy*****************@tornado.texas.rr.com...
I had always planned to open a connection for each web page. That was not
the issue. The one web page may open several classes (tables) then do
what's
necessary then close the tables and the connection for the page. Each and
every web page opens it's own connection, yet several tables for the one
web
page (which are in classes)! If each table opened a connection per web
page
and there are many tables, again less say 30, that would be 30
connections
per web page. The connection pool would soon run short. This is what I
have
been asking and everyone seems to think I talking about one connection
for
all web pages, which was never the case. The global connection was only
global for the one web page, not through the entire web site!

"Göran Andersson" <gu***@guffa.com> wrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
Ok, I'll try without sarcasm. ;)

The web server is built to process several web pages simultaneously
using
mutliple threads. The database is built to process several queries
simultaneously using multiple sessions. If you use only one connection
in
your application, that will become the bottle neck of the application.
Whatever you do, it will always boil down to the fact that only one
thread
at a time can access the database, and all the other threads have to
wait
their turn.

Open a connection for each page, and use that for the database
operations
in the page. When you close the connection it will be returned to the
connection pool. Connections are only costly when you create them the
first time. When there is a connection in the connection pool that can
be
reused, opening it is much faster.

Terry Jolly wrote:
No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening
30+ connections made more sense than one connection if in both cases
the
connection would be opened the same amount of time (which no-one yas
yet
answered). I really would like to hear another point of view. I have
not
discounted anything anyone has said. Someone did say:

> my suggestion is having one class that just handles commands to
> database.
>
> your other 30+ classes would just prepare commands and pass them for
> execution. So you will have central place for working with
> SQLConnection object

Which I like and am (have been) diagraph/mapping out to see if this
would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl...
> Yes, you are probably right.
>
> He got a lot of answers, but he didn't seem to listen to anyone who
> didn't already agree with him. Mark Rae even answered him using my own
> words, so there wasn't much I could add to that. I thought that I'd
> try
> a different angle, but I might have taken it a bit too far.
>
> Anyway, if he don't want to listen, a different angle will probably
> not
> work anyway.
>
> Ray Booysen wrote:
>> Hi Göran
>>
>> Sarcasm like this isn't neccessary. Someone asked a question to
>> something that he/she obviously didn't know the correct answer for.
>> It doesn't help answering the question in this sort of vein.
>>
>> Regards
>> Ray
>>
>> Göran Andersson wrote:
>>> Using only one single database connection for a web application?
>>> Good
>>> one...
>>>
>>> You could just as well neuter the web server by only allowing one
>>> single thread, as the other threads only will be waiting for the
>>> connection to get free.
>>>
>>> What database are you using? If you are only going to use a single
>>> connection against it you should consider downgrading to something
>>> low-grade like MSDE or Access. Or why not anything even simpler like
>>> storing the data in text files?
>>>
>>>
>>> Terry Jolly wrote:
>>>> Web Solution
>>>>
>>>> Goal: Have a global database connection
>>>> Why: (There will be 30+ tables, represented by 30+ classes) I only
>>>> want to reference the database connection once.
>>>>
>>>> I put the connection string in the web.config.
>>>> I created a class with a static database connection and the class
>>>> opens and closes the database.
>>>>
>>>> This seems to work so the question: Is there a better way to handle
>>>> the database connection?
>>>>
>>>> Thanks In Advance.



May 14 '06 #32

P: n/a
re:
Are you saying you never open multiple tables on the same connection?
The number of tables doesn't matter.

The connections to the databases, whether to one table or to many,
should always be closed as soon as you're done retrieving/displaying the data.


Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espańol : http://asp.net.do/foros/
===================================
"Terry Jolly" <te***@ipmas.com> wrote in message news:AZ*****************@tornado.texas.rr.com...I totally agree! Once the data is retrieved the table and connection is closed.

One web pages has ONE class, the business class that opens the connection (public) then the
business class may open (depending on what's needed) several tables (at the same time) (becuase a
join will not work -- later discussion), retrieves the data, closes the tables and then closes the
connection.

Are you saying you never open multiple tables on the same connection?


"Juan T. Llibre" <no***********@nowhere.com> wrote in message
news:OP**************@TK2MSFTNGP03.phx.gbl...
You can always close connections in a web page, even if there's 1,000
connections in it, as soon as you're done retrieving the data you need to request.

The number of connections in a page doesn't have a bearing
on how soon you shoud close a connection you've opened.

The answer is always the same : as soon as you're done retrieving/displaying the data.


Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espańol : http://asp.net.do/foros/
===================================
"Terry Jolly" <te***@ipmas.com> wrote in message news:yy*****************@tornado.texas.rr.com...
I had always planned to open a connection for each web page. That was not
the issue. The one web page may open several classes (tables) then do what's
necessary then close the tables and the connection for the page. Each and
every web page opens it's own connection, yet several tables for the one web
page (which are in classes)! If each table opened a connection per web page
and there are many tables, again less say 30, that would be 30 connections
per web page. The connection pool would soon run short. This is what I have
been asking and everyone seems to think I talking about one connection for
all web pages, which was never the case. The global connection was only
global for the one web page, not through the entire web site!

"Göran Andersson" <gu***@guffa.com> wrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
Ok, I'll try without sarcasm. ;)

The web server is built to process several web pages simultaneously using
mutliple threads. The database is built to process several queries
simultaneously using multiple sessions. If you use only one connection in
your application, that will become the bottle neck of the application.
Whatever you do, it will always boil down to the fact that only one thread
at a time can access the database, and all the other threads have to wait
their turn.

Open a connection for each page, and use that for the database operations
in the page. When you close the connection it will be returned to the
connection pool. Connections are only costly when you create them the
first time. When there is a connection in the connection pool that can be
reused, opening it is much faster.

Terry Jolly wrote:
> No one has given a different view yet, accept to say I'm wrong and I'm
> leaving the conncection open, which is not true. So, I asked if opening
> 30+ connections made more sense than one connection if in both cases the
> connection would be opened the same amount of time (which no-one yas yet
> answered). I really would like to hear another point of view. I have not
> discounted anything anyone has said. Someone did say:
>
>> my suggestion is having one class that just handles commands to
>> database.
>>
>> your other 30+ classes would just prepare commands and pass them for
>> execution. So you will have central place for working with
>> SQLConnection object
>
> Which I like and am (have been) diagraph/mapping out to see if this would
> work. Don't be offended about asking to clarity you position better. It
> doesn't mean I think your wrong.
>
>
>
> "Göran Andersson" <gu***@guffa.com> wrote in message
> news:uJ**************@TK2MSFTNGP03.phx.gbl...
>> Yes, you are probably right.
>>
>> He got a lot of answers, but he didn't seem to listen to anyone who
>> didn't already agree with him. Mark Rae even answered him using my own
>> words, so there wasn't much I could add to that. I thought that I'd try
>> a different angle, but I might have taken it a bit too far.
>>
>> Anyway, if he don't want to listen, a different angle will probably not
>> work anyway.
>>
>> Ray Booysen wrote:
>>> Hi Göran
>>>
>>> Sarcasm like this isn't neccessary. Someone asked a question to
>>> something that he/she obviously didn't know the correct answer for.
>>> It doesn't help answering the question in this sort of vein.
>>>
>>> Regards
>>> Ray
>>>
>>> Göran Andersson wrote:
>>>> Using only one single database connection for a web application? Good
>>>> one...
>>>>
>>>> You could just as well neuter the web server by only allowing one
>>>> single thread, as the other threads only will be waiting for the
>>>> connection to get free.
>>>>
>>>> What database are you using? If you are only going to use a single
>>>> connection against it you should consider downgrading to something
>>>> low-grade like MSDE or Access. Or why not anything even simpler like
>>>> storing the data in text files?
>>>>
>>>>
>>>> Terry Jolly wrote:
>>>>> Web Solution
>>>>>
>>>>> Goal: Have a global database connection
>>>>> Why: (There will be 30+ tables, represented by 30+ classes) I only
>>>>> want to reference the database connection once.
>>>>>
>>>>> I put the connection string in the web.config.
>>>>> I created a class with a static database connection and the class
>>>>> opens and closes the database.
>>>>>
>>>>> This seems to work so the question: Is there a better way to handle
>>>>> the database connection?
>>>>>
>>>>> Thanks In Advance.



May 15 '06 #33

P: n/a
Well, in that case you are on the track.

You wrote in your original post that you intended to use a static
database connection. Unless you also make it ThreadStatic, it would be
shared by all threads that use the class. That is why everyone thought
that you wanted to use one single connection for all requests.

I've built a data access library that used threadstatic connections, and
I advice against it. Create the connection at one level, pass it along
to any method that needs to use it, and close it at the same level as
you created it. I use an application specific wrapper class around a
general database handler class. The database calls go in classes that
represent the objects in the database. The code in the page ends up
looking something like:

List<SomeObject> someThings;

using (Db db = new Db()) {
someThings = SomeObect.GetAll();
SomeObject.DoSomeUpdate();
}
foreach (SomeObject something in someThings) {
...use something
}

Terry Jolly wrote:
I had always planned to open a connection for each web page. That was not
the issue. The one web page may open several classes (tables) then do what's
necessary then close the tables and the connection for the page. Each and
every web page opens it's own connection, yet several tables for the one web
page (which are in classes)! If each table opened a connection per web page
and there are many tables, again less say 30, that would be 30 connections
per web page. The connection pool would soon run short. This is what I have
been asking and everyone seems to think I talking about one connection for
all web pages, which was never the case. The global connection was only
global for the one web page, not through the entire web site!

"Göran Andersson" <gu***@guffa.com> wrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
Ok, I'll try without sarcasm. ;)

The web server is built to process several web pages simultaneously using
mutliple threads. The database is built to process several queries
simultaneously using multiple sessions. If you use only one connection in
your application, that will become the bottle neck of the application.
Whatever you do, it will always boil down to the fact that only one thread
at a time can access the database, and all the other threads have to wait
their turn.

Open a connection for each page, and use that for the database operations
in the page. When you close the connection it will be returned to the
connection pool. Connections are only costly when you create them the
first time. When there is a connection in the connection pool that can be
reused, opening it is much faster.

Terry Jolly wrote:
No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening
30+ connections made more sense than one connection if in both cases the
connection would be opened the same amount of time (which no-one yas yet
answered). I really would like to hear another point of view. I have not
discounted anything anyone has said. Someone did say:

my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object
Which I like and am (have been) diagraph/mapping out to see if this would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uJ**************@TK2MSFTNGP03.phx.gbl...
Yes, you are probably right.

He got a lot of answers, but he didn't seem to listen to anyone who
didn't already agree with him. Mark Rae even answered him using my own
words, so there wasn't much I could add to that. I thought that I'd try
a different angle, but I might have taken it a bit too far.

Anyway, if he don't want to listen, a different angle will probably not
work anyway.

Ray Booysen wrote:
> Hi Göran
>
> Sarcasm like this isn't neccessary. Someone asked a question to
> something that he/she obviously didn't know the correct answer for.
> It doesn't help answering the question in this sort of vein.
>
> Regards
> Ray
>
> Göran Andersson wrote:
>> Using only one single database connection for a web application? Good
>> one...
>>
>> You could just as well neuter the web server by only allowing one
>> single thread, as the other threads only will be waiting for the
>> connection to get free.
>>
>> What database are you using? If you are only going to use a single
>> connection against it you should consider downgrading to something
>> low-grade like MSDE or Access. Or why not anything even simpler like
>> storing the data in text files?
>>
>>
>> Terry Jolly wrote:
>>> Web Solution
>>>
>>> Goal: Have a global database connection
>>> Why: (There will be 30+ tables, represented by 30+ classes) I only
>>> want to reference the database connection once.
>>>
>>> I put the connection string in the web.config.
>>> I created a class with a static database connection and the class
>>> opens and closes the database.
>>>
>>> This seems to work so the question: Is there a better way to handle
>>> the database connection?
>>>
>>> Thanks In Advance.


May 15 '06 #34

P: n/a
Göran Andersson wrote:

List<SomeObject> someThings;

using (Db db = new Db()) {
someThings = SomeObect.GetAll();
SomeObject.DoSomeUpdate();
}
foreach (SomeObject something in someThings) {
...use something
}


As I send the database object along to any method that uses it, it's of
course like this:

List<SomeObject> someThings;

using (Db db = new Db()) {
someThings = SomeObect.GetAll(db);
SomeObject.DoSomeUpdate(db);
}
foreach (SomeObject something in someThings) {
...use something
}
May 15 '06 #35

P: n/a
Terry Jolly wrote:
I totally agree! Once the data is retrieved the table and connection is
closed.

One web pages has ONE class, the business class that opens the connection
(public) then the business class may open (depending on what's needed)
several tables (at the same time) (becuase a join will not work -- later
discussion), retrieves the data, closes the tables and then closes the
connection.

Are you saying you never open multiple tables on the same connection?

Hi Terry,

I think you confused a lot of people with your use of the word
"global". Global tends to indicate that there is one and only one in
the entire application. Instead, I think we're now gathering that
during a single page request, you're opening a connection, performing
various actions against one or more tables, and then closing the
connection. If this is the case, then it's probably spot on.

The various connection options I can think of are:

1) One shared, global connection, which anyone wanting to use it must
synchronize access to,
2) One connection per session (which if multplie requests per session
are possible, must be synchronized)
3) One connection per request
4) One connection per database command

I think you're looking at option 3, which I believe to be quite
reasonable. My own preference is for 4, but that's because it's easier
to do in my Database Objects Generator (which generates classes to
interact with the db). 3 would probably be better if you needed to
accomplish several interactions within a single transaction. 1 scales
horribly, and I haven't attempted 2, but I'd imagine it also scales
badly.

Anyone got anything to add?

Damien

May 15 '06 #36

This discussion thread is closed

Replies have been disabled for this discussion.