Problem with an MSI distribution

Question

So I am continuing testing and releasing changes to my app and I have come across a pain point that I am unsure how to deal with.

First off, my app uses a SQL Server CE database to store information and I need to be able to make changes to this db so I've created an internally updating process that runs whenever the application runs to make sure the db is up to date.

The crux of this internal update process is another SDF file named DBUpdates.sdf that contains all of the db schema changes that need to be applied.

The problem I am having is that the MSI distribution I created will not overwrite this file. It appears that when SQL Server CE opens this file, it changes the Modified date/time of the file. This is a flag to the MSI process that the file has changed, and that it shouldn't overwrite the file. Well now I am seeing that my db changes aren't being applied, because the MSI process thinks the user has changed this file.

At this point I am kind of stumped. I was planning on using an MSI distribution but maybe I can't. What do you think?

Answer 1

What about storing your .sdf as an embedded resource in your executable, and then extracting it to a temporary location on disk (as necessary) and perform the updates.

Answer 2

Unversioned files with MSI can be a bit difficult to handle if you need to force the installation of the file. You can see this previous question, for some ideas, http://stackoverflow.com/questions/102374/how-to-add-a-version-number-to-an-access-file-in-a-msi.

The question contains a link to this blog post, http://blogs.msdn.com/astebner/archive/2005/08/30/458295.aspx, which suggests the way I prefer to deal with this problem. Add the .sdf file to be part of your executable's component. The downside to this is if someone delete the .sdf file, but not your executable I don't think a repair of the application will catch this. If your using Visual Studio to create your MSI files then this may prove a difficult solution to implement. I strongly suggest your check out WIX in that case. It is a better MSI build system.