Storing Windows Phone application data is a pretty normal requirement. If you want it to persist across sessions, or even phone reboots, you really have three options:
- Save to the Cloud
- Save to Isolated Storage
- Save to Local Database
Each is correct for certain scenarios. But in this article, I will ONLY walk through the Local Database. This is an enhanced version of SQL CE – meaning your SQL skills are an asset. And, if you don’t have them, they come quickly.
Note: this is Part 2
In my previous post we discussed Option 1: Creating a Database with Code-first. This generates your database at run-time, based on DTO object decorations.
Is a Database worth the Effort?
Large Quantities of Data
A database can filter large data sets without loading them into memory. Isolated Storage, on the other hand, must de-serialize ALL records into memory to return a filtered set.Transactions and Roll Backs
A database will roll back any changed records if one, inside a transaction, fails for any reason. Isolated Storage, on the other hand, has no mechanism to roll back on errors.Referential Integrity
A database enforces foreign keys. You cannot save a record that references another if the other does not actually exist. Isolated Storage, on the other hand, does not enforce keys.
Option 2: Use the Visual Designer
In 5 Easy Steps 
It seems most intuitive to use the database visual designer at design-time. It is most unfortunate that this method is the least supported. Moreover, it seems just messy that we must drop to the Visual Studio DOS prompt and use SQLMetal to get it fully working.
Step 1: Create the Database
The first thing you need to do is create your database (the SDF file). In Visual Studio, open the Server Explorer and right click Data Connections. Select Add Connection and the wizard will do the rest for you.
Remember: you want a version 3.5 database, not 4.
Step 2: Construct your Schema and Default Data
This is completely up to you. You can create tables without data. Or, you can create tables with data so that there are lookups or defaults when your application is installed.
Step 3: Include the Database in your Project
Once you include it in your project, then it will be included in your Phone installation. And, as a result, it will be in your App Folder so you can access or move it later.
Step 4: Generate your Entities with SqlMetal
There is no UI-oriented way to generate classes from an existing database (yet). Until there is, we will use SqlMetal. SqlMetal is a command-line tool that generates code and mapping for the LINQ to SQL component of the .NET Framework. By applying options, you can instruct SqlMetal to perform several different actions that include the following:
-
From a database, generate source code
-
From a database, generate a DBML file for customization.
-
From a DBML file, generate code
SQL Metal requires the Visual Studio Command Prompt. It’s easy to find: click START and type COMMAND. Then you should see a filtered list somewhat like this:
SQL Metal has some command line parameters:
- <input file> this is input file (the path to our SDF)
We will use [“c:\projects\sample\MyDatabase.SDF”] - /code[:file] this is the output file (the resulting CS)
We will use [/code:”c:\projects\sample\MyDatabase.cs] - /namespace:<name> this is the default namespace
We will use [/namespace:Sample] - /context<:type> this is the base class of the entities.
We will use [/context:MyDataContext] - /pluralize this will automatically make classes singular or plural
We will use [/pluralize]
Our syntax looks like this:
c:\Program Files (x86)\Microsoft Visual Studio 10.0\VC> SqlMetal "c:\Projects\sample\MyDatabase.SDF" /code:"c:\Projects\sample\MyDatabase.cs" /namespace:Sample /context:MyDataContext /pluralize
Remember: once you get the command line syntax correct and working, you save it to a text file for later. No need to waste time.
STEP 5: Clean up the Data Context
A. Delete broken Constructors
There are two constructors included in your new data context which are wrong. There’s nothing you should do to fix them. Just delete them.
Remember: If you run SQL Metal again, the file will be re-generated and these will re-appear. In that case, just delete them again.
B. Implement your partial OnCreated() Method
What is a partial method? Partial methods are for partial classes (one class in multiple files). It allows you to specify methods without implementing them. It assumes (although it is optional) that methods are fully implemented in another of the class’ partial files.
Remember, do not edit the generated file – your changes will be erased if you ever run SQL Metal again (which re-generates the file).
Above, we have created a second, partial class for MyDataContext. OnCreated is called inside every constructor, this gives you a perfect chance to ensure your database has been copied from the Application Folder to Isolated Storage.
C. Move your Database File (at run-time)
Users find your application in the marketplace. They download it. Windows Phone creates a dedicated App Data folder. Here, the XAP file is expanded. This will include databases you bundle. This will work fine. But, sadly, a database in an App Data folder is read-only. As a result, we must copy our SDF from App Data to Isolated Storage.
Remember: only move your database if you must write to it. If your application is read-only, leave it where it lies to save space.
Get real code here.
Conclusion
Using this approach, we pre-create our database and even its data. SQL Metal creates our objects and data context. Then, ship it with the installer and copy it to Isolated Storage. That’s it, then we can read and write to it like a standard local CE database.
This is a handy approach because we get to visually build our database and interact with it at design-time.
However, you see all the hacks you need to do in order to use this approach, right? As a result, this is an unsupported approach. The code-first is the recommended approach for you. Thankfully, now, you can choose what is best for you.