This is the third 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 and 2:
Windows (RT and Phone) and Sqlite (Part 1)
Windows (RT and Phone) and Sqlite (Part 2)
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
So far the PCL support has been non-existent. In this post we’re going to look at adding support so that you can access data from within your portable class library. When I thought to do this I figured it’d be an easy job of creating a bunch of interfaces and a factory, or two. It wasn’t too hard but there was a bit more to it than I expected. We’ll start with using the sqlite-winrt library, and I suspect it might be in part 4 of this series when I return to sqlite-net.
The primary problem we have is that our PCL can’t access any libraries that are winrt or wp specific. Since Sqlite-winrt is essentially two libraries, one for winrt and one for wp, this means we can’t access them directly from within our PCL. However, if we were to have an interface, declared in a PCL, that is implemented by each platform, then we can simply pass the implementation into our PCL and it’s none the wiser (standard DI stuff really). This means step one has to be to provide a PCL interface and a subsequent implementation for each platform:
Interfaces
The interfaces were relatively easy to define, considering you can open a .winmd in tools like ILSpy and you’ll be able to see the internal interfaces declared in sqlite-winrt. I’ve only included the interfaces for IDatabaseFactory and IDatabase here. The rest are included in the attached source code at the end of this post. These interfaces need to be declared in a stand alone portable class library (they could be in your application pcl but that would remove any possible reuse between projects)
public interface IDatabaseFactory
{
Task<IDatabase> CreateInstance(string file);
Task<IDatabase> CreateInstance(string folder, string name);
}
public interface IDatabase:IDisposable
{
string Path { get; }
Task OpenAsync();
Task OpenAsync(SqliteOpenMode openMode);
Task<IStatement> PrepareStatementAsync(string cmd);
Task ExecuteStatementAsync(string cmd);
long GetLastInsertedRowId();
}
Proxy Implementations
For each platform we need to implement these interfaces. Essentially the implementation is just going to proxy calls through to the sqlite-winrt library for the respective platform. For example, here is the implementation of the IDatabaseFactory interface.
public class DatabaseFactory : IDatabaseFactory
{
public async Task<IDatabase> CreateInstance(string file)
{
var proxy = new DatabaseProxy();
var sf = await StorageFile.GetFileFromPathAsync(file);
proxy.Instance = new Database(sf);
return proxy;
}
public async Task<IDatabase> CreateInstance(string folder,
string name)
{
var proxy = new DatabaseProxy();
var sfolder = await StorageFolder.GetFolderFromPathAsync(folder);
var sf = await sfolder.CreateFileAsync(name,
CreationCollisionOption.OpenIfExists);
proxy.Instance = new Database(sf);
return proxy;
}
}
You might be thinking “really, I need to implement it twice?” Well the good news is that you only need to write the code once. sqlite-winrt has the same set of apis for both Windows 8 and Windows Phone, so whilst you do indeed need to create a class library for each platform, you can actually share the code between the projects (simply add the code file as a linked file using “Add as Link” in Visual Studio).
Application PCL
In the portable class library for your application you can now access Sqlite. The only thing your PCL needs is a reference to an implementation of the IDatabaseFactory 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 IDatabaseFactory Factory { get; set; }
public async Task<string> DoSomeDatabaseStuff(string dbPath)
{
// Create a new SQLite instance for the file
using (var db = await Factory.CreateInstance(dbPath))
{
// Open the database asynchronously
await db.OpenAsync(SqliteOpenMode.OpenReadWrite);
try
{
await db.ExecuteStatementAsync
("CREATE TABLE Cities (rowid INTEGER PRIMARY KEY ASC, CityName TEXT);");
await db.ExecuteStatementAsync
("INSERT INTO Cities (rowid, CityName) VALUES (1, 'Perth');");
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
// Table exists....
// TODO: Improve this so we're not throwing an exception
// for a common case!
}
// Prepare a SQL statement to be executed
var statement = await db.PrepareStatementAsync
("SELECT rowid, CityName FROM Cities;");
// Loop through all the results and add to the collection
if(await statement.StepAsync())
{
return statement.GetIntAt(0) + ": " + statement.GetTextAt(1);
}
return null;
}
}
}
Assigning a Platform Implementation
The last thing you need to do, before you start accessing sqlite (eg calling the DoSomeDatabaseStuff method) is to pass in the platform specific IDatabaseFactory 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
protected override async void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
await CreateCitiesDatabase();
}
private async Task CreateCitiesDatabase()
{
// Get the file from the install location
var file =
await
ApplicationData.Current.LocalFolder.CreateFileAsync
("cities.db", CreationCollisionOption.OpenIfExists);
var dal = new DataAccessClass();
var factory = new DatabaseFactory();
dal.Factory = factory;
var data = await dal.DoSomeDatabaseStuff(file.Path);
Debug.WriteLine(data);
}
And there you have it…. accessing code from within your PCL. So let’s do another recap of our goals:
– Sqlite: Tick!
– PCL: Tick! We fixed this
– Objects Not SQL: Fail (still but this solution will suit some people)
– Blendable: BAM! Fail. Doh! my solution is no longer Blendable.
Unfortunately we’ve broken our ability to design our application in Blend. This is hit and miss, if you get to this point and everything is still Blendable then you’re luckier than me. The good news is that there is a fix and it has nothing to do with us writing more code, or changing our existing code.
FIX: Install Visual Studio Update 3 Note – it is currently at RC, so whilst it is probably quite stable and safe to install, you do so at your own risk. If you’re risk adverse, you may want to wait until it RTMs.
With that fix in place, the only thing let is using Objects Not SQL. More on that in the next post.