SQL Server CE + Addin Read and Write Collision Avoidance?

Question

We have an Office Addin that uses Sql CE with the usual DBConnection to a *.sdf in the filesystem ('C:/...etc...'). When we start two copies of the application having the Sql CE-augmented Office Addin (testing what a user might do by mistake) the Sql CE database becomes corrupted and the Office Addin can no longer access its data.

It seems like we are missing some basic way of avoiding a seemingly obvious problem. By now everyone knows that Sql CE does not lock its rows allowing the sort of corruption that multiple writes to the same *.sdf can cause.

Perhaps the answer is "don't do that;" and that Sql CE is not designed to handle this case. But surely most applications of Sql CE have a similar risk of mistakenly starting more than one process and attempting to write to the same *.sdf?

We like Sql CE's small size, free-ness, and easy integration to our addins. But we have looked at Sql Server Express and 2008 comparisons: http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact%5FExpress%5FComparison.doc

Answer 1

If i understand that correctly your use-case does not really require two running applications, you just need to catch the error if the user does so by mistake.

In that case it should be sufficient to check if your addin is already running (whatever your language/framework supports for that...). If there is another instance, just don't instantiate the addin.