If you’re a  Windows Phone 7  developer you’ll probably know that with ‘Mango’ it is now possible to persist data using a local database based on SQL Server CE  whose data files are stored inside isolated storage, this is a quick overview of how to save and load data on a Windows Phone 7.1 application.
First of all, if you, like me, gave Linq2Sql a change when it came out you’ll probably happy to know that your investment has gone totally lost from Entity Framework advent since all database interaction is based on  Linq2Sql.
In order to access some data we need a database with some tables and, honestly, this is a quite boring part since all MSDN documentation states that everything must start from a class decorated with specific mapping attributes, since I’m a lazy man (or better, I like to spend my time in a more profitable way) I’ll show you a quick alternative but beware, this is totally unsupported by Microsoft (but it works)

You know that Linq2Sql has a designer right? so, why don’t we give it a try? well let’s start creating our very intricate database :-) adding a new Linq To SQL Classes item into a separate Windows Project:

image

Now let’s design the entities using Visual Studio designer:

image

If you use IDENTITY columns for primary keys, be sure to set the Server Data Type:

image

Now let’s use “Add as link” feature to add the file containing generated classes (MyDb.Designer.cs in my demo…) to a WP7 project:

image

If you now compile the phone application the compiler will complain about missing IDBConnection, just comment those lines from .designer.cs file and' problem will gone.

Now, let’s create the database inside Isolated Storage using CreateDatabase method , please note the special connection string format “isostore://[file].sdf”

public static string DbConnectionString = "Data Source=isostore:/MyDb.sdf";

public MainPage()
{
   InitializeComponent();

   using (MyDbDataContext db = new MyDbDataContext(DbConnectionString))
   {
      if (!db.DatabaseExists()) db.CreateDatabase();
   }
}

And now it’s just a matter of writing some Linq To Sql code, to give you a brief idea, this adds some Customers with some Orders into MyDb database:

private void button1_Click(object sender, System.Windows.RoutedEventArgs e)
{
   Customer c1 = new Customer() { FirstName = "Corrado", LastName = "Cavalli" };
   Order o1 = new Order() { ProductName = "HTC HD7", Quantity = 2 };
   c1.Orders.Add(o1);
   Customer c2 = new Customer() { FirstName = "Giulia", LastName = "Cavalli" };
   Order o2 = new Order() { ProductName = "IPhone 4", Quantity = 1 };
   c2.Orders.Add(o2);
   Customer c3 = new Customer() { FirstName = "Fabio", LastName = "Cavalli" };
   Order o3 = new Order() { ProductName = "HTC Mozart", Quantity = 2 };
   c3.Orders.Add(o3);
   using (MyDbDataContext db = new MyDbDataContext(DbConnectionString))
   {
      db.Customers.InsertOnSubmit(c1);
      db.Customers.InsertOnSubmit(c2);
      db.Customers.InsertOnSubmit(c3);
      db.SubmitChanges();
   }

}
 
while this just queries those Customers who bought a HTC phone:
 
private void button2_Click(object sender, System.Windows.RoutedEventArgs e)
{
   using (MyDbDataContext db = new MyDbDataContext(DbConnectionString))
   {
      var results = from c in db.Customers
                    where c.Orders.Count > 0
                    from o in c.Orders
                    where o.ProductName.StartsWith("HTC")
                    select o.Customer;

      foreach (var customer in results)
      {
         System.Diagnostics.Debug.WriteLine("{0} {1}", customer.FirstName, customer.LastName);
      }
   }
}
 
Simple, isn’t it? but: What if Schema changes? (be sure it certainly it will…)
Let’s suppose Customer now has an additional Discount column (of type int nullable since we want to keep currently saved data)
image
Adding necessary upgrading schema strategy requires us to modify startup code this way:
 
using (MyDbDataContext db = new MyDbDataContext(DbConnectionString))
{
   if (!db.DatabaseExists()) db.CreateDatabase();
   DatabaseSchemaUpdater updater = db.CreateDatabaseSchemaUpdater();
   if (updater.DatabaseSchemaVersion == 0)
   {
      updater.AddColumn<Customer>("Discount");
      updater.DatabaseSchemaVersion = 1;
      updater.Execute();
   }
}

where you can see the use of a DataSchemaUpdater class to add missing column and set a version to new database schema (DataSchemaUpdater allows adding of Tables,Indexes, Associations and Columns)
One final note: If you wish to distribute a pre-populated read-only database you can just add it to your xap using CopyLocal=true and connect to it using following connection string: “datasource=’appdata:/MyDb.sdf’; mode=’read only’” in this case data will be available in read-only mode, don’t forget the mode=… part otherwise you’ll get an exception when you try to query the database.
 
Have fun!
Technorati Tags: ,