Making a database connection global

T

Terry Jolly

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

Mark Rae

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
 
M

Michael Bosch

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

Galin Iliev [MCSD.NET]

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
 
T

Terry Jolly

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

Kevin Spencer

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

Terry Jolly

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

Terry Jolly

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

Terry Jolly

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

Mark Rae

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

Kevin Spencer

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
 
?

=?ISO-8859-1?Q?G=F6ran_Andersson?=

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

Ray Booysen

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

Galin Iliev [MCSD.NET]

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
 
?

=?ISO-8859-1?Q?G=F6ran_Andersson?=

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top