If you are using a SQLServer Compact Edition database, you will need to deploy the software for it. You can deploy it as a prerequisite, but this requires the customer to install one more thing without clicking Cancel. He must also have administrative privileges.
Additionally, version control is more difficult with ClickOnce deployment, because prerequisites are not handled by the incremental updates, and you can never get all of your customers to reinstall a prerequisite. At least one of them will be out of town, and when they come back, they’ll have too much e-mail to deal with and won’t take the time to follow your instructions to update their application.
A better idea is to deploy the assemblies required locally. This video explains all.
In the interest of copy and paste and making your life easier, here are the 7 dll’s you need to locate and add to your project. You should be able to find them in C:\Program Files\Microsoft SQL Server Compact Edition\v3.5 (or v3.0). If you are using 3.0, substitute “30” for “35” in the names of these dll’s.
Set the Build Action to “Content”, and “Copy to Output Directory” to “copy always” on these entries in Solution Explorer. On the reference to the System.Data.SqlServerCe.dll in your project, set “Copy Local” to “true”.
In other news about SQLCE databases (or any database, for that matter), if you are deploying one with ClickOnce deployment as data, did you know if you open the database it changes the timestamp, and deploys a new version next time you deploy an update? On the client side, it then copies the previous version to the ApplicationData\pre folder, and puts the new one to the ApplicationData folder. If you’re not handling this in your code, it looks like the user has lost his data. This kind of thing tends to annoy customers, which is always a bad thing that can lead to a loss of cash flow, if you know what I mean.
For a easier way to handle updates and let your database change only when you intend it to, you might want to check out my blog entry titled Where do I put my data to keep it safe from ClickOnce updates? I think the title is self-explanatory.