Global Database connection in all classes

[Bryan]

Hello,
I'm just starting to develop in asp.net and i have a question about
using a database connection globally in my app. I have set up the
procedures for getting all my connection string info which each page
will use, but my question relates to how to use the database
connection i create in all my classes.

I have a database class, in a separate namespace and file, i created
that handles all the connection opening, executing statements etc. I
also use 2 other classes in my asp.net app. which are in the same
namespace and same file.
1 holds information specific to the current user logged in, and the
other class is used as a global functions class. I want to be able to
open the db connection once, and no matter if i'm running a method in
the UserSession class, or the Global Functions class to be able to use
that same Database class i created, without creating a new instance of
the database class in each class i want to run this in.

example. my UserSession class might have the web login authentication
method in it that gets run when the user clicks the login button on
the login page, right after that method runs, which again is in the
user session class, i might run a method from the global functions
class that needs to use the database. i wanted to use the same
Database class i instantiated instead of creating a separate instance
of the database class for each class i needed it in. Can this be done?
or do i need to change my thinking around a little and use a different
model.

thanks for any help you can offer. Any links to any "best practice"
type info/articles would be appreciated as well.
Bryan

 

[Hermit Dave]

I would suggest that you have a read of
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

also have a read up of
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Karl Seguin]

Bryan,
From what I understand you are trying to do, best practice goes against it
You seem to be trying to set it up so that a single connection is opened
throughout the life of a request. With ADO.Net and connection pooling, the
prefered method is to keep connections closed for the shortest time possible
/ the smallest unit. It's far better to create new database connections and
open them on a as-needed basis.

For example, it's much better to do this:

function login_click
Call UserIsLoggedIn
if true then
Call GetUserInfo
end if
end funciton

function UserIsLoggedIn
open connection
validate user
close connection
end function


function GetUserInfo
open connection
get user info
close cnnection
end function


than to do this:

function login_click
open conection
Call UserIsLoggedIn
Call GetUserInfo
close connection
end funciton

function UserIsLoggedIn
validate user
end function


function GetUserInfo
get user info
end function

Granted that's a pretty trivial example...

If my word isn't good enough for you...From Scott Gu
(http://scottgu.com/PerformanceEurope.zip)

Code Recommendation:
"Open connections in your code late, and then close them early"
Don't hold on to connections for long periods of time - do not try to build
your own "smart" connection pool logic
Close the connection as soon as you are finished with it (this returns it to
the pool)