Wednesday, October 19, 2011

Windows Phone Mango SQL CE tips and tricks


   Hi windows phone developers,
   I’m currently working on a windows phone project where I use as storage medium the local database feature of new Windows Phone Mango.
   After playing with some configurations I come to recommend you some nice shortcuts to take in your projects:

1. Tables(Entities) and DataContext automatic code generation.

   Instead of writing all that code by hand, why not take advantage of existing designers in Visual Studio + some very interesting tools.
   Let’s add to the project an SQL Compact 4.0 Local Database file (.sdf) with the name you wish. When you confirm the file addition you will get this error:
“The assembly reference "System.Data" could not be added to the project. This wizard will continue to run, but the resulting project may not build properly.
trick_02
   Don’t worry, we will use this file as a blueprint for next phase and it should never be included into the project (in this scenario), so pay attention to change file property “Build Action” to “None”.
   Next create a data connection to this new created local database and design it on your will:
trick_01
   Now, we are going to need a nifty tool to generate all the plumbing and code that will handle the data storage into windows phone (mango) local database.
   This tool is named "SQL Compact Code Generator” and you can install it from Extension Manger (not for Visual Studio Express) (http://visualstudiogallery.msdn.microsoft.com/119234ae-bd87-415f-af87-098b72b90e9a?SRC=VSIDE). Luckily, it has also an independent tool for same purpose that can be used by those with Visual Studio Express(http://sqlcecodegen.codeplex.com/).
   Thank you Christian for this nice addition, we really appreciate it.
   Once the tool is installed  from ExtensionManager all you have to do is to set the database file property “Custom Tool” to value “SQLCEMangoCodeGenerator” and to add System.Data.Linq reference to your project.
screen_02     screen_01
   Immediately we should see beneath our database file a “.cs” file that contains all the generated code.
  Done!
  You can write your code for accessing datacontext and table entities the ordinary way.

2. Performing backup and restore for a SQL CE windows phone mango database

   Ok, we should know already that in Windows Phone Mango, the local database isn't but just another file into the isolated storage (or application folder). For isolated storage case, a simple connection string should look like this:
public static string ConnectionString = "Data Source=isostore:/SampleDB.sdf";

   Now that we know this fact, we can make a backup of this file into a new “*.bak” isolated storage file and consider this one as the database backup.

trick_03

   Of course we should pay attention on some aspects:
- during the backup/restore operation make sure that all the DataContext(s) that are using this database file are properly disposed and closed (also apply some sync mechanism for concurrent access); otherwise you will encounter a nice error message: “Operation not permitted on IsolatedStorageFileStream.”
- after a restore operation repopulate the interface and/or modelview with fresh data.

trick_04   A sample project to demonstrate backup/restore of local database can be found on the this link.



I hope these tricks will help you spare some time.
See you soon!

4 comments:

  1. I suggest you have a look a SQL Server Compact Toolbox, which can create an improved DataContext, with Indexes and Foreign Keys

    ReplyDelete
  2. Indeed Erik, SQL Server Compact Toolbox is a great tool.

    I'm testing generating the DataContext for a Windows Phone project/library and it seems I have to find some tricks to make it work. Any ideas!?

    Regards,
    Claudiu Farcas

    ReplyDelete
  3. Ok.. so the trick is to create another project of type Console, Winform, Library etc. where you can create a SQL CE 3.5 "Local Database". . then after, you copy this new created file into Windows Phone project.
    After, you have to create a connection to this file into Server Explorer - Data Connection (make sure you use Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5)).
    After, from SQL Server Compact Toolbox (and having Windows Phone project selected into Solution Explorer) right click on the Database file and choose "Add Windows Phone DataContext to current Project ..." option.
    This should launch the datacontext creation wizard.

    Well done!
    Nice tool Erik! ;)

    ReplyDelete