Windows mobile Compact Framework SqlCeConnection

views:

213

answers:

+2 Q:

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 with sql ce?

+1 A:

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.

ctacke 2010-03-11 21:24:35

You can change the lazy flushing behaviour in two ways. When you call `Commit` on a `SqlCeTransaction`, you can `CommitMode.Immediate` to flush the transaction immediately (http://msdn.microsoft.com/en-US/library/esdw1h9d.aspx). There is also a `flush interval` option on the connection string (http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx).

Phil Ross 2010-03-11 21:34:16

What do you mean by "primed"??

pdiddy 2010-03-11 22:22:31

I was looking for any public doc on this, but all I can find is a private email from a dev on the SQL CE team. Basically the first connection to a database initializes some objects internally and every other connection uses those. Creating and holding a connection builds up those objects for use by all subsequent connections (thereby improving performance).

ctacke 2010-03-11 22:25:01

+1 A:

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.

Phil Ross 2010-03-11 21:40:42

HI,

I am using SqlServerCE3.0 for my .net application running on windows mobile 5.0.I have placed the sdf file in program files folder of the device.After certain transactions I can see some times the device gets hanged & on restarting I found the data is roll backed to certain extent.

Please Suggest!!!!

Agesh 2010-05-09 13:45:58

ansaurus

tags:

views:

answers:

Windows mobile Compact Framework SqlCeConnection

related questions