using SQLite inside portable class library

Question

recently we started to work on a new project which includes clients for Windows 8 Metro, Windows Phone and Desktop application. it was decided to use MVVM pattern as main Architecture because sharing ViewModels between projects is much more acceptable solution for us.

we decided to use portable class library for this purpose, but the problem is that after downloading and installing SQLite for windows runtime from Visualstudio 2012 extension gallery when we try to add reference to appropriate libraries we do not see those libraries at all. this makes us think, that it is not possible to use SQLite in Portable class library project.

Maybe some of u already done this and knows the way we could achieve that functionality? please provide us right way to develop this task as using SQLite and having reusable code is very important on this stage of the development

Solution 1

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

Solution 2

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

Solution 3

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

  }

Solution 4

After weeks of hunting things down, I finally figured you have to connect using:

var factory = new MvxWpfSqLiteConnectionFactory();

And I installed

using Cirrious.MvvmCross.Community.Plugins.Sqlite;
using Cirrious.MvvmCross.Community.Plugins.Sqlite.Wpf;
using Cirrious.CrossCore;

For all the MVVMcross geniuses out there and all of the Sqlite geniuses out there this might be obvious.

Nobody mentions this ANYWHERE and the examples for MVVMcross all won't compile or use Sqlite.NET which isn't MVVM nor PCL compatible when actually doing the connection.

Oh, and this works in Winforms, PCL, etc (I don't even use Wpf).

View more solutions

Comments

[Rati_Ge]

The approach is very close to what we thought to implement but thank u very much for providing those links as these will help as very much in our development process I think this is really perfect solution to our problem this is a separate question, but do u know if it is possible to use Task<T> inside PCL? I did not manage to make it work

[Stuart]

That's an excellent question - ask it as a new question and you'll get an answer!

[Daniel Plaisted]

@rati_ge If you are targeting platforms that already have Task<T>, then it should be available in your PCL. Probably you are targeting Windows Phone 7.5, which doesn't have Task support. However, we have released a package that supports Task and async/await on a variety of platforms. See this blog post: blogs.msdn.com/b/bclteam/archive/2012/10/22/…

[Rati_Ge]

@DanielPlaisted thank u for your answer we found out, that we where targeting Silverlight 4 and this was the problem after we changed our targeting options Task<T> is available now, but it is good to know there is solution for WP 7.5 as well

[Adrian Grigore]

I was going to use the same approach, but I don't seem to be able to install sqlite-net inside my PCL, which means that I can't use attributes like [PrimaryKey] to decorate my models. Having those attributes only inthe concrete implementations of my repositories does not help since the models should inside the PCL. How did you get around this problem?

[Stuart]

We split the pcl - give the n=10 video a watch at slodge.blogspot.co.uk/2013/05/…

[user2959284]

I still can't get "get { return _connection.Table<ListItem>(); }" with an Index, or Insert to work though. Can anyone help me with this?

[user2959284]

My bad, it works. You need more than one entry for Skip to work and Insert is fine.

[riverswb]

Link returns 404. I tried finding an equivalent at: opensource.microsoft.com/… But no luck