This is the fourth (and final) part in a series on using Sqlite across both Windows RT and Windows Phone. To catch up I suggest you take a look at parts 1, 2 and 3:
Windows (RT and Phone) and Sqlite (Part 1)
Windows (RT and Phone) and Sqlite (Part 2)
Windows (RT and Phone) and Sqlite (Part 3)
Just to recap, here is my list of goals:
– Blendable: My project has to be designable in Blend at all times!
– Sqlite: I want to use sqlite to store relational data across both Win8 and WP applications
– PCL: I want to be able to do all my data access from a portable class library
– Objects Not SQL: I want to be able to read and write objects, rather than write sql statements
In part 3 we got most of the way using sqlite-winrt by abstracting the platforms specific implementations into a set of interfaces which were exposed by a portable class library (PCL). This meant we could have all our application logic in a reusable class library (ie another PCL). We’re going to use a similar technique in this post to do the same thing with sqlite-net. This should solve the last remaining issue which was being able to query using objects, not sql.
In part 2 we discussed briefly how to get started with sqlite-net. Essentially for Windows Phone you needed an additional native-to-managed bridge, in addition to referencing the SQLite for Windows Phone visual studio extension. When you add the sqlite-net from nuget you’ll have noticed that it added two files, SQLite.cs and SQLiteAsync.cs, which contain the LINQ style wrapper which makes it possible to read and write objects. It’s our goal to define a set of interfaces which can be extracted into a PCL.
Interfaces
As we did in the previous part with sqlite-winrt, the first step is to create a separate SQLitePCL project and to define a set of interfaces which map to the classes/methods which is exposed by Sqlite-net. I’m not going to bore you with the details but you can see from following image just a couple of the interfaces which will map to classes such as the SQliteConnection, TableMapping and Column.
Platform Implementations
For each platform we need to implement these interfaces. This is really a matter of taking the sqlite-net classes, defined in SQLite.cs and SQliteAsync.cs and modifying them to implement the defined interfaces. This isn’t quite as simple as adjusting the class signature to include the appropriate interface but it isn’t far off.
We need to create a separate class library for each platform, eg SQLiteWinRT and SQLiteWP8. It doesn’t matter which platform you start with (I did the phone implementation first) since you’ll be referencing the same classes using the “add as link” technique discussed in the previous post. You might be thinking, if we’re simply going to be adding the same classes to both libraries why they can’t be all in the shared PCL. The answer lies in the conditional compilation statements at the top of the sqlite-net files – these determine how the classes are built for the respective platforms.
You’ll also need to add a class which will act as a connection factory:
public class SQLiteConnectionFactory : ISQLiteConnectionFactory
{
public ISQLiteConnection Create(string address)
{
return new SQLiteConnection(address);
}
}
Again, this class can be shared across both class libraries.
Application PCL
In the portable class library for your application you can now access Sqlite using a LINQ style interface (ie using objects, not sql). The only thing your PCL needs is a reference to an implementation of the ISQLiteConnectionFactory interface. In the following code for simplicity we’re doing this with a public property on the DataAccessClass but in a real application you might want to use a DI framework to inject this as required.
public class DataAccessClass
{
public ISQLiteConnectionFactory Factory { get; set; }
public void DoSomeStuff(string path)
{
using (var con = Factory.Create(path))
{
con.BeginTransaction();
con.CreateTable<Person>();
con.Insert(new Person { Name = "Nick", Age = 15 });
var people = con.Table<Person>().ToArray();
Debug.WriteLine(people.Length);
con.Commit();
}
}
public class Person
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
}
Assigning a Platform Implementation
The last thing you need to do, before you start accessing sqlite (eg calling the DoSomeStuff method) is to pass in the platform specific ISQLiteConnectionFactory implementation. As I mentioned you can do this using your favourite DI framework. In the following snippet I’m simply assigning it in the code behind of our sample application (not an ideal way to do it but super simple for demonstration of the concept!). The following code is from the MainPage.xaml.cs file in the Windows Phone application but the Windows RT code is exactly the same, just referencing the SQLiteWinRT namespace for the platform specific implementation.
public static string DB_PATH = Path.Combine(Path.Combine
(ApplicationData.Current.LocalFolder.Path, "sample.sqlite"));
protected override void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
var factory = new SQLiteConnectionFactory();
var dac = new DataAccessClass();
dac.Factory = factory;
dac.DoSomeStuff(DB_PATH);
}
And there you have it…. accessing data using objects from within your PCL. So let’s do another recap of our goals:
– Sqlite: Tick!
– PCL: Tick!
– Objects Not SQL: Tick!
– Blendable: Tick! (assuming you installed the update I mentioned in part 3).
I’ve included a sample project which includes all of the libraries and code discussed.