C# Windows Application - Many threads using the same connection?

Question

I've got a c# WINDOWS Application that is multi-threaded. It is my understanding that in a web environment, connections are pooled automatically. It is also my understanding that in a Windows app, this is not the case. Therefore, for a Windows app, the same connection should be used and not closed after each call, but instead closed when the app shuts down.

I'm curious though - is my correct? If it is, can two threads use the same connection to get a dataset from the DB at the same time or is that functionality queued up?

Thanks

Answer 1

The Connection Pooling is one feature of ADO.NET. Therefore the connections are already pooled. Not only in the web environment.

http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html

Answer 2

It is my understanding that in a web environment, connections are pooled automatically. It is also my understanding that in a Windows app, this is not the case.

No, this is wrong, as m3rLinEz pointed out. Connections are always pooled.

Therefore, for a Windows app, the same connection should be used and not closed after each call, but instead closed when the app shuts down.

You could keep a connection open for the duration of the application in a monolithic WinForms app. But it's better to use the standard pattern of opening/closing connections whenever you need them. Connection pooling means you won't notice a performance difference. And your data access code will be compatible with server applications such as ASP.NET.

If it is, can two threads use the same connection to get a dataset from the DB at the same time or is that functionality queued up?

No. The ADO.NET classes (connection, command etc) are not thread-safe and should not be shared between threads without synchronisation. But as noted above, you should prefer the standard pattern for data access.

Answer 3

ok - so this assumption of mine was brought on by observation: When I tried a win app setup in the typical pool fashion, I always experience a 3-5 second delay while a real connection is established to the remote server. Even when I did an open, then a close, the next query would always have this delay.

When the server connects, it obviously doesn't establish a connection for each connection in the pool. Also, is the pooling mechanism smart enough to grab a connection that it knows is already open or is it possible for it to simply grab any random connection?

What is the default max connections in the pool?