Storing Database Connection Object

  • Thread starter Thread starter cindy
  • Start date Start date
C

cindy

What is the best way to handle this situation:

You have a very simple phonelist ASP.NET application where users can view
the phone list (dynamic) and they can also update or delete items in the
phone list. So, you would have 3 pages -- one that views the info, one that
updates the info and one that deletes the info.

What would be the best and most efficient place to store the DB connection
object? If each page manually opens the connection, performs the commands
and then closes the connection, it seems like a lot of wasted effort.
Should I make the connection in the main page and then assign it to an
application or session state variable and then use this variable in the 3
pages? Or, is it better to just open and close the connection when needed?

Thanks.
 
If the connection has the exact same connectionstring, it'll take advntage
of the pool and it's not a big deal.

If you 'store' it somewhere are you planning on leaving it open? If so, I'd
highly recommend against it. In many instances it won't even work. You
can store the connection somewhere, in session state or wherever (Don't do
it in a module b/c it'll be shared by all users) else, but that doesn't get
you a whole lot. And you'll still have to open and close connections each
time.

You can cache things to minimize trips to the db, but I wouldn't worry about
opening and closing connections b/c the cost of leaving a connection open
for an entire visit is much more potentially problematic.


Let me know if you have any questions.

Cheers,

Bill
 
The connection (the real connection) is already cached automatically--in the
Connection pool. It saves very little to duplicate this functionality in
your own code. Just let ADO.NET open and close the connection (using Fill
and Update) and let the provider manage the pool for you.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I make the database object (connection, dbCommands, dbAdapters, and
datasets) in my Global.asax design page.

Then for each page that may use those database objects I make a 'Worker'
class that inherits Global, like so...

Public Class WebPage

Public Class Worker
Inherits Global
End Class

Private Dim wrWebPage as New WebPage.Worker

' Use wrWebPage as is it were Global Class, contains all database
objects that are in Global.asax

End Class

Severin
 
Back
Top