Connecting for entire session, or just when performing an operation?

Here's a simple question for you:

Is it better to maintain a single open connection to the database for the
entire duration of the users session, or to connect, perform an operation
and then disconnect each time the user wants to do some work, within that
session?

If so, why?

Thanks,

RobinRobin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> Is it better to maintain a single open connection to the database for the
> entire duration of the users session, or to connect, perform an operation
> and then disconnect each time the user wants to do some work, within that
> session?

The normal procedure these days is to do both, That is, typically
your code goes:

cnn.Connect;
cmd = cnn.CreateCommand("SELECT ... ")
cmd.Execute
GetData
cmd.close
cnd.Disconnect

But behind the scenes the client library maintains a connection pool,
so when you say disconnect, the library lingers on the connection, so
if you recnnect within some period, the connection is reused. If you
do connect withine some time, typically 60 seconds, the connection is
closed for real.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It's my understanding that if you use a DataAdapter, that the
DataAdapter makes it appear to you that you're always connected, but
behind the scenes it releases and reqacquires the connection when
appropriate. Does this sound right to anyone?|||(shumaker@.cs.fsu.edu) writes:
> It's my understanding that if you use a DataAdapter, that the
> DataAdapter makes it appear to you that you're always connected, but
> behind the scenes it releases and reqacquires the connection when
> appropriate. Does this sound right to anyone?

Well, if you have connected explicitly, the DataAdapter will close the
connection. But if the connection is closed, the DataAdapter will open,
perform the operation and close.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp