Thanks guys, I'm relieved to realize I'm not the only one viewing
things like this. I find it hard to back up my claims tho, because all
articles that explains how to handle a database are example usings the
open-execute-close technique.

Maybe some MS MVP guy would have a link to a technical paper that would
help me prove my point ? (or prove me wrong...either way)

I'm not so much concerned about proving I'm right (or wrong) but I'd
like to find something that really explains how's its done or what
technique is best depending on a given situation.

My books and articles support David's approach. Making (and breaking)
connections is NOT free. If you're in a routine that fully expects to make
use of the connection, keep it open. However, if the page ends and the
connection is left open by accident you'll leak connections. This is not an
issue with Windows Forms applications where it's far easier to simply open a
connection and leave it open. In ASP applications, it is important to keep
the number of active connections to a minimum--using JIT open/process/close
is a safe approach but can actually hurt performance if your code is not
cognizant of the bigger picture.

hth

Show quoteHide quote

Show quote Hide quote BTW has everyone seen Alazel Acheson's DbConnectionScope class?

http://blogs.msdn.com/dataaccess/

Very cool.

David

Thanks guys. after reading everyone's comments and also reading this
group :
http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/791a79365a034df4/?hl=en#

I realized that while my technique was still good that I'd better off
with the JIT approach. basically here's a very simple version of how my
code was :

Class Profiles {

database = new clsDataAccessLayer(constantfile.connectionstring)

database.openconn()

loop sections
  loop datasets
    loop rows
      select case section
        case section 1
            myResults = database.getDataForSection1

        case section 2
            myResults = database.getDataForSection2

        case section 3
            myResults = database.getDataForSection3
     end select
end loop
end loop
end loop

database.closeconn()
database = nothing
}

for my application, I've put all the database operations at a single
point. So i thought it would be more efficient to leave the connection
open. But i came to realize that in my code, 3 indented loops with a
few lines in between can become slow if you put a few hundreds (or
more) concurrent users. meaning that im hogging connections that could
be useful to another instance.

So ive changed my code so that every call to the database class opens a
connection, execute and close the connection (dispose)

my only remaining question is this :
I know that the pool will be destroyed when the active process ends, in
my case, when the call to my webservice ends.

if i have 10 different people calling my webservice, on the same
server, will there be 10 different connection pools or will the pool be
the same for those 10 users ?

Parent-Child Relationship Headaches (add new records) (VB 2003)
VS2003 transfer data between two data sources
instance of sqlDataReader - is not available to an exteranl method
Should Dispose be called on DataTable and DataSet objects?
Data Access Problem