using SQLite inside portable class library

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; }

  }

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