Problems faced in keeping SQL Compact Edition Data

Question

I am using SQL CE 3.5 for one of my project, the front end is WPF application which process the given files and dumps the data to SQL CE database.

Presently application and DB is on same machine.

Client wants that he should be able to run the application from any machine on the network but database should remain on shared location of Server.

User will select the path to SDF file in the Application and then when any file is processed application will dump the data to database.

My question is, if keep SDF file on network shared location and access it from any machine then does it will work fine or could give problem?

Answer 1

actually, it is not possible - SQL CE does not support network-hosted operations: everything related to the sdf-file (temp data) is recorded to the local machine, not network source, thus server is unable to process requests correctly.

You can use SQL Express Server that acts like data storage, for the local client the only thing you need is Microsoft Synchronization library 2.1 (it also works with SQL CE 4.0 SP1)

Summarising, create the SQL CE database, fill it with tables, index them, then port it to SQL Server express, add sync module to your app (in a separate thread ofc) and that's it.

Another solution is to use MS Access DB, which allows such mess, but it is incredibly slow not to mention no way to allow simultaneous writing to the db.