Sunday, June 05, 2011

Using Local Database in WP7-Mango

by Corrado Cavalli via Corrado's BLogs on 6/5/2011 4:34:17 PM

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:

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

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

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:

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();
   }

}

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);
      }
   }
}

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();
   }
}