SQL CE is one of the most appreciated features for developers introduced in Windows Phone 7.5: before SQL CE the only way to store data locally was serialization or remote services (for example, on the cloud). With SQL CE, instead, we have full access to a relational database and we can work with familiar concepts like tables, relations and so on. Windows Phone officially supports two ways to work with a SQL CE database:
- Code first: the database is created starting from the definition of our entities. We create a class for each entity and, with the help of some special attributes, the database is created when the application is launched for the first time. These attributes will help us to define the tables, the columns, the relations and so on. Since the database will be created when the application is launched, it will be obviously empty.
- Read only: the database is created out from the application, with an external tool (like the Server Explorer tool, that is part of Visual Studio). The file will be a SDF file, that will be included into the Visual Studio project. This solution is the best choice when we have a prepopulated database and we need to display the data in our application. The downside of this approach is that the database will be read only: we’ll be able only to read data from it, we won’t have write access.
In my experience I had often the need to have best of both worlds: I have a database that is prepopulated with data that is displayed in the application but, at the same time, I want the chance to write stuff. There’s a way to do it: copying the database in the isolated storage when the application is launched for the first time. This way, we can simply add the database to the project like if we want to use it in read only mode: after we’ve copied it into the storage, we will be able to write data into it like if it was created in code first mode.
The code to achieve this result is not really straightforward, so please welcome SQL Server Compact Toolbox: I’m a big fan of this extension, developed by the fellow MVP ErikEJ, and I’ve used it extensively in my Windows Phone project. It’s a Visual Studio extension, so it can be installed directly from the Visual Studio Gallery.
This extensions provides a series of utilities that come in handy when you work with SQL CE database: one of them is the Windows Phone support, that allows, starting from an existing SQL CE database, to generate all the classes needed to work with it in a Windows Phone application (both entities and data context).
And what about our scenario? The generated data context contains a method called CreateIfNotExists, that overrides the original data context methods that are used to check if the database already exists, and if not, to create it. Basically, this the the code that we were writing in the past:
using (CommunityDaysContext db = new CommunityDaysContext(CommunityDaysContext.ConnectionString))
{
if (!db.DatabaseExists())
{
db.CreateDatabase();
}
}
Using SQL Server Compact Toolbox we can simply write instead:
using (CommunityDaysContext db = new CommunityDaysContext(CommunityDaysContext.ConnectionString))
{
db.CreateIfNotExists();
}
This method will return true or false to reflect if the database has been created or not. If you take a deeper look to the DataContext class generated by SQL Server Compact Toolbox you’ll notice that the CreateIfNotExists method is much more than simply a wrapper to the original DataContext methods: that’s because the method contains all the logic needed to check if there’s a SQL CE database embedded into the project and to copy it into the Isolated Storage.
To trick the magic we simply have to copy the SDF file into our project and make sure that the Build Action (that can be changed in the Properties panel) is set to EmbeddedResource.
And now you’re done: simply call the CreateIfNotExists when the application is starting (for example, in the Application_Launching event inside the App.xaml.cs file, or when the MainPage of your app is loaded) and the database will be copied into the isolated storage. To access to it you’ll have to use the regular connection string (since it’s not a read only database): the generated DataContext will automatically provide it with the ConnectionString static property.
Watch out the database size!
Be careful that the bigger is the database, the longer is the time needed to copy the database into the storage. Remember to provide a loading animation in case the loading operation is longer than 2 or 3 seconds and to postpone it after that the application is fully loaded, otherwise you’ll risk to exceed the 5 seconds time limit allowed to display the splash screen.
What if the database is stored in another project?
If, as a developer, you are used to split your solution in different projects (for example, one for the main application, one for the services, etc.) you may find yourself in the situation where the database is stored in one project, while the entities and the data context in another one.
In this case, the code generated by SQL Server Compact Toolbox won’t work, because the CreateIfNotExists method will look for the database inside the same assembly of the application (using reflection). Don’t worry, there’s a workaround! The first step is to change the Build Action of the database to Content, then you’ll have to change the CreateIfNotExists method in the following way:
public bool CreateIfNotExists()
{
bool created = false;
using (var db = new DbContext(DbContext.ConnectionString))
{
if (!db.DatabaseExists())
{
//string[] names = this.GetType().Assembly.GetManifestResourceNames();
//string name = names.Where(n => n.EndsWith(FileName)).FirstOrDefault();
StreamResourceInfo stream = Application.GetResourceStream(new Uri("Database/Recipes.sdf", UriKind.Relative));
if (stream != null)
{
using (Stream resourceStream = stream.Stream)
{
if (resourceStream != null)
{
using (IsolatedStorageFile myIsolatedStorage = IsolatedStorageFile.GetUserStoreForApplication())
{
using (IsolatedStorageFileStream fileStream = new IsolatedStorageFileStream(FileName, FileMode.Create, myIsolatedStorage))
{
using (BinaryWriter writer = new BinaryWriter(fileStream))
{
long length = resourceStream.Length;
byte[] buffer = new byte[32];
int readCount = 0;
using (BinaryReader reader = new BinaryReader(resourceStream))
{
// read file in chunks in order to reduce memory consumption and increase performance
while (readCount < length)
{
int actual = reader.Read(buffer, 0, buffer.Length);
readCount += actual;
writer.Write(buffer, 0, actual);
}
}
}
}
}
created = true;
}
else
{
db.CreateDatabase();
created = true;
}
}
}
else
{
db.CreateDatabase();
created = true;
}
}
}
return created;
}
In this code we’ve changed the loading by reflection with the Application.GetResourceStream method, that is able to load a resource inside the XAP (so it works even if the database is not in the same project) as a Stream. This method requires, as input parameter, the Uri of the file (starting from the root of the project): in the example, the database is called Recipes.sdf and is placed inside the Database folder.
That’s all! Have fun with SQL CE and Windows Phone!
