Search code examples
sqlitexamarinxamarin.formssqlite-netsqlite-net-extensions

SQLite-Net Extensions Example on Xamarin Forms

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

  • 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: