SQLite-Net Extensions Example on Xamarin Forms

Question

I am intersted in using SQlite-Net Extensions (https://bitbucket.org/twincoders/sqlite-net-extensions) with Xamarin Forms. I am using Visual Studio 2013, SQlite.Net PCL 2.3.0 from NuGet, and a blank Hello World Xamarin Forms PLC project. As a first step I am trying to just get the example from the Sqlite.Net extensions site working. As per the suggested method on the Xamarin website I am using DependencyService to get the SQLIteConnection. However, my code will not even compile since it cannot find UpdateWithChildren nor GetWithChildren methods on the SQLiteConnection. It seems obvious that my SQLiteConnection object does not have all the same things as the example. Am I using the wrong library for SQLite.Net PCL? Both Xamarin and SQLite-Net Extensions suggested using the PCL version from NuGet, which is what I think I have...

I have this posted on the Xamarin Forums as well here: http://forums.xamarin.com/discussion/20117/sqlite-net-extensions-and-sqliteconnection#latest

Here is my code (except for the ISQLite class). Data Models:

using SQLiteNetExtensions.Attributes;
using SQLite.Net.Attributes;


namespace Sample.Models
{
    public class Stock
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        [MaxLength(8)]
        public string Symbol { get; set; }

        [OneToMany]      // One to many relationship with Valuation
        public List<Valuation> Valuations { get; set; }
    }

    public class Valuation
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        [ForeignKey(typeof(Stock))]     // Specify the foreign key
        public int StockId { get; set; }
        public DateTime Time { get; set; }
        public decimal Price { get; set; }

        [ManyToOne]      // Many to one relationship with Stock
        public Stock Stock { get; set; }
    }
}

And here is my DatabaseHelper. Right now I'm just running a test right in the constructor. The error I get is that the methods UpdateWithChildren and GetWithChildren cannot be found.

using Sample.Models;
using SQLite.Net;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xamarin.Forms;

namespace Sample.Orm
{
    class DatabaseHelper
    {
        private SQLiteConnection db;

        public DatabaseHelper()
        {
            db = DependencyService.Get<ISQLite>().GetConnection();
            //Create the tables

            db.CreateTable<Stock>();
            db.CreateTable<Valuation>();

            var euro = new Stock()
            {
                Symbol = "€"
            };
            db.Insert(euro);   // Insert the object in the database

            var valuation = new Valuation()
            {
                Price = 15,
                Time = DateTime.Now,
            };
            db.Insert(valuation);   // Insert the object in the database

            // Objects created, let's stablish the relationship
            euro.Valuations = new List<Valuation> { valuation };

            db.UpdateWithChildren(euro);   // Update the changes into the database
            if (valuation.Stock == euro)
            {
                Debug.WriteLine("Inverse relationship already set, yay!");
            }

            // Get the object and the relationships
            var storedValuation = db.GetWithChildren<Valuation>(valuation.Id);
            if (euro.Symbol.Equals(storedValuation.Stock.Symbol))
            {
                Debug.WriteLine("Object and relationships loaded correctly!");
            }
        }
    }
}

Solution 1

SQLite-Net Extensions is now available as NuGet packages for MvvmCross and standard PCL flavors of SQLite-Net. This should fix this kind of issues where the library was compiled with one SQLite-Net version but executed with another, that could cause the kind of issues that you are describing.

The links:

• For SQLite-Net PCL: https://www.nuget.org/packages/SQLiteNetExtensions/
• For MvvmCross SQLite Community plugin: https://www.nuget.org/packages/SQLiteNetExtensions-MvvmCross/

Solution 2

Jon Goldberger from Xamarin support pointed me in the right direction. For those finding this post in the future, the solution in short, is that unless you are using MvvmCross you need to build SQLite-Net Extensions from source, do not use the precompiled dll. Once you pull from their git server, you need to add the SQLiteNetExtensions-PCL.csproj, restore it's packages with Project->Restore Packages and then reference the SQLiteNetExtensions-PCL project from your base project.

Comments

[thedigitalsean]

I updated to using the Nuget version. This is right answer now.