Database Connections

[Tom John]

Hi

I have a data access layer that provides static functions between my
business logoc and an Access database. The connection string to the
database never changes.

Currently I carefully open and close a connection to the database at
the begining of each method in the dataacces layer. But am not sure
this is the best method, I went this way because i read somewhere in
web apps connections should be kept open for as short a period as
possible.

If i have multiple sessions on the go on the web server i worry that
there are lot of connections opening and closing that could be shared.

Can anyone advise

Thanks

Tom

 

[Phillip Ian]

You might want to look at the connection pooling information in help.
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.htm
(not sure these links work, but it seems like it should).

The gist is, if you keep the connection string constant, ADO.NET will
manage connection pooling for you. It will pool and re-use connections
across sessions. If you find that the pooling system doesn't work to
your satisfaction, you can tune it by adding some commands to the
command string.

-Phil

 

[Sahil Malik [MVP]]

Tom,

SqlConnection is not the same as a database connection when connection
pooling is enabled, so as long as your connection (sqlconnection) instance
is kept open for the least amount of possible, your db server won't die.

Here is something i wrote a while back on how it all works under the seams.

http://codebetter.com/blogs/sahil.malik/archive/2004/11/12/31798.aspx