0

I have a WPF project set up to use a local SQL Server Compact database through an ADO.NET Entity Data Model in Visual Studio Express 2012 for Desktop. The project works great, on first run I can load all of the data, manipulate it as I please and come back later with the changed data still in place.

I noticed while doing a little restructuring to the schema that the data visible to VS was only the very first bits of data that I entered manually when creating the database and the next time I compiled all of the data I had added since was gone!

After some digging, I came to the conclusion that the compiled version of the app was using the SDF file sent to the bin/Debug folder by the file's Content:Copy If Newer build action. This means that there could be as many as 4 different copies of the database to be worried about: project folder, debug folder, release folder, and the deployed copy on the end user's PC.

I would like to have a single copy of the database on my dev machine that is accessed by both debug and release compiled versions and the database explorer in VS that is installed on the end user's PC by ClickOnce. I suppose I could change the connection string to an absolute path during development and hope I can remember to change it back to relative before I publish for deployment.

Finally, I foresee the need to release updates for this application as well and am worried that such an update would erase the end user's data if improperly done. If possible, I would like to be able to only update the schema of the end user's database without touching the data itself whenever I release an update. If this is not possible that is acceptable and I'll just have to make sure I put every structure I can think of into the database before my first deploy.

In summary my questions are the following:

  1. How to share a single sql compact database between VS, debug, and release?

  2. How to handle local database during application deployment and updates, with the optional ability to update the database schema without erasing the data?

Improve this question
2
  • 1
    You can also create the database programatically to be in total control. Commented May 31, 2013 at 11:05
  • That could work. Is there an example of this somewhere? I would like to maintain the use of EF for classes and DataSources for binding if possible. Commented May 31, 2013 at 14:29

1 Answer 1

Reset to default
1

I have a similar application and I keep the database file completely separate. Because you may also need to do updates that you don't want the user database overwritten. I have a process that checks the database schema before the EF connection takes place. So when my users install this application it requires they download the database file from my webserver and puts it in a specific location on their computer.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I suppose I could send the database file to AppData or something. Could you detail the schema check process you mentioned?
When the app starts I check to make sure the file exists. Then I have it do checks for any schema changes for example if I added a column to a table in my dev database then I just check for that column and if it isn't there I do a sqlCE command to add the column and in some cases update the value in the column before trying to use the EF connection. This may seem like a lot of overhead to start the program but in the long run its much easier to keep my data in sync with my customers and still allow them to keep their personal portion of the database from being over written.
Okay, I'm not too worried about EF being out of sync because that should be contained in the update I send. What I'm still unclear on, is the following scenario: SomeUser has SomeDatabase with Schema1 and Data1. Then I update my copy of SomeDatabase to Schema2. What must I do to to create an update that will update SomeUser's database to Schema2 leaving Data1 completely intact (unless columns were removed of course)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.