Using a simple ORM with sqlite on WP7

I come from a background of using ORMs to do data access when developing for the full version of .Net. I'm very use to leveraging the power of these tools to do what I see as an infrastructure concern - repetitive and time consuming code to get data in and out of a persistence store. Now we are writing apps for WP7 and some require the use of databases, specifically sqlite. This required me to return to writing a DAL for several of the applications Rich and I are building.

Obviously the support for a full blown ORM on a phone device probably can't be done and why would you want too? I believe most apps requiring a database are going to have a read only nature (or mostly read with few writes) and have a simple relational model with few tables & constraints. Personally I wouldn't use a database to store a score table or user setting for a game\app, I'd more likely use isolated storage with a serialized object structure.

Rich & I've been porting an iPhone app recently, it makes use of a sqlite database. This is well supported on the iPhone platform and fortunately for us sqlite is also supported for WP7, see here. We decided to see if there was an support on the platform for a simple ORM tool (also known as ORM lite) to help simplify any DAL code we had to write. If you're wondering what an ORM lite is check out Dapper.Net or Massive written by Rob Corney of SubSonic fame.

Unfortunately most of these tools are not going to be used on the platform - they require the use of system.data namespace. But there is one which does support sqlite on WP7 - Vici Project. They have a simple ORM library called CoolStorage. This library is written on top of the sqlite support for WP7. Shown below is how easy it can be to access data in your database along with a screenshot of the database schema. This code reads all the postcodes from a test database into a POCO array:

private IEnumerable GetAllPostCodes()
{
    CSConfig.SetDB(databaseFile, SqliteOption.None);
    return CSDatabase.RunQuery(viciCoolStorageSql);
}

As you can see I can do the data access in 2 lines of code :) I definitely like the approach and it gives me the ability to populate the model classes with ease. The model here is the PostCode class it is no more than a bindable POCO class with 4 settable properties.

Now I'm to old to expect to get anything for nothing, those 2 lines of code surely must have an associated cost. After all this library is built on top of the WP7 implementation for sqlite database access. The next step was to do some simple tests to compare performance.

We decided to CoolStorage to writing the DAL code explicitly. The first test was to get all the post codes:

CoolStorage
SQL -'SELECT postCode AS Value, district AS PostDistrict, latitude AS Latitude, longitude AS Longitude FROM PostCodes'

private void CoolStorageExecute()
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();
                
    CSConfig.SetDB(databaseFile, SqliteOption.None);
    var results = CSDatabase.RunQuery(viciCoolStorageSql);

    stopwatch.Stop();

    Debug.WriteLine("Vici CoolStorage duration: {0} ms, results count - {1}", stopwatch.ElapsedMilliseconds, results.Length);
}

Explicit DAL Code - I was a bit rusty writing this :)
SQL - 'SELECT postCode, district, latitude, longitude FROM PostCodes'

private void ExplicitDalExecute()
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();

    var results = new List<PostCode>();
    using (var conn = new SqliteConnection("Version=3,uri=file:" + databaseFile))
    {
        conn.Open();

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = csharpSqliteSql;
            using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
            {
                while (reader.Read())
                {
                    var postCode = new PostCode
                                        {
                                            Value = reader.GetString(0),
                                            District = reader.GetString(1),
                                            Latitude = reader.GetDouble(2),
                                            Longitude = reader.GetDouble(3),
                                        };

                    results.Add(postCode);
                }
            }

            conn.Close();
        }
    }

    stopwatch.Stop();

    Debug.WriteLine("Explicit CSharp Sqlite duration: {0} ms, results count - {1}", stopwatch.ElapsedMilliseconds, results.Count);
}

I used a simple WP7 app to run this code and produce the following results, what you can see is the explicit DAL is faster than CoolStorage, by an approximate factor of 1.5 to return a recordset of 2821 rows.

CoolStorage  average = 322 ms
Explicit DAL average = 253 ms

I negated the first debug output as this includes JIT'ing time for any session classes.

I'm not surprised by the results, as I said you don't get anything for free. What is surprising is my thoughts about moving away from using CoolStorage. Normally I would do anything to improve app performance but right now we aren't in any hurry to swap out the code, why?

We are currently in a UAT phase for the app and unless the performance of the search is flagged up as an issue it won't be changed. It would delay the release and for what, this isn't a trading system...