Mittwoch, 19. Dezember 2012

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.

Kommentare:

  1. Nice one and surely useful for many others, thanks!

    ~ Matthias

    AntwortenLöschen
  2. Thank you Matthias for all the tips using SQLite and the nice ExistsTable method :-)

    AntwortenLöschen
  3. Nice, I've been hoping to find an example using SQLite and code first Entity Framework on the Windows Phone 8... have you had any luck there?

    --Tyler
    http://bleedingedgemachine.blogspot.com/

    AntwortenLöschen
    Antworten
    1. Sorry Tyler, have not tried this combination.

      Löschen
    2. @Tyler: There is no SQLite provider for the Entity Framework, thus you can't use it there (unless you're implementing your own ;-)).

      ~Matthias

      Löschen
  4. Hi,

    Your solution is nice but, I could fine some limitations when i tried to have a property of type List.

    It appears that the attribute on the model properties are ignored.

    For example, I added an IgnoreAttribute (based on the same model that the PrimaryKeyAttribute) and it was not ignored (an exception was thrown).

    If model is moved in DataAccess project, it is working Well ... :-(

    AntwortenLöschen
    Antworten
    1. Hi, have you already trie to move the Attributes from the original SQLite Files to your Model Assembly und uncomment the original Attribute Definition? The should solve your Problem.

      Löschen
  5. Does the windows phone have memory upgrade issue as with Android phone memory upgrade

    AntwortenLöschen
  6. A great quote for the coming new year. Let’s start thinking more of others and less of ourselves. Nice post.

    AntwortenLöschen
  7. Windows Phone Development
    may be the latest smart phone platform, but it has already taken significant market share and poses a big aggressive threat to iPhone, Android, and BlackBerry.

    AntwortenLöschen
  8. Patric Schouler

    Here is a database compatible with Windows Phone 8:
    https://www.kellermansoftware.com/p-49-ninja-winrt-database.aspx

    AntwortenLöschen