Despite the odds

Saving your time: how to work with large reference databases on WP7

Andrew Khmylov -

In the previous post I showed how to load the large SQL Server CE database on WP7. However, if you are working with a read-only reference database, deploying it to the phone or emulator may take up to several minutes. In this post I will try to explain how to save your nerves and time, making the regular development and debugging easier.

So for now, there are 2 options for deploying the database with your app:

  • Use Isolated Storage. Upload the database to the device with the help of ISETool. For ~50 mb database it takes 4 minutes on my laptop.
  • Pack it as the application content (setting Build Action to Content). In that case, the database is packed into the XAP file on your machine and then unpacked back on the device. While this approach is a bit faster then the first one, it still takes something about 1 minute to deploy your app, which I find unacceptable.

Note that the 1st option should be used only for Debug configurations. The only way to include the reference database when delivering the app to the users is by packing it into the XAP (you may, however, copy it to Isolated Storage upon the first launch, but I don’t see any sense why should you do so).

So I can’t fasten the packaging process, but I can upload my database to the Isolated Storage once and it will be preserved between the application deploys when debugging. When I need to release the application build, I will include the database to XAP file content. Sounds easy.

Being lazy motivates me to script the uploading routine. Here is a batch file that takes the IS snapshot with ISETool, adds the database file and restores the snapshot on the emulator:

set isePath="C:Program Files (x86)Microsoft SDKsWindows Phonev7.1ToolsIsolatedStorageExplorerToolISETool.exe"
set tempPath="C:TempDatabaseIS"
set dbPath="your_db_path"
set appId=your_app_id
%isePath% ts de %appId% %tempPath%
copy %dbPath% %tempPath%
%isePath% rs de %appId% %tempPath%

In the snippet above you need to replace your_db_path with the path to your .sdf file (relative to the script location) and your_app_id with Product Id of your application (you can find it WMAppManifest.xml file in the Properties subfolder of your project).

You will need another script file that will copy database to the emulator. The only difference is that you will use “xd” argument instead of “de” in lines 8 and 10.

Not hard so far, right?

Now we need to adjust the connection string, so that it points to the isolated storage in DEBUG configuration and to the content file otherwise.

#if DEBUG
    var source = "isostore:test.sdf";
#else
    var source = "appdata:/test.sdf";
#endif
    var connectionString = String.Format( "Data Source='{0}'; Max Database Size = 60; File Mode= Read Only;", source);

The last step – configuring the project file so the database file is not packed into XAP in DEBUG mode. Unfortunately, Visual Studio doesn’t allow you to specify conditions for files in the project, so you have to modify the project file manually.

Add the database file to the project and set its Build Action to Content.

Save you solution, right-click the project file and select Unload Project, then right-click again and select Edit %ProjectName%.csproj.

Locate “test.sdf” string in the project file:

Add the Condition attribute the the Content node, so it looks like:

<Content Include="test.sdf" Condition="'$(Configuration)' == 'Release'">
     <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
</Content>

Here we specify that file test.sdf should be included in the project as Content only when the project configuration is set to ‘Release’.

Now you can reload the project in Visual Studio. You won’t see any changes in the Solution Explorer, but try building the solution – the database will be added to XAP only in Release configuration. Don’t forget to re-launch the deployment scripts every time you uninstall the application or debug it on the new phone.