0

How do I get changes to be committed to the .sdf?

If the app crashes then all changes to the database are left in a temp file and do not get committed to the database .sdf.

Do I need to close and reopen the connection, or is there a setting or better way?

My app is designed to work with either SQL Server CE or "normal" SQL Server, so it uses ProviderFactory to get connections. When using SQL Server Express, the connection is opened and closed with every transaction, and all changes are committed immediately.

But with SQL Server CE, on a few machines, there is a 30-second delay while SQL Server CE is opened (the cause seems to be access-permissions on a .NET encryption file. SEE: SQL Server CE extremely slow on some computers)

So, with SQL Server CE, I leave the connection open for the whole duration app, but if it crashes or I stop it with the debugger, then the DB changes don't get saved.

Database file is in ApplicationData area.

I'm using SQL Server CE 4.0, private deployment.

Thanks for your help!

Community
  • 1
  • 1
XKCD137
  • 357
  • 4
  • 14
  • “I leave the connection open for the whole duration app”. Don't do that! That is very very dangerous! If you're worried about slow updates, use the method described in the solution to the question you posted. Do not leave connections open any longer than you need them. – Dour High Arch Feb 21 '14 at 19:54
  • ErikEJ advised me that when using SQLServer(Express), then the connection should be opened and closed every time, but when using Compact Edition a connection should be left open for the life of the application. – XKCD137 Feb 22 '14 at 00:22
  • On re-reading ErikEJ's advice, I noticed that he specified keeping an "unused" connection open, and use another connection in each thread to do the work. I tried adding another, unused, connection and then opening/closing my working connection each time. Unfortunately this did not help. If the app closes properly, data gets committed to the .sdf and the sqlxxx.tmp file goes away. If the app crashes, then changes don't get saved in the .sdf file and the sqlxxx.tmp file never gets deleted. Any other ideas? – XKCD137 Feb 22 '14 at 00:27

1 Answers1

1

First of all, you should prevent your app from crashing! You can implement global unhandled excpetion handler, if nothing else, and there force data to flush (or at least prevent the app from closing too early).

By default, the SQL Compact engine can wait up to 10 seconds before flushing to disk. This is controlled by the "Flush Interval" connection string setting, whcih can be as low as 1 second. So doing that may be a good start.

You can also make SQL Compact flush data immediately, by using the SqlCETransaction Commit mode "Immediate" - see my blog pos here for a full sample: http://erikej.blogspot.dk/2013/05/sql-server-compact-code-snippet-of-week_21.html

Both of these option will affect perceived performance.

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Yes, the solution was to close the database connection in the Unhandled Exception Handler. – XKCD137 Feb 22 '14 at 18:27
  • It's also important to not simulate a crash by simply stopping the program with the debugger. So I added a "cause unhandled exception" button. And then I realized that my predecessor put had a #if !DEBUG block around the exception-handler assignment inside Main, which was why changing the handler didn't seem to have any affect. THANK YOU!!!! – XKCD137 Feb 22 '14 at 18:36