SQLite for WinRT

Developing applications for Windows Phone was made easy because of the SQL Server CE database build in the operating system. The SQL Server CE database operated with relational single file databases, build runtime inside the application isolated storage.

SQL Server CE is not build into Windows 8 or Windows 8 RT. Building Metro Style applications can’t use SQL Server CE because it have to be installed separately from application. The solution for this need it a database engine named SQLite:

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform – you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen().

– Source: http://www.sqlite.org/about.html

Tim Hauer made a really helpful post about how to compile SQLite for Windows 8 Runtime. Using Visual Studio, ActiveTcl, updated version of gawk.exe and Fossil required for source building will consume time and according to the comments under posts it may lead to a lot of exceptions.

So I searched for a ready to use version and I found a github package made by Frank A. Krueger  – containing a downloadable package with SQLite for WinRT.

Now developing a metro style application for Windows 8 using a SQLite database is only two simple steps:

1. Add Existing Item to your project – select sqlite3.dll from the SQLite-net metro build (and change “Copy To Output Directory” to “Copy Always”)

2. Search and install “SQLite-net” from Library Package Manager \ Manage NuGet Packages

 

From this point forward source is different for each project, but bellow I copied a simple example:

A simple class for describing the database

    public class Photo
    {
        [AutoIncrement, PrimaryKey]
        public int ID { get; set; }
        public string Name { get; set; }
        public string DownloadUrl { get; set; }
        public string ThumbnailUrl { get; set; }
    }

 

And a build method (in this case creating some face data)

public MainPage()
{
    this.InitializeComponent();
    string dbRootPath = 
         Windows.Storage.ApplicationData.Current.LocalFolder.Path;
    using (SQLiteConnection db = 
          new SQLiteConnection(Path.Combine(dbRootPath, "workPhotos.sqlite")))
    {
        db.CreateTable<Photo>();

        db.RunInTransaction(() =>
        {
            for (int i = 0; i < 10; i++)
            {
                db.Insert(new Photo() { Name = "Image " + i.ToString() });
            }
        });
    }
}

 

For more detailed info about insert, update, delete and execute check the complete Wiki documentation.