My app includes a SQL Server CE database. If users have added and edited their own data, I obviously don't want their database to be overwritten when they update their software version. How do I prevent this?

The problem is that it's like 10 pages in Brian Noyes's book, and distilling it down into a posting is a bit of a challenge. Hopefully this will help. Otherwise, go spend $30 and buy the book from Amazon.com

http://www.amazon.com/Smart-Client-Deployment-ClickOnce-Applications/dp/0321197690/ref=sr_1_1?ie=UTF8&s=books&qid=1206469474&sr=8-1

Basically, you deploy the database with the application as a data file. It goes into the folder defined by ApplicationDeployment.DataDirectory, or Application.UserAppDataPath. (Assuming you have it in the top level of your project, otw it puts it in a relative folder under that base path).

The database is only placed there when actually deployed, so when you test out of VS, you have to set the "copy to output directory" to "copy always" or "copy if newer", so the files will be copied to the same (relative) folder location under the build output folder for your project.

Then you have to add code that checks to see if you're running the deployed version (ApplicationDeployment.IsNetworkDeployed = true), and if not, adjust the path programmatically for your debugging (it will be under the Application.ExecutablePath + relative-folder-if-you-have-one).

string dataPath;
if (ApplicationDeployment.IsNetworkDeployed)
dataPath = ApplicationDeployment.CurrentDeployment.DataDirectory;
else
dataPath = System.Windows.Forms.Application.StartupPath;
dataPath = System.IO.Path.Combine(dataPath, "yourdatabasename.sdf");

When you deploy your app, it deploys the database in the DataDirectory. When you publish an update to the application, it copies the database forward to the folder for the new version of the application, assuming nothing about the server-side database has not changed in any way.

But if you change the database, that's where it gets complicated. It will copy the new database from the server to the new version's DataDirectory folder.

It will copy the old database to a .\pre subfolder under the DataDirectory folder for the new version.

Then you have to write code to migrate the old data to the new database. If you do nothing, it will ignore the previous version's database and use the new database.

You can get the code from Brian Noyes' ClickOnce book here: http://www.softinsight.com/clickoncebook/ In there, there should be two samples called SQLCompactDefaultMigration. v1.0.0.0 is the first version; this is the first database deployed. v2.0.0.0 is the second version, that has changes. If you run 1.0 and make changes to the data, then run 2.0, you will see that the changes made to 1.0 are missing.

The general process to migrate the data is to run a SQL script or a set of SQL queries to retrieve the data from the old database and insert the data into the appropriate tables in the new database. You might write this code as part of a separate migration utility that is invoked when the new version gets deployed. If you look at Brian's code, there is a sample of this called SQLCompactScriptedMigration. It includes v1.0.0.0 and 2.0.0.0 of the same application I mentioned above, but 2.0.0.0 contains the code in the main form's constructor to migrate the data.

The difference between 1.0 and 2.0 in his case is that a new table was added to the database with a fk relation to the old table.

And one thing about connection strings: If you include the |DataDirectory| placeholder in your connection string, the path will be filled in automatically by the runtime. For example, for Northwind, it would be
Data Source = "|DataDirectory|\Northwind.sdf".

Be sure your database is added to the project as a file with a Build Action of "content", and marked as a Data File in the Application Files dialog.

Another potential problem to be aware of is that the migration of a data file is triggered by *any* change to the data file in a newly published version of your application. If you connect to your SQL Compact database in VS just to check the schema or contents of the default values, you will have modified the date timestamp of the .sdf file, so the next time you publish your application it will be treated as a new version of the database, and you need to have your data migration code in place to handle that.

I hope this helps. Basically, if you never change your database structure, ClickOnce will handle the data migration for you by copying the database forward. OTW you have to handle it yourself in code.

RobinS.

Seem to be lots of question in this forum and not many answers. Does anyone know the answer to this, as it seems like a problem that many people will encounter?

You'll have to be more precise about what "update their sofware version" means. This is a ClickOnce/Setup & Deployment Projectsforum, so is it one of these you're referring to? Update SQL CE or update some app that you're installing?

OK, sorry about that ambiguity. I am referring to a ClickOnce setup project. To make it clear, I have written a vocabulary learning program. The word database will be added to and modified by the user. My app may be updated as I improve it, but I don't want the user's database to be overwritten if they update to the latest version of the software.

Are you still looking for an answer to this question? It's covered in depth in the ClickOnce book written by Brian Noyes. I can try to summarize if you want.

RobinS.

GoldMail.com

Actually Robin I am. Apologies for the delayed response - other problems have been occupying me for the time being. If you wouldn't mind giving me a brief rundown of the answer I'd appreciate it!

I am still looking for an answer to this. Can someone give me some pointers? Thank you.

The problem is that it's like 10 pages in Brian Noyes's book, and distilling it down into a posting is a bit of a challenge. Hopefully this will help. Otherwise, go spend $30 and buy the book from Amazon.com

http://www.amazon.com/Smart-Client-Deployment-ClickOnce-Applications/dp/0321197690/ref=sr_1_1?ie=UTF8&s=books&qid=1206469474&sr=8-1

Basically, you deploy the database with the application as a data file. It goes into the folder defined by ApplicationDeployment.DataDirectory, or Application.UserAppDataPath. (Assuming you have it in the top level of your project, otw it puts it in a relative folder under that base path).

The database is only placed there when actually deployed, so when you test out of VS, you have to set the "copy to output directory" to "copy always" or "copy if newer", so the files will be copied to the same (relative) folder location under the build output folder for your project.

Then you have to add code that checks to see if you're running the deployed version (ApplicationDeployment.IsNetworkDeployed = true), and if not, adjust the path programmatically for your debugging (it will be under the Application.ExecutablePath + relative-folder-if-you-have-one).

string dataPath;
if (ApplicationDeployment.IsNetworkDeployed)
dataPath = ApplicationDeployment.CurrentDeployment.DataDirectory;
else
dataPath = System.Windows.Forms.Application.StartupPath;
dataPath = System.IO.Path.Combine(dataPath, "yourdatabasename.sdf");

When you deploy your app, it deploys the database in the DataDirectory. When you publish an update to the application, it copies the database forward to the folder for the new version of the application, assuming nothing about the server-side database has not changed in any way.

But if you change the database, that's where it gets complicated. It will copy the new database from the server to the new version's DataDirectory folder.

It will copy the old database to a .\pre subfolder under the DataDirectory folder for the new version.

Then you have to write code to migrate the old data to the new database. If you do nothing, it will ignore the previous version's database and use the new database.

You can get the code from Brian Noyes' ClickOnce book here: http://www.softinsight.com/clickoncebook/ In there, there should be two samples called SQLCompactDefaultMigration. v1.0.0.0 is the first version; this is the first database deployed. v2.0.0.0 is the second version, that has changes. If you run 1.0 and make changes to the data, then run 2.0, you will see that the changes made to 1.0 are missing.

The general process to migrate the data is to run a SQL script or a set of SQL queries to retrieve the data from the old database and insert the data into the appropriate tables in the new database. You might write this code as part of a separate migration utility that is invoked when the new version gets deployed. If you look at Brian's code, there is a sample of this called SQLCompactScriptedMigration. It includes v1.0.0.0 and 2.0.0.0 of the same application I mentioned above, but 2.0.0.0 contains the code in the main form's constructor to migrate the data.

The difference between 1.0 and 2.0 in his case is that a new table was added to the database with a fk relation to the old table.

And one thing about connection strings: If you include the |DataDirectory| placeholder in your connection string, the path will be filled in automatically by the runtime. For example, for Northwind, it would be
Data Source = "|DataDirectory|\Northwind.sdf".

Be sure your database is added to the project as a file with a Build Action of "content", and marked as a Data File in the Application Files dialog.

Another potential problem to be aware of is that the migration of a data file is triggered by *any* change to the data file in a newly published version of your application. If you connect to your SQL Compact database in VS just to check the schema or contents of the default values, you will have modified the date timestamp of the .sdf file, so the next time you publish your application it will be treated as a new version of the database, and you need to have your data migration code in place to handle that.

I hope this helps. Basically, if you never change your database structure, ClickOnce will handle the data migration for you by copying the database forward. OTW you have to handle it yourself in code.

RobinS.

Wow - thank you so much for this fine summary, above and beyond the call of duty. I think I get the gist, and if I run into serious problems, I'll buy the book!
Cheers, pp

I hope it helps. If you get stuck, feel free to post back and someone (me, if I'm around) will try to answer your question.

Good luck.

RobinS.

Your response was great (and I marked it as so!) on the DB updates using ClickOnce.

This is not DB file related but we seem to lose the settings inthe app.config on updates. Are the .config file changes handled in a similar way as to the Data files? any advice guidance you can point us to would be great!

Thanks!

Mike

Well, to be frank with you, we got a little irritated with the whole application config stuff built into VS and updating it and so on. We actually rolled out own config manager using a dictionary and read/writing it to/from XML.

When our users start up our application, it checks to see if the file is there, and if not, it creates it -- we have a bunch of defaults in our config file, then as the user runs the app, it adds in user preference info. Then it loads the dictionary, and we access that throughout our application.

We are placing this file in Environment.SpecialFolder.LocalApplicationData concatenated with our company name.

So you could include some kind of config file with your deployment, check to see if it is under the folder in LocalApplicationData, and if not, copy it over there, and use it from that location forever forward.

As for using app.config itself, I can't help you. ClickOnce definitely deploys it with each update, and doesn't carry the previous one forward. If you need it to do that, you have to find another way.

RobinS.

GoldMail.com

So I wonder when MS is going to realize that the ClickOnce deployment process is actually not "click once" and perhaps someone should look at a different way of deploying applications?
The current model is broken, frustrating, and defeats the ability to rapidly develope AND deploy an application making us all look stupid.
If the deployment process requires buying a third party book then digesting 10 pages of text to POSSIBLY solve the problem then the model is broken.