Windows Phone 8 Using SQLite

After using a flat file to persists my local data within my WorkTime app and sync them to SkyDrive I asked myself: Why not using a local database to store the data?

So what the database systems that will be supported by Windows Phone 8:

(1) With Windows Phone 7.5 (Mango) Microsoft has introduced an old technology with a new database access layer. SQL CE as database engine and LINQ to SQL to manipulate and define tables using classes.

(2) At the Build Microsoft introduces a new solution for Windows Phone 8: SQLite. That the right decision, because with SQLite you have the chance to share your local application data across several platforms Windows Phone 8, Windows 8, Android and iOS. Microsoft has worked with the SQLite team to provide an easy way to add SQLite to our Windows 8 and Windows Phone 8 projects. The result are two extensions in the  Visual Studio Gallery. One to support Windows 8 projects and an other one to use with Windows Phone 8.

Writing native code for Windows Phone 8?

But what his? These extensions are only the SQLite database engine and written in native code.To use them you have to write native code to interact with the SQLite database engine and perform operations with the database. So what to do: Searching a library which supports a wrapper that you can use C# to use the SQLite database engine.

Peter Torr – who introduces SQLite at the Build - actual posts about the current development process for such an wrapper from Microsoft: http://blogs.msdn.com/b/ptorr/archive/2012/11/27/update-on-sample-code.aspx – so we have to wait for the demoed solution from Microsoft.

So Microsoft gives us a new SQLite database engine but unfortunately no wrapper library to access this by C#.

What’s about csharp-sqlite?

csharp-sqlite is published on Google Code that allows to use the native SQLite engine directly in a C# application. I have worked with this library already on Windows Phone 7 and the library works fine with Windows Phone 8 and can be used as it is.

But if you want to use the same data access layer within Windows Phone 8 and Windows 8 (which should be the main goal for developing apps for these platforms)? The solution: Usage of a additional library sqlite-net which could be found and installed via Nuget:

<?xml version="1.0" encoding="utf-8"?>

<packages>

  <package id="sqlite-net" version="1.0.5" targetFramework="wp80" />

</packages>

Sqlite-net works for my Windows 8 and my Windows Phone 8 test projects and capsulated the data access in a very nice way.

The data class:

public class Contact

    {

        [PrimaryKey]

        public string UnqiqueId { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

    }

The CRUD methods:

public async Task CreateContactAsync(Contact contact)

{

    var conn = GetConnection();

    await conn.InsertAsync(contact);

}

public async Task UpdateContactAsync(Contact contact)

{

    var conn = GetConnection();

    var existingContact = await conn.FindAsync<Contact>(c => c.UnqiqueId == contact.UnqiqueId);

    if (existingContact != null)

    {

        await conn.UpdateAsync(contact);

    }

}

public async Task DeleteContactAsync(Contact contact)

{

    var conn = GetConnection();

    await conn.DeleteAsync(contact);

}

private SQLiteAsyncConnection GetConnection()

{

    return new SQLiteAsyncConnection("contacts");

}

Some helper methods:

private async void CreateTableAsync<T>() where T : new()

{

    var conn = GetConnection();

    await conn.CreateTableAsync<T>();

}

private bool ExistsTable<T>()

{

    var connStr = new SQLiteConnectionString("contacts", false);

    using (var conn = new SQLiteConnection(connStr.DatabasePath))

    {

        var command = new SQLiteCommand(conn)

        {

            CommandText =

                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" +

                typeof(T).Name + "'"

        };

        return (command.ExecuteScalar<int>() > 0);

    }

}

 

So in case you are developing a Windows Phone application and you need to use a database, which are the possible solutions?

 

What database solutions for Windows Phone 8?

The first one I mentioned above: still keep using the old SQL CE which works just fine in Windows Phone 8. But then your database could not be used by other apps from different platforms.

A alternative approach is to use SQLIte and the csharp-sqlite library. Together with sqlite-net you can implement the same data access layer for Windows 8 and Windows Phone 8 by changing the underlying SQLite database engine for both platforms.