Basic question: is it better to leave a connection open or only keep it open as minimal as possible?

Context: I am developing a Windows Forms database app in Csharp/mysql (almost done) for a small user base of about < 25 users (but i would be interested if you answer would be different for more users).


So far I have used a disconnected architecture and have been using timestamps to handle concurrency issues BUT -

I found myself questioning myself and my design because I was watching a webinar on MS SqlServer Reporting Services today and the very well established speaker was pushing the idea of keeping the connection open instead of reopening and closing over and over.

So what are your thoughts. Thanks

C# uses connection pooling. For me, it seemed to be a good technique

closing and opening connections is expensive it time and resources, so it makes sense to do it as infrequently as possible.

I'd ag1ree with aflorin if your front end language supports connection pooling then thats a smart way to go.
I don't think it makes sense to open more than one connection in any one instance of the front end.

It should be possible to extract all the data you need fromt he same connection. it makes sense to release the connection as soon as you have finished with that connection (ie consumed all the data) or run what ever SQL processes you want.

connection pooling isn't however a free lunch, you may have to go through specific steps to invoke connection pooling, and IIRC it only works if the userid and database are identical.. but that may depend on the front end language you are using.

ok - I forgot about the connection pool in .NET.
From what I remember reading a while back is that .NET uses the connection pool by default. I'll have to read up on it again to make sure I'm allowing the application to take advantage of it. My concern is that I dispose my connection objects as soon as I am done retrieving what I need from the DB, so I'm wandering if disposing has any affect on the connection pool