I’m used to doing more web development so I sometimes forget that just because an application writeable file (.xml, .csv, .sdf, .txt, etc.) resides in the project root directory structure, doesn’t mean that is the actual file that is getting written to when developing WPF applications. Depending on the “Copy to Output Directory” setting, that actual file may or may not get copied to the bin/Debug folder which is where the executable program resides (when debugging).
The “Copy to Output Directory” setting options are:
- Do not copy – If you use this option, but you make changes to the copy in the project directory, the changes will not get copied to the bin/debug directory which the executable uses at runtime.
- Copy if newer – Be careful with this if you are sloppy about making changes in one location one time, and another the next. What changes are overwritten with each compile can be unpredictable.
- Copy always – Caution: If you use this, understand that if you make manual changes to the copy in the bin/debug directory, your changes will be overwritten by the version in the project directory at the next compile of the project.
Local Database Cache example
I created a WPF application that stores data locally on the client in a SQL CE (.sdf) database which periodically synchronizes with a server based SQL Server database. Since SSMS 2005/SQL Profiler does not have the ability to connect to a SQL CE database to view the data in the .sdf file, I created a quick “diagnostic” Windows Forms client to peek into the local client database to view the data to make sure the WPF program was operating as expected. I pointed the “diagnostic” Windows Form connection string to the .sdf file in the project root directory, then started 
Oh yeah, that’s right… the Windows Form was looking into the original client database that was created with the local database cache Visual Studio project wizard at the project root - not the live copy that was running out of the bin/Debug directory (See the Solution window screenshot). After I figured that out, I changed the connection string on the “diagnostic” Windows Form app to look at the .sdf file in the bin/Debug folder. Then in order to avoid getting confused about the data I was looking at between the remote database and the local client side database I needed to make sure I understood when the .sdf file in the project root directory was going to get copied into the bin/Debug folder upon recompile via the “Copy to Output” settings on the .sdf file.
@deanwillson on twitter
connect on LinkedIn
Posts
14 comments:
I followed the instructions here, to place the DB in the Bin/Debug folder and renewed the connection string.
My DB is set to copy if newer , is this right?
But my DB is still not getting saved I have a -1 in the AutoID column, which means its not saved and this is causing a concurrency violation on related records.
It is only getting saved when I close and reopen which I don't know why it so.
The way I save is typical,
Try
Me.Validate()
ProjectBindingSource.EndEdit()
ProjectDetailBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.PTTDataSet)
Me.ProjectDetailTableAdapter.Update(Me.PTTDataSet.ProjectDetail)
' Me.Form1_Load(sender, e)
MessageBox.Show("Data Saved Sucessful!", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show("Data Failed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
I would highly appreciate any help
Abe Perl
Abe, If you are just running your project from within Visual Studio, the Copy if Newer setting is probably fine. In that scenario, any data that was stored in the SQL CE database the last time you ran it in Visual Studio will be available the next time you run the program. As for why it is not saving, is it throwing the exception in your try block, or just not displaying the saved data? You indicated the data is there if you close and re-open the app. If this is a project using the Sync framework, is your AutoID property a GUID, or integer? I found that using a Linq Data Context, the AutoId functionality did not generate for me. I needed to explicitly call the Guid.NewGuid() method to set the ID as in oDetail.Id = Guid.NewGuide where oDetail is my repository object and the Id property is the Primary Key.
More information:
I added a call to Guid.NewGuid because the MSSQL default, newid(), for the Id column did not transfer to the SQL CE .sdf schema definition' and the Linq DataContext does not auto-generate either when it was generated from the MSSQL schema, or if you manually set the property on the Linq class to true.
I hope that helps.
Hi Dean thanks for the quick reply,
When I click save after entering the new record, I am not getting an error, but usually if it would of been saved I would of see that the new record in the Auto Identity integer column -1 turns into a ID. In this case I receive the message "saved" but it stays the same "-1" .
But when I try to change the data on the new record and hit save I get the concurrency violation error.
This is also preventing me from adding new related records due to the concurrency violation.
My Auto ID column is an integer Identity column .
And yes the interesting part is, that after adding a new record and hitting save I check in the DB and see that the data is there. Now when I reopen the form if I go the last record then it allows me to enter related records.
I would appreciate further help,
----
Abe
Abe, it doesn't sound like you are populating the Dataset table with the @@IDENTITY value of the row inserted into the SQL CE table. Maybe you need to rebind the source or the form? The following link is a great resource for setting up a master/detail form using SQL CE. Note the section near the end titled "Adding Code to Handle Inserts to SQL CE Databases". http://blogs.msdn.com/b/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx hopefully it will provide more insight to your issue.
Thank you so much Dean!
Now it works nicely.
I was missing an important part... the @@Identity handling.
Thanks again!
Abe
Abe, I'm glad you were able to get it working.
--Dean
Hi,
I am now seeing another problem which prevents to enter my data on an other form.
The error I get when saving the data is "Prepare: Command text Property has not been initialized.
It uses the same Ds as above
I would appreciate any help, I count on the pro's!
Abe,
Check and make sure your table has a primary key defined. http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/4be43c76-b820-4961-8ed0-c71c17461bb9
Thanks so Dean , I really appreciate your extensive help!
Abe
Hi Dean,
I know I am shifting the topic, but I need more help, and I see you are a professional so I hope you don't mind that I ask.
I know I can deploy with sqlce as a clickonce project.
I would like to know if and how I can publish my application with a SQL Exppres (MDF) db??
Where should I put the MDF and the LDF files?
The reason why I want to use it with a MDF is because its not so limited asa SQLCE db/
I would really appreciarte your help, I cannot find this info nywhere.
Thanks again
Abe Perl
Abe,
I'm sorry, but that is not really my specialty. I generally develop corporate intranet line of business apps that use a full SQL Server edition like Standard or Enterprise. I usually either have direct access to the server for install configuration, or I have an IT contact that configures it based on my documentation or instruction.
You might want to consider looking into Web Deploy or Installshield LE http://blogs.msdn.com/b/deployment_technologies/archive/2010/04/20/installshield-limited-edition-is-available-for-download-in-visual-studio-2010.aspx when I have developed off-the-shelf software in the past, I used Installshield to create the installation packages.
Dear Dean,
I read your article and here is my scenario.
I need to edit local resource (.resx) files from within my asp.net app. Which in turn expires my sessions. Can I follow your tip regarding DO NOT COPY option and then try?
Do you have any solution for my scenario?
Thanks, Dean! I'm such an idiot!
Post a Comment