Porting a Windows Phone 8.0 Silverlight app to Windows Phone 8.1 Universal app - Database / SQLite

Two weeks ago I wrote about the preparation of porting my P | Cast app to Windows Phone 8.1 Universal.

After this I started investigating if I would be able to port my database including the data access code.

Prepare the Windows Phone project

So of course we need to have the SQLite database engine for Windows Phone 8.1. So download this Visual Studio Extensions: SQLite for Windows Phone 8.1. Now it’s time to add a reference to the SQLite engine on the Windows Phone project. It will also add a reference to the Microsoft.VCLibs.

While in the past a special C++ project would have to be added to your Windows Phone 8.0 solution, this is much easier.

image

Prepare the Windows Project

While I’m still not sure if the Windows version of P | Cast will ever see the light, I want to know if my code is compatible. So I downloaded the Visual Studio Extension: SQLite for Windows Runtime (windows 8.1). So this time add a reference to the SQLite engine on the Windows project.

image

Time for some Data Access

As always I try to decouple the data access from the UI. It’s a best practice I always try to follow. Nowadays we have Portable Class Libraries so I add a new PCL project to the solution.

image

I reference this project from both the Windows Phone and the Windows project.

Next I add the same NuGet package I’m used to use in the P | Cast app, called sqlite-net.

Everything compiles, time to migrate code. First I took the code from a table definition. Actually I copied all of my table definition classes, but for this article I’ll show the Subscription definition.

[Table("Subscriptions")]
public class Subscription
{
    [AutoIncrement, PrimaryKey]
    public int Id { get; set; }

    [Unique]
    public string Uri { get; set; }

    public string Title { get; set; }
    public string Description { get; set; }
    public string Copyright { get; set; }
    public string ArtUri { get; set; }

    public string ExternalId { get; set; }
    [Indexed]
    public DateTime LastUpdated { get; set; }
}

 

So I tried to do actually create the database, insert data and query the data. I called the below method from the OnLaunched.

private async void TryDatabaseStuff()
{
    var connection = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "podcasts.db"));
    await connection.CreateTablesAsync<Subscription, Track>();

    await connection.InsertAsync(new Subscription { Uri = "http://hanselminutes.com/subscribe" });
    Debug.Assert(await connection.Table<Subscription>().CountAsync() > 0);
}

 

So to assure you, all this code works like a charm on both Windows Phone and Windows. Now it’s time to migrate some more parts.