If you're using Datasets then open late and close early, easy enough.
When it comes to other types of connections, it depends...
If you have 10 subs all calling each other through a single user action, in other words the user clicks one button and all 10 subs are triggered, then by all means leave the connection open until all 10 are complete.
I would have a hard time leaving a connection open during the life of an application. But then again it depends of the app.