Windows Phone and Database Support

June 27, 2011 — 4 Comments

After developing quite a few applications for Windows Phone both personally and via my company RedBit, one of the biggest challenges is database support.  I’m not the only one complaining about this and if you have developed apps for Windows Phone 7, you probably have encountered this challenge and come up with workarounds to get data inside your application. 

There are many reasons why you want database support but my biggest reasons are it makes life a little easier to support offline scenarios for apps and to maintain code.  Currently, we have used a combination of methods such as binary, JSON and XML Serialization/DeSerialization.  Not the greatest solution but does work.

For the next version of Windows Phone, codenamed Mango, storing data in particular structured data, is now possible and is pretty easy.  With Silverlight for Windows Phone you are going to use LINQ to SQL for database operations.  You will also use LINQ to SQL to define your database schema in code.

How to access data?

Windows Phone DataContext and Local DatabaseWell the main namespace you will require is the System.Data.Linq which contains the main class to access data called System.Data.Linq.DataContext.  Combining DataContext, ‘plain old CLR objects’ (POCOs) and attributes, you can perform your CRUD operations on your data.  If you want a more thorough explanation, check out Local Database Overview for Windows Phone on MSDN.

But I’m Old School
old_schoolIf your are coming from Compact Framework world (and used to using SQL Compact, access to the data is not same.  For example, if you like using table direct to optimize data access (like I did Smile), well, you can’t do that with Windows Phone 7.  I’m assuming it’s because it’s V1 of SQL on Windows Phone but we’ll have to wait and see till next version.  From my preliminary testing and talking to the product group, they have focused on making things fast so for now I’m not worried.

What’s The Difference
If you are going to be using a local database for Windows Phone, you are going to have to be aware of some of the differences from using SQL Server. These are

  1. The database runs in process meaning, that it’s not running as a service like SQL Server but instead is only accessible only when you app is running.
  2. The database is when created is only accessible from the application that created it. The reason for this is it is created in Isolated Storage which is only accessible by the app that created it for security reasons. Think of it as a silo and other applications can’t access the data.

If you are coming from the desktop, then there are a few things you need to be aware of when using LINQ to SQL.  These are items that jump at me but all details can be found on the LINQ to SQL Support for Windows Phone on MSDN:

  1. There is no support for the ExecuteCommand which means you can run and Transact-SQL, DDL (Data Definition Language), or DML (Data Modeling Language)
  2. You cannot use LINQ to SQL to directly access ADO.NET objects such as the DataReader.  Any LINQ to SQL query will return an object collection defined by the DataContext
  3. BinaryFormatter is not supported – This basically means you will have to do some manual work here.  For example, if you are storing an image as binary in a column, you will only get binary back so conversion is not automatic.  You will have to use byte[], System.Data.Linq.Binary, or a LINQ to SQL CustomType

A few other things to be aware of

  1. If you are deploying a SQL Compact Database with your XAP (or it’s included in your project), the file is still accessible but it is read only.  If you want to write to it you will have to make a copy of it to Isolated Storage.  This might be use full if you are making a lookup table that never changes (for example drink mixes)
  2. You can update the database schema on the fly using the Structured Data API. Basically it will allow you to update Tables, Columns, Indexes, Associations/Keys but only basic updates. Complex upgrades might require a full database migration and custom code might also be required
  3. You cannot encrypt a database after it has been created. 
  4. You cannot share database across applications
  5. You can connect to a database from a Scheduled Task

What’s Next
Next up, I’ll follow up on a quick sample on creating a database and accessing the data on Windows Phone 7.  If you have already tried Local Database storage on Windows Phone let me know your experience by commenting below or via twitter @MarkArteaga

4 responses to Windows Phone and Database Support

  1. Is there not support for the visual designer than can generate all of the table code, just like you can with LINQ-to-SQL data models in other projects? I haven’t seen any WP7 Mango samples showing that, and they always write that manually, which would be a big pain in the ass, in my opinion.

  2. Hi Mark. I have made a Database first workflow possible in the latest release of the SQL Server Compact Toolbox – you might find it useful? http://sqlcetoolbox.codeplex.com

  3. Michael, following up in another post on how to creat these things, just need some time to polish it up :)

    Erik, cool stuff, I’ll check it out!

  4. With the bases loaded you srtcuk us out with that answer!

Leave a Reply