scope of ado.net transactions and connections

microsoft.public.dotnet.distributed_apps

15 Nov 2004 10:03 PM

Johan Jooris

Hi,

According to the ms patterns and practices, one should keep a database
connection as short as possible open.
I assume that, when you have a server request which does some reads during
its business logic, and than performs an update of the database, several
connection objects can be instantiated, opened and cloased as soon a db
access has been made. Since connection pooling is default on, this is no
problem since one uses the same connection from the pool.

When you want to do this in a sqltransaction, whih spans the whole request
handling, one has to instantiate one and only one connection, and than start
the sqltransaction on this connection.
This implies that the connection has to stay open during the whole
procedure, until the database is committed.

This is a bit contradictory. Can anyone give some input on this ?

thanks!