Windows phone: Mango DB

Did you know the Windows Phone Developer Tools 7.1 Beta is out? Wow, what a mouthful and very, very exciting.

One of the new features to be supported when Mango is released, is a database. Databases are common place in other mobile devices, but this was a missing feature from the original release of Windows phone 7, but now it’s here, in mango. let’s run down what you need to do to get the minimum database up and running.

The DataContext code

   1:  using System.Data.Linq;

   2:  using Mango1.Models;

   3:   

   4:  namespace Mango1.Data

   5:  {

   6:      public class MangoDb : DataContext

   7:      {

   8:          public MangoDb()

   9:              : base("Data Source=isostore:/MangoDb.sdf") { }

  10:   

  11:          public Table<SuperJuicy> SuperJuicy;

  12:         

  13:      }

  14:  }

As you see mango supports none other than (a lighter) LinqToSql. This is a refreshing return but we’re old friends and if your not, I think you’ll like him. We shall call him MiniLinq

In order to interact with the database You need to inherit from System.Data.Linq.DataContext and provide a default constructor that passes in the connection string. You can complicate the hell out of this, if you want, but this is all you’ll need to do.

This connection string is really simple, at a minimum you only need to supply the standard SQL “Data Source” property, but it’s value is completely different, it has isostore:/ at  the front of the database file name.

You can have two different kinds of databases in Mango:

1. Read only databases deployed inside your application; 
2. Read/Write databases stored in Isolated Storage.

isostore:/ means we are creating a Read/Write database in Isolated Storage.

The next step in the process is to create your parent entities, called aggregate roots. In this example we are defining a public field called SuperJuicy of type Table<SuperJuicy>. This, believe it or not, is auto-populated by MiniLinq at runtime, when you create the DataContext. Also, this is OK too, in a real implementation the MangoDb will be wrapped by the Repository pattern.

The ORM

   1:  [Table(Name="SuperJuicy")]

   2:  public class SuperJuicy

   3:  {

   4:      int juicyId;

   5:   

   6:      [Column(Name = "JuicyId",          

   7:          IsPrimaryKey = true,           

   8:          IsDbGenerated = true,          

   9:          DbType = "int IDENTITY(1,1)",  

  10:          AutoSync = AutoSync.OnInsert,          

  11:          CanBeNull = false,             

  12:          Storage = "juicyId"            

  13:          )]

  14:      public int JuicyId { get { return juicyId; } }

  15:   

  16:      [Column(Name = "JuicyRating",

  17:          CanBeNull = false,

  18:          DbType="int"

  19:          )]

  20:      public int JuicyRating { get; set; }

  21:  }

Mapping objects to your relational schema is really nice, there is no DBML, so you’ll have to type those attributes out by hand (maybe SQLMetal can, but this is the world of code-first man, get your hands dirty)

Give your table a name by adorning the Table attribute to your class. My table will be named SuperJuicy. At the very minimum you need one property on your class when the column is not Db Generated and two, if you have a Db Generated column.

Let’s walk through the specification of the JuicyId property:

• I am a read only property named JuicyId
• My column Name will be called JuicyId
• My database type is integer and will be an Identity seed 1, increment 1.
• I am a Primary Key
• The database will create my values
• The database will synchronise this value when I am first inserted.
• I cannot be null
• Because I am read only, my backing field is juicyId (VB.net _juicyId)

Too easy? This is easier, the JuicyRating property:

• I am a read/write property name JuicyRating
• My column name will be called JuicyRating
• My database type is integer
• I cannot be null

Now we have a mapped class, lets use it. For this example, we can just do this where ever you want:  

   1:  using (MangoDb mangoDb = new MangoDb())

   2:  {

   3:      if (!mangoDb.DatabaseExists()) 

   4:      {

   5:          mangoDb.CreateDatabase();

   6:      }

   7:   

   8:      SuperJuicy mango = new SuperJuicy { JuicyRating = 180 };

   9:   

  10:      mangoDb.SuperJuicy.InsertOnSubmit(mango); 

  11:      mangoDb.SubmitChanges();

  12:   

  13:      int id = mango.JuicyId;//Will be 1 :)

  14:  }

The important things in this code is we create the MangoDb, check to see if the database exists, if not create it.

InsertOnSubmit does not insert straight away you can record a whole heap of steps before calling SubmitChanges and this will happen in a batch. This is currently, the recommended approach.

MiniLinq is a light weight LinqToSQL and is provided in Windows phone Mango. This is just touching the surface and we’ll talk about database schema syncing tomorrow. There will be blood, sweat and tears on this, I promise.