Making a database connection global

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

 

[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

 

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

 

[sloan]

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/codegallery.aspx?id=295a464a-6072-4e25-94e2-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

 

[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

 

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

 

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

 

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

 

[Mark Rae]

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.

 

[Mark Rae]

No the connection is only open when needed --

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

 

[Terry Jolly]

you synchronize - which you would have to do under any circumstances.

 

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

 

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

 

[Terry Jolly]

I like your idea and will give it thought.

Thanks!

 

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

 

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

 

[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

 

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