Windows mobile Compact Framework SqlCeConnection

I'm hearing that is better to have one connection open upon app start up and closing it when the app shuts down.

What kind of issue can occur having multiple connections ?

Any articles out there that it is best practices to have one connection?

What are your experience wit开发者_StackOverflowh sql ce?

In our SQL CE 3.5 / Compact Framework 3.5 application, we open a connection at startup and keep it open until the application is closed. The database is required on almost every user-interaction in the application and keeping the connection open is faster than opening and closing it on demand.

All data updates are performed in transactions. We Commit the transactions using the CommitMode.Immediate option. This ensures that data changes are immediately flushed to the file, minimising the potential for data loss.

It really depends. For performance, SQL CE works best if there is always a live connection to the database, as the engine doesn't have to build up everything every time you connect.

Having a single connection, however, leads to lazy flushiong of data to the file, and a higher likelihood of data loss or corruption in the event of a catastrophic failure.

I tend to open a "dummy" connection to the database at app startup and have that connection always open but rarely or never actually used. This keeps the engine "primed" if you will. THen for actual data access I use a separate connection and manage state based on what activity I'm doing, typically leaving it open across multiple queries (a pseudo transaction if you will), but not leaving it open indefinitely.