One of the goals with my new Codemash App is reduce subsequent load time to almost nothing.  This means that aside from the first run, loading should be a near instantaneous act.  Obviously you cannot get away form some load time, but I wanted it to comparable to the act of grabbing your conference guide and flipping through pages.

Unfortunately, in the present release of Windows 8, not local database is supported for Metro apps.  I was disappointed by this, I figured SQL CE at least would be supported.  Whether this will change in the future I do not know.  With this in mind I went looking for an answer and found this post on Stack Overflow.  The guys at SQLite were kind enough to take it upon themselves to provide support for WinRT.

Installation

I used Tim Heuer’s blog post to understand things.  His video is somewhat dated however, as the SQLite team has in fact released something which can be included in Visual Studio to support SQLite.  This extension can be downloaded here.  Important to understand this is a visx file, meaning it will include the DLL on its own, you just need to list as a dependent extension.  See below:

After installation:
Tools –> Extensions and Updates…

All this means is you CAN reference it, not that it is referenced.  To actually reference it, do the typical Add Reference.  Looks under Windows –> Extensions.

Make sure you check the SQLite AND C++.  FYI, I know that most people use Any CPU as their compilation mode.  For a component like this, you’ll have to pick one, though it might support both.  I dont have an ARM device to test on.

At this point the SQLite libraries are a part of your project output, but you really dont want to be interfacing natively with the C++ library.  Thankfully, there is a nice LINQ based wrapper you can get from NuGet.

Install-Package sqlite-net (alternatively, you can do a search for it through the Manage Nuget Packages –> Online interface)

Run that command pointing at the correct project.  Once installed, our next steps to actually talk to SQLite will be easy.

Creating a Database

So SQLite is filed based, meaning its not your typical RDBMS system you are familiar with if you are coming from MySQL or SQL Server.  Its designed to be extremely lightweight, which makes it perfect for WinRT.  The concept of a “table” is not the same as what you would expect from the afore mentioned DB products.  Keep this in mind.

Lets “connect”

So, its not really connecting, you are opening a file.  So we have to dictate where we want the file stored.  Tim Heuer recommends storing it in the local ApplicationData folder, so that is what I am doing as well:

string applicationPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
_databasePath = Path.Combine(applicationPath, DATABASE_NAME);

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

We use this path whenever we establish the connection, like this:

using (var db = new SQLiteConnection(_databasePath))
{
       var entities = db.Table().ToList();
 }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This code opens the connection and does a select all against the Session “table”.  Remember, its not really a table as we understand it from RDBMS world.

Talkin’ bout saving data

Once you have established the connection, there are a variety of methods to support the CRUD operations.  One tip: define your PrimaryKey (look in the SQLite.cs file created) as shown below:

public class Session : EntityBase
{
    [PrimaryKey]
    public int SessionId { get; internal set; }

    public string Title { get; internal set; }
    public string Abstract { get; internal set; }
    public DateTime Starts { get; internal set; }
    public DateTime Ends { get; internal set; }
    public string Level { get; internal set; }
    public string Room { get; internal set; }
    public int SpeakerId { get; internal set; }
    public string Track { get; internal set; }
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

I would question anyone who has a complicated model being stored locally.  Traditionally, you want to keep these structures simple so they are easier to sync.  The idea of storing a complex database on the local system seems counterintuitive to me.  SQLite is not designed to support such an approach.  The amount of data being kept here is minimal and the operations simple, intentionally.

Closing Thoughts

I had worried that creating this feature would present various challenges, however I was wrong.  Thanks to Tim Heuer’s tutorial I was able to quickly get this up and running.  Quite honestly the tricky part was getting the code just right using the new async and await features so that I could do something of a thread join (though we aren’t using threads).  Once I got this to work just right the application worked as I expected, though I am still using DevLink data.  I hope to be using Codemash data starting sometime next week.