Mind your Copy to Output Directory Setting on Writeable Files

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 changing the data using the WPF application. The Windows Form was not showing the data changing, but the remotely synch’ed server side SQL Server was. What’s going on here?

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.

Designing Applications for the Occasionally Connected Scenario – NUFW Presentation

The presentation slides from my presentation at NUFW last night are below. It’s not much value without the demo’s and audio except for the links which I used to research and debug while creating the demo application. I have the presentation audio and may turn it into a webcast at some point in the future.

Designing For Occasionally Connected Apps Slideshare

View more documents from DeanWillson.

Using Local Database Cache for Occasionally Connected Data Synchronizing

After getting everything setup using the Visual Studio template and configuration wizards, I found that the server table was not being updated during the synchronization. Using SQL Server Profiler, I could see the SELECT queries that were interrogating the server instance to see if there were changes on the server, but I saw no indication of UPDATE or INSERT statements. I ran across a helpful blog post http://videoworld-rong.blogspot.com/2009/10/adonet-sync-services.html that indicated the default behavior of the Sync process is DownloadOnly where changes to the central database are downloaded to the local cached database. A simple change to OnInitialized event in the code-behind file of the .sync file resolved that issue easily.

   1: Private Sub OnInitialized()

   2:  ' ProductionHistoryDetail is

   3:  ' the name of the cached table

   4:  Me.ProductionHistoryDetail.SyncDirection = _

   5:     Microsoft.Synchronization.Data.SyncDirection.Bidirectional

   6:  

   7: End Sub

There were two other issues to overcome:

1. While testing what would happen if the database was taken offline between synchronization, I found that the .Net SQLClient Data Provider connection that the sync framework provider was using would block the process where the command was issued to set the database offline (ALTER DATABASE <databasename> SET OFFLINE).
   
2. Once the database went offline, the application would throw an exception when it attempted to connect to the server database to synchronize with the local SQL CE 3.5 database.

Solution:

1. ?? Still investigating.
   
2. Wrap the call to syncAgent.Synchronize() in a try/catch block. Surely there are methods to determine ability to connect to the db more efficiently than catching an exception.

Local Database Cache – Cached Table Add Button Grayed Out

I was experimenting with adding a Local Database Cache to a WPF client app I was working on when I ran into a situation where I could not configure the Cached table. There are a number of tutorials for how to set up the local database cache so I won’t go into much about those details except a brief introduction and where my experience differed.

The local database cache uses the Microsoft Synchronization Framework to synchronize tables between a local SQL Compact Edition .sdf file with a standard SQL Server database which is a really powerful feature for those occasionally connected scenarios. Visual Studio 2008 has a project template to make the process painless. See the photo below.

The step in the process that slowed me down was the point where you set connection to the client and server databases, then select the tables you want to keep synchronized. On my screen, the “Add” button below the Cached Tables list box was grayed out as in the following illustration.

After considerable searching and retries, I found a that I had neglected to set the primary key in the table I wanted to synchronize. I configured it as an Identity column, but forgot the click the key button in the SSMS table designer on the “Id” field. After correcting that, the “Add” button was no longer disabled on the table selection.