I have a winforms application that is doing the following:
on each query:
as we are trying to avoid having to keep the db connection open. This suffers from performance as we have to connect / disconnect everytime.
What is the idealized model here to have the fastest performance but without having open connection for too long. Is there a best practice here?
The most efficient means is connection pooling, and ADO.NET does this automatically for you as long as you make sure to close your connections. Just wrap your SqlConnection objects into using statements, and you won't have to worry about it.
Maybe try to think about caching your data? My own rough tests have shown querying in memory data to be up to 20x quicker than querying the same data from the database. Though this approach might not suit your schema or your data needs, it is always worth looking at caching frequently accessed data somewhere if you can.
There is very little overhead in connecting/disconnecting with connection pooling. Your bottleneck is most definitely the DB queries themselves, so leaving a global connection open isn't going to help.
If your queries are too slow, try to optimize them. If that doesn't work, you'll be looking at caching, asynchronous fetches, or denormalization. There's really no general use case I can think of for keeping a DB connecton around.