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 ";MultipleActiveResultSets=True"

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]

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

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

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).

I agree, I tend to always uose open/use/close approach.
But I found a case lately where it was just faster to keep the connection
open during a "long" import.
I have to read a two list from access (clients & items) then update them in
the SQL DB. Each entity is represented by a business class in the system
and of course each one implements a Save method that get a connection and
close it afterwards.
Well this process was slow, but as soon as I used an overload that received
as a parameter an opened connection and did ALL the updates using it the
performance improved.

So the conclusion is , as Marc said, it depends of the situation.