Say you are developing a desktop application that extensively uses a database, should you have one handle for the database that the entire app references? Or should you open a connection when you need data, get the data, and then close the connection when data isn’t necessary.
4
You have already listed the two major schools of thought about database connections. Each has positive and negative aspects.
Open a connection and leave it open for the duration of the application
Positive: This works better in a fully connected environment (desktop or server application primarily). You limit the number of connections to your database to a minimum. You don’t have the overhead of making the connection and shutting it down every time you want to execute a query.
Negative: This doesn’t work well in a disconnected environment or one where the connection is likely to be severed midstream frequently.
Open a connection long enough to do what you need
Positive: This works best in an environment where you are needing data infrequently. You free up a connection for another process in a heavily utilized server. If you get disconnected midstream, just restart the query process with a different connection.
Negative: There is an overhead for making a connection to the database every time. It gets worse when you have SSL and/or VPN in addition to the connection. Database connection pooling can help with this. You have to be aware that INSERTing or UPDATEing data can put you in a race condition with another user/process. It is easy to mitigate, but will require additional design and coding steps.
For a desktop application, I would normally recommend using an open connection. You will still have to have error handling in place for when the connection fails (someone restarts the database on you).
All of the negatives I have listed can be mitigated or limited somewhat by designing your application with error situations in mind (e.g., before you execute a query, you insure the connection is still open).