using SQLite inside portable class library

In MvvmCross, we tackled this via a different approach.

We wanted to take advantage of the native ports of SQLite and we wanted to use the SQLite-net ORM wrapper from https://github.com/praeclarum/sqlite-net/

So instead of using just a PCL, what we did was to:

• build a core PCL containing a set of cross platform SQLite-net interfaces and base classes https://github.com/slodge/MvvmCross/tree/vnext/Cirrious/Plugins/Sqlite/Cirrious.MvvmCross.Plugins.Sqlite

• build an extension/realisation of that plugin DLL for each platform

  • e.g. for MonoDroid we have https://github.com/slodge/MvvmCross/tree/vnext/Cirrious/Plugins/Sqlite/Cirrious.MvvmCross.Plugins.Sqlite.Droid
  • e.g. for WinRT we have https://github.com/slodge/MvvmCross/tree/vnext/Cirrious/Plugins/Sqlite/Cirrious.MvvmCross.Plugins.Sqlite.WinRT
    
    

• use a common DI pattern and library so that both PCL and non-PCL database clients know how to load and instantiate these plugins.

  • e.g. you can see some of these in the SimpleDroidSql sample in https://github.com/slodge/MvvmCross/tree/vnext/Sample%20-%20SimpleDialogBinding

At a code level, client apps can use the plugin like:

In a business logic library (PCL or platform specific) the code can define a model object:

public class ListItem{    [PrimaryKey, AutoIncrement]    public int Id { get; set; }    public string Name { get; set; }    public string WhenCreated { get; set; }}

during startup the app can call:

  Cirrious.MvvmCross.Plugins.Sqlite.PluginLoader.Instance.EnsureLoaded();  var factory = this.GetService<ISQLiteConnectionFactory>();  var connection = factory.Create("SimpleList");  connection.CreateTable<ListItem>();

then during operation, the code can do things like:

  connection.Insert(new ListItem() { Name = TextToAdd, WhenCreated = DateTime.Now.ToString("HH:mm:ss ddd MMM yyyy") });

or

 public ListItem this[int index] {     get { return _connection.Table<ListItem>().OrderBy(_sortOrder).Skip(index).FirstOrDefault(); } }

While the UI specific code has to reference the platform-specific extension of the plugin and to inject that platform specific implementation into the IoC/DI system. On Droid this really is simple (because MonoDroid supports Assembly.Load at runtime), but on other platforms, this involves a little bit of 'boiler-plate' code like:

    protected override void AddPluginsLoaders(Cirrious.MvvmCross.Platform.MvxLoaderPluginRegistry loaders)    {        loaders.AddConventionalPlugin<Cirrious.MvvmCross.Plugins.Sqlite.WinRT.Plugin>();        base.AddPluginsLoaders(loaders);    }

Notes:

• the current MvvmCross repo only includes the WinRT and MonoDroid SQLite wrappers - but others (WP* and MonoTouch) should be easy to build (and I know others have built them, but not yet contributed them back)

• the current MvvmCross repo only includes the sync (not async) interfaces for WinRT - but again I know people have told me that they have extended this in their private projects.

• I'm currently in the process of pulling this plugin structure outside of MvvmCross so that the plugins can be used more widely. Hopefully expect an announcement on this before Xmas.

• For more on plugins in MvvmCross see https://speakerdeck.com/cirrious/mvvmcross-going-portable

Stuart make an excellent explanation of how to create PCL with SQLite, but today I found this approach and I hope it can resolve much better any other issue

New open source Portable Class Library for SQLite

Probably worth mentioning that the AutoIncrement and PrimaryKey attributes are from the following namespace

using Cirrious.MvvmCross.Plugins.Sqlite;

  public class Reference : IBusinessEntity    {      public string Key { get; set; }      public string Value { get; set; }     [PrimaryKey, AutoIncrement]      public long Id { get; set; }  }