DarksideCookie

I finally got my act together and started doing some WP7 development again. It has been a while, and a few things have happened since I was last doing it…Mango for example. This also means that there are a heap of new features available to me as a developer, but the one that I have got real excited about is actually the database support…

So, Chris, being who you are, why in the world did you chose that feature to get excited about? Well, partly because it was the first new feature I have tried out. But mostly because I sort of didn’t expect to be as neat. Knowing that it was based on SqlCE, I sort of expected it to use a subset of ADO.NET, and regular SQL for access. However, color me surprised when I realized that that wasn’t the case. Instead, it uses Linq-to-SQL and attribute based entity mapping. And on top of that, I didn’t have to create a base database and include it in my project in some obscure way. The database can be created based on the attributes, which is a nice little treat…

But let’s kill my rant and look at some actual code!

The app I will be looking at is a ridiculously simple app for storing “to-dos”. On top of storing to-dos, it is possible to add comments to those to-dos (is “to-dos” even a real word?). So basically you get a list of to-dos, and when you look at one of them, you can read comments for it as well as add new comments. Like this

Ok, now that we know what I am building, it is time to look at code.

The first thing I need are a couple of entities that will be used for mapping data back and forth to the DB. In my case, I need 2 – Item and Comment (item being a to-do).

The entities as such are pretty simple POCOs…at least if we look away from the attributes that will be put in there. But let’s start as POCO.

public class Item
{
    private int _id;
    private DateTime? _creationDate;
    private EntitySet<Comment> _comments = new EntitySet<Comment>();
        
    public void AddComment(Comment comment)
    {
        _comments.Add(comment);
    }
 
    public int Id { get { return _id; } }

    public DateTime? CreationDate { get { return _creationDate; } }

    public string ThingToDo { get; set; }

    public bool Complete { get; set; }

    public IEnumerable<Comment> Comments
    {
        get { return _comments; }
    } 
}

and

public class Comment
{
    private int _id;
    private EntityRef<Item> _item;
    private DateTime? _date;
    internal int _itemId; 
 
    public int Id { get { return _id; } }

    public DateTime? Date { get { return _date; } }

    public string Text { get; set; }

    public Item Item
    {
        get { return _item.Entity; }
        set { _item.Entity = value; }
    } 

}

As you can see, there is nothing in those classes that give away the fact that they are used for data access. Except possibly for the EntityRef<T> and EntitySet<T> fields.

EntityRef<T> and EntitySet<T> are probably pretty obvious in their intention. EntityRef<T> keeps a reference to another entity, in this case it is the link between a Comment and the Item it is related to. And EntitySet<T> is the same thing, but for a one-to-many relation ship. In this case it is the link between the Item and its Comments.

Ok, so so far there is nothing really new. It is just 2 POCOs with a slightly odd field each.

The real soup comes from some attributes found in the System.Data.Linq.Mapping namespace in the System.Data.Linq assembly.

The attributes of interest in this case are the TableAttribute, the ColumnAttribute and the AssociationAttribute. There are a lot more of them in the namespace, but these are enough top get pretty far…

The table attribute is used to tell the system what table the class should be persisted in. If the optional property Name is left empty, the name of the Table will correspond to the name of the class. Otherwise it will obviously be named according to your decision.

The ColumnAttribute is of course used to define the name of the column that the value of the member should be stored in. On its own, without any properties set, it will put the value in a column with the same name as the member, with a data type inferred by the type of the member. But there are a wide variety of properties that can be set, that will define more granularly what will happen. For example you can use IsPrimaryKey to set it as primary key, CanBeNull to set nullability and Name to set the name of the column. You can also set the data type manually by defining the DbType property, and whether it is generated by the db by setting the IsDbGenerated.

As you can see, there are a lot of details that can be set on the ColumnAttribute, and you will see some of them in action in a short while. But most of them are self explanatory, so I won’t cover them in depth.

The last attribute, the AssociationAttribute, is used to define an association…doh… It is by far the most complicated attribute to get your head around, which doesn’t actually mean a lot considering how simple the attributes are…

It has a few properties that are interesting. The ThisKey property defines what member/column on this object that should be used as key. The OtherKey defines what member/column should be used as key at the other end. The Storage property, which is available as a property on ColumnAttribute as well, defines where the value should be stored if it shouldn’t be stored in the adorned property. This can be used to tell the system to populate a backing field instead of the property itself, making it possible to encapsulate access.

Ok, let’s move away from the theory and look at adding them to the entities.

[Table]
public class Item
{
    private int _id;
    private DateTime? _creationDate;
    private EntitySet<Comment> _comments = new EntitySet<Comment>();
 
    public void AddComment(Comment comment)
    {
        comment.Item = this;
        _comments.Add(comment);
    }
 
    [Column(Storage = "_id", IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true, CanBeNull = false)]
    public int Id { get { return _id; } }
 
    [Column(Storage = "_creationDate", AutoSync = AutoSync.OnInsert, DbType = "DateTime NOT NULL DEFAULT GetDate()", IsDbGenerated = true, CanBeNull = false)]
    public DateTime? CreationDate { get { return _creationDate; } }
 
    [Column]
    public string ThingToDo { get; set; }
 
    [Column]
    public bool Complete { get; set; }
 
    [Association(Storage = "_comments", OtherKey = "_itemId", ThisKey = "Id")]
    public IEnumerable<Comment> Comments
    {
        get { return _comments; }
    } 
}

As you can see from the snippet above, the Item entity will be stored in a table called Item as I have not specified a Name for the Table attribute.

The Id property is used a primary key. It gets set by the Db, and gets “synced” into the entity when inserted, which means it will get default(int) until it gets persisted at which time it will be set to the Db generated value. It is also set as non-nullable, and stored in the private member _id, making the property read-only.

The other interesting property to look at is the CreationDate. It too is auto generated by the Db, and set up as read-only. It however gets its value generated from the DbType declaration. As you can see, it declares the DbType as “DateTime NOT NULL DEFAULT GetDate()”, which is you know any form of SQL means that it is a DateTime column that cannot be null and that gets a default value generated by calling GetDate().

The final property worth looking at is the Comments property. It defines the association between to-do items and comments. The attribute basically says I want an association between this object and some other object using the column Id in this end, and _itemId at the other end. And I want you to store it in the _comments field. And by declaring the backing field as EntitySet<Comment> the system figures out that it is an association between Item and Comment.

I also decided to expose IEnumerble<Comment> instead of EntitySet<Comment>. This way I get a bit more control over what is happening as I force the user to use the AddComment() method to add comments.

Ok, what about the Comment class? Well, it is pretty much as simple

[Table]
public class Comment
{
    private int _id;
    private EntityRef<Item> _item;
    private DateTime? _date;
    [Column]
    private int _itemId; 
 
    [Column(Storage = "_id", IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true, CanBeNull = false)]
    public int Id { get { return _id; } }
 
    [Column(Storage = "_date", AutoSync = AutoSync.OnInsert, DbType = "DateTime NOT NULL DEFAULT GetDate()", IsDbGenerated = true, CanBeNull = false)]
    public DateTime? Date { get { return _date; } }
 
    [Column]
    public string Text { get; set; }
 
    [Association(Storage = "_item", OtherKey = "Id", ThisKey = "_itemId", IsForeignKey=true)]
    public Item Item
    {
        get { return _item.Entity; }
        set { _item.Entity = value; }
    } 
}

It looks very similar to the previous snippet. There are really only 2 interesting things in here. The first being the _itemId field. As you can see, I have actually defined it as a column…yes…you can tell the system that you want private fields to be stored in the database.

And the second is the AssociationAttribute. In this case, it says that I want to use the _itemId column (remember that from the previous point… ) as the key in this end, and the Id column of the other entity as the key at the other end. I also make sure to say that this is a foreign key.

Ok, so that wasn’t too complicated. I must admit that the AssociationAttribute is still a little bit of a mystery. It seems pretty simple, but it would be nice if there were some helpers around what properties to set when…

Now that I have the entities set up, I need a way to communicate with the database. This is done through a DataContext object, which is really easy to create. All I need to do is to create our my class that inherits from DataContext, and then declare what entities I want to work with. In this case it is as simple as this

class TodoDataContext : DataContext
{
    public TodoDataContext() : base("Data Source=isostore:/Todos.sdf")
    {
        if (!DatabaseExists())
            CreateDatabase();
    }

    public Table<Item> Items;
    public Table<Comment> Comments;
}

As you can see, I pass a connection string to the base class. This connection string defines that I want a database called Todos.sdf in isolated storage. However, as this wont be there on the first run, I make sure to check if it exists in the constructor. If it doesn’t, I make sure to create one by calling CreateDatabase().

The CreateDatabase() method will go off and create a new database and set up the schema based on the entities exposed as Table<T> on the current class.

You could also deploy your own database by adding it as a resource to your application and then copying it to the isolated storage on first run. That way you could pre-populate it with both schema and data.

Ok, now that I have a context to work with, I have decided to wrap it in a repository instead of passing around the context in my application. That way I get a simple API to work with, and predefined queries to run. I could also abstract the implementation away by adding an interface, thus making it easy to mock, but in this case I wont.

The repository basically just creates a new context in the constructor, and thus possible initialize a new DB. It then uses that context throughout its lifetime. It exposes its data by exposing methods wrapping predefined queries.

public class TodoRepository
{
    private TodoDataContext _ctx;
 
    public TodoRepository()
    {
        _ctx = new TodoDataContext();
    }
 
    public IEnumerable<Item> GetActiveTodoItems()
    {
        return (from i in _ctx.Items where i.Complete == false select i).ToList();
    }
    public Item GetTodoItemById(int id)
    {
        return (from i in _ctx.Items where i.Id == id select i).FirstOrDefault();
    }
    public Item AddNewItem(string thingToDo)
    {
        var item = new Item
                        {
                            ThingToDo = thingToDo
                        };
        _ctx.Items.InsertOnSubmit(item);
        _ctx.SubmitChanges();
        return item;
    }
}

That’s all there is to it! The only thing to make sure you handle correctly is the lifetime of the context, which can be a little bit interesting when handling updates and so on… More on the in a future blog post hopefully…

Just to make it possible to test the storage mechanism, I have built the application shown off at the beginning. it is all MVVM based and available in the download below. But being that it is a pretty stock standard application, which on top of it is ridiculously simple, I won’t go through it here. If you are interested, just get the code below!

That’s it! A quick and dirty intro to the database support on WP7…let me know if anything is missing, or might be wrong!

Code for download: DarksideCookie.WP7.DataAccess.zip (29.19 kb)

Cheers!