Use SQLiteNetextensions with Xamarin for Android-App

Question

I've read some threads about SQLite for Xamarin but I'm still not able to set up SQLiteNetExtensions in a proper way. I'm currently developing an android app with Target Framework Android 7.1(API Level 25 - Nougat).

Can anybody tell me what I'm doing wrong?

1. I installed nuget packages:

   Install-Package SQLiteNetExtensions -Version 2.0.0-alpha2 -Pre

   Install-Package SQLite.Net-PCL -Version 3.1.1

   According to: https://bitbucket.org/twincoders/sqlite-net-extensions

2. Then I set up my code.

   using SQLite.Net.Attributes;
   using SQLiteNetExtensions.Attributes;
   using System;
   
   namespace AppName.Resources.Model
   {
     public class Entry
     {
       [PrimaryKey, AutoIncrement]
       public int Id { get; set; }
       [ForeignKey(typeof(Stock))]
       public int StockId { get; set; }
       public DateTime Date { get; set; }
       public double Amount { get; set; }
     }
   }
   

   
   

   using SQLite.Net.Attributes;
   using SQLiteNetExtensions.Attributes;
   using System;
   using System.Collections.Generic;
   
   namespace AppName.Resources.Model
   {
     public class Stock
     {
       [PrimaryKey, AutoIncrement]
       public int Id { get; set; }
       public string Name { get; set; }
       public DateTime LastUpdated { get; set; }
       [OneToMany(CascadeOperations = CascadeOperation.All)]
       public List<Entry> Entrys { get; set; }
     }
   }
   

   
   

   using Android.Util;
   using AppName.Resources.Model;
   using SQLite.Net;
   using SQLite.Net.Platform.XamarinAndroid;
   using SQLiteNetExtensions.Extensions;
   using System.Collections.Generic;
   using System.IO;
   
   namespace AppName.Resources.DataHelper
   {
     public class DataBase
     {
       string folder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
       public bool CreateDataBase()
       {
         try
         {
           using (var stocksDBConnection = new SQLiteConnection(new SQLitePlatformAndroid(), Path.Combine(folder, "Stock.db")))
           {
             stocksDBConnection.CreateTable<Entry>();
             stocksDBConnection.CreateTable<Stock>();    
             return true;
           }
         }
         catch (SQLiteException ex)
         {
           Log.Info("SQLiteEx", ex.Message);
           return false;
         }
       }
   
       public bool InsertIntoTableStock(object stock)
       {
         try
         {
           using (var stocksDBConnection = new SQLiteConnection(new SQLitePlatformAndroid(), Path.Combine(folder, "Stock.db")))
           {
             stocksDBConnection.InsertWithChildren(stock);
             return true;
           }
         }
         catch (SQLiteException ex)
         {
           Log.Info("SQLiteEx", ex.Message);
           return false;
         }
       }
       ...
   

3. These References were added by nuget:

   SQLite-net

   SQLite.Net

   SQLite.Net.Platform.XamarinAndroid

   SQLiteNetExtensions

   SQLitePCLRaw.Batteries_green

   SQLitePCLRaw.Core

   SQLitePCLRaw.lib.e_sqlite3

   SQLitePCLRaw.provider.e_sqlite3

4. Occuring error:

   'SQLiteConnection' does not contain a definition for 'InsertWithChildren' and the best extension method overload 'WriteOperations.InsertWithChildren(SQLiteConnection, object, bool)' requires a receiver of type 'SQLiteConnection'

   'SQLiteConnection' does not contain a definition for 'GetAllWithChildren' and the best extension method overload 'ReadOperations.GetAllWithChildren(SQLiteConnection, Expression>, bool)' requires a receiver of type 'SQLiteConnection'

Well that's how far I get. Anybody out there who knows what to do? Maybe remove conflicting references?

Answer 1

Ok I'm sure that its just a version problem because I just tried your code and it works fine for me. Try installing SQliteNetExtensions v 1.3.0 package from the NuGet package manager window in Visual Studio and nothing else. It will install all its dependencies.

Here is what I did - Try to do the same and see if this helps :

1. I Created a new Android Single View App Project in Visual Studio (I'm using community 2017 - but it should not matter ) and created
   the Stock , Entry and Database Classes and pasted your given
   code respectively.
2. I installed only the SQliteNetExtensions v 1.3.0 package from the NuGet Package Manager It installed the following dependencies along with it, I didn't installed these myself.
   SQLite.Net-PCL v3.0.5
NewtonSoft.Json v6.0.8

Here is what I got after installing just SQliteNetExtensions v 1.3.0 package.

As you can see its showing an update for SQLite.Net-PCL v3.0.5 to v3.1.1 I tired also with the updated one and it still works fine , so its up to you to update or not.

Here a bit proof that its working fine. I also compiled and run the app on emulator and it works all fine.

Answer 2

After some research I found out that:

Install-Package SQLiteNetExtensions -Version 2.0.0-alpha2 -Pre

depends on

Install-Package sqlite-net-pcl

instead of

Install-Package SQLite.Net-PCL -Version 3.1.1

But after removing all references, cleaning the solution and reinstalling the two needed packages there is no SQLite.net available anymore. When trying to use SQLite instead:

'SQLiteConnection' does not contain a definition for 'InsertWithChildren' and the best extension method overload 'WriteOperations.InsertWithChildren(SQLiteConnection, object, bool)' requires a receiver of type 'SQLiteConnection'

I'm curious if it's even possible to develop Android-Apps with VS seamlessly. Since everything takes years to set up.

---

EDIT:

Well, after some days of pain in the a** the solution was to use the even newer pre release (alpha3) which wasn't even referenced to on the official pages. Currently I'm satisfied. :)

Install-Package SQLiteNetExtensions-netstandard -Version 2.0.0-alpha3 -Pre

Hope anybody may need this.

Answer 3

Ok after a bit of searching , I came to know that the creator of this extension used a pattern called MvvmCross while developing it. So unless you are using that pattern model you will get this error.

For non-MvvmCross users they have to rebuild the source files and reference it in their project and not use the pre-compiled one, the one you are using.

Go to the fourth post on the following link https://forums.xamarin.com/discussion/20117/sqlite-net-extensions-and-sqliteconnection here he tells how to rebuild from source in his post in a very brief manner.

Hope you will understand it but if not wait till I install Visual Studio and Xamarin and try it out myself so I can give you accurate steps. It will take a while so till then , Kudos!

Here is the link to source files https://bitbucket.org/twincoders/sqlite-net-extensions/downloads/ Click on download repository

Answer 4

I just solved this after doing a whole load of updates. Hit the same issues.

• Uninstall ALL SQLite plugins. See attached pic/link below.
• Reinstall SQLiteNetExtensions and this installs all dependencies too, actually all files in that list except SQLite.Net-PCL.
• SQLiteNetExtensions installs SQLite-net-pcl but you need the other one too so, install SQLite.Net-PCL as well.
• Restart Visual Studio as it doesn't always pickup all newly installed packages, the reopen your solution.

That worked for me so hopefully it'll help someone else here too.