To close or not to close connection in database

Question

I work with Windows-Mobile and Windows-CE using SqlCE and I dont know what better to do.

To open connection when the program open, run any query's... update...delete database and close the connection after the program close?

Or open connection run any query's..update...delete database and close the connection immediately?

Answer 1

Nice. The answers are all over the place. Here's what I know from experience and interacting with the SQL Compact team:

1. Closing the connection flushes the changes you've made, otherwise the engine waits for the flush period before doing it. It's a good idea to close the connection when you're done using it to ensure that your changes actually go to the store. A power loss after a write and before a flush will lose data.
2. There is no official connection pool, but opening the first connection is expensive (i.e. slow), all others are quick. The recommendation I got from the team is to actually create a connection when the app starts up and just leave it open. You don't actually need to use it, but keeping it open keeps a lot of connection info cached so that subsequent connections to the same store are quick.

So the answer, actually, is both.

Edit

For those interested, a good example of how this works can be seen in the OpenNETCF ORM library. The library, by default, creates a "maintenance" connection that remains open and is used for doing things like schema queries. All other data operations use their own connection. You also have to option to configure the library to reuse a single connection for the life of the Store, or to use a new connection every time it touches the store. Perfomance and behavior has always been best in all of my projects using the default (which is why I made it the default).