Best practice to work with multiple DB connections

[Venkat]

Hi,

We have a windows application developed in c# and SQL Server 2005. Our
application need to execute more than one command (ExecuteReader and
ExecuteScalar) at a single time. Till now we have we use with only one
connection created during start up of application and will be
displose/closed when user logs out of application. With the single database
connection we have problems when trying to execute more than command at a
particular time.
Can you tell me the best practice to solve this problem? I am thinking the
following ways

1) Is it better to open and close connection whenever we need to execute a
command? But the problem with this is the Sql server runs on a network
server and it is taking much time to establish/create a database connection.

2) By using Connection pool. I am thinking to have a connection pool with
minimum connections of 1 and maximum connections as 5. But I read some
articles that connection pooling is generally used in web applications. I am
not sure whether it is helpful for windows based application or not.

Please suggest me the best practice to solve my problem.

Thanks in advance.
Srikanth

[Marc Gravell]

0) Since you are on SQL2005, try enabling MARS; this will allow you to
call ExecuteScalar -on the same connection - while ExecuteReader is in
progress. Sounds too good to be true, but worth a try. Just tweak the
connection string to include ";MultipleActiv eResultSets=Tru e"

1/2) is not as big an issue as you think, since .NET provides pooling
by default. When you Close() a managed connection it (by default)
simply goes back into the pool. There is a very good chance (in such
an app) that you will get the same actual connection (albeit reset)
next time.

Marc

[Venkat]

Thanks for your valuable input. Can you suggest the best one out of your two
suggestions?

"Marc Gravell" <ma**********@g mail.comwrote in message
news:uV******** ******@TK2MSFTN GP05.phx.gbl...

0) Since you are on SQL2005, try enabling MARS; this will allow you to
call ExecuteScalar -on the same connection - while ExecuteReader is in
progress. Sounds too good to be true, but worth a try. Just tweak the
connection string to include ";MultipleActiv eResultSets=Tru e"

1/2) is not as big an issue as you think, since .NET provides pooling by
default. When you Close() a managed connection it (by default) simply goes
back into the pool. There is a very good chance (in such an app) that you
will get the same actual connection (albeit reset) next time.

Marc

[Marc Gravell]

both ;-p

Use MARS when MARS is appropriate, otherwise pooling. Personally I
tend to use the "open, use, close" approach to connection management -
i.e. keep it local and short-lived (and let pooling work its magic),
until you /know/ (ideally via profiling) that a block of code really
warrants explicit connection handling. You can use a long-lived
connections, but it doesn't always fit well with every situation (for
example enterprise library will handle the connections internally).
Additionally, this approach tends to make it easy to port the code
into a more stateless model (such as aspx or web-services).

Marc