sqlite on iot on the raspberry pi 2

Question

Is there anyone who got this to work? I spent days, literally days to try to get this to work.

But I always end up with an exception that sqlite3.dll is not found. Now apparently the SQLite vsix which allows the reference to 'SQLite for Universal App platform' should take care of this. But so far with no luck.

I am referencing:

'SQLite for Universal App platform'

SQLite.NET

SQLite.NET.Async

SQLite.NET.Platform.WInRT

VC++ 2015 runtime

Everything builds fine, but when SQLite tries to make the connection it is missing the sqlite3.dll

Anyone got this to work?

Answer 1

Andy Wigley on channel 9 is the go to man for IoT SQLite.

---

riclh

Answer 2

Thanks, any specific url where he explains using SQLite and IOT? I don't see anything on channel9 about it?

Answer 3

https://channel9.msdn.com/Events/Windows/Developers-Guide-to-Windows-10-RTM

and

Using SQLite-WinRT in UWP apps

http://andywigleyblog.azurewebsites.net/?p=721

might be a good starting point

---

riclh

Answer 4

Thanks, found them, tried the samples but they do not work either. They were build in older versions of SQLite and vs. Got them to compile again, but then they only partly work and as something goes wrong without any exception inside the sqlite3, it's impossible to find out what the problem is.

Just cannot believe that there are so few people trying to use SQLite on the Pi.

Answer 5

As I say Andy is the champion here. Try his blog or email at MS as he is usually quick to respond to these things.

He is based in the UK so think in that timezone

---

riclh

Answer 6

I'll try, thanks

Answer 7

Thanks to riclh for pointing me at this.
I have three samples working on Raspberry Pi 2 that use SQLite under the hood: first uses Entity Framework 7, the second uses the SQLite-Net wrapper, and the third uses the SQLite-WinRT wrapper. I see no reason why SQLitePCL should not work, but I haven't tried it yet.
I think that the Entity Framework folks have managed to get sqlite3.dll installed as a dependency of their NuGet packages, but for the SQLite-Net, SQLite-WinRt and other similar wrappers, you need to add a reference to the SQLite for Universal App Platform extension SDK - that's what brings in sqlite3.dll.
Apart from that, all I did for SQLite-WinRT was added a ref to the Sqlite-Winrt.uwp NuGet package (that's pre-release so check the appropriate checkbox when searching).

For SQLite-NET, there is no point in adding the SQLite-Net NuGet package because NuGet V3 has broken it - NuGet v3 does not allow adding of content files into your project. So instead, all I did was to copy the SQLite.cs and SQLiteAsync.cs files from a Windows 8.1 Store App project and added them to my project. That's all it took!

I have posted samples at http://andywigley.com Working with SQLite in UWP

---

Technical Evangelist, Microsoft DPE

Answer 8

Hi Andy, thanks for your response.

I tried your samples at 'Working with SQLite in UWP', the SQLiteSamplesUWP.zip

I build the SQLiteWinRT, used it in a sample Background IOT project. And took the code from your sample to create two tables.

The first table creates fine, the second just does not, the background task just exits. No errors, was not able to catch any exception. But the implementation was too basic anyway, I have been using SQLite a lot using the async version and was hoping to use that in this project too. BTW the sample project in the zip is not buildable at all in the lastest version of VS 2015.

I tried many projects with all kind of SQLite nuget packages, I always included the 'SQLite for the Universal App Platform' in the references. I even had a version with Sqlite-Winrt.uwp. I got most projects to build and to run. But they all fail because of the same reason, they cannot find the sqlite3.dll. Now somehow this 'SQLite for UAP' is responsible for the distribution of sqlite3.dll (adding an ARM version of sqlite3.dll to the project itself is not possible), but it does not work when you create a backgroundtask project. The moment the first SQLite3.xxxx call is made, it stops with a dll not found.

Again, I got it to work with a Universal Windows App in the Raspberry, but not with just a backgroundtask project.

Maybe I am doing something totally wrong....

But I keep trying. I know have again a working project (Universal WIndows App) on IOT, using SQLite-net (that includes the SQLite.cs and SQLiteAsync.cs.  Now I will try to take the SQLite part out of it, put in a UWP library and reference that from the App. The next thing would be to reference this same library from the backgroundtaskproject. (And as I just added SQLite-net with no problem, I guess I don't have NuGet V3 yet).

While writing this all down, I start wondering if the problem might be that I reference the 'SQLite for UWA' from the library that I use and not from the App itself and that therefor the deploy of the App does not result in a deploy of the sqlite3.dll.

Haven't given up yet, could really use a 'complete working SQLite class library example ' that I could just reference in my App(s).

Again thanks for responding  and btw , I did leave a comment earlier on this page https://andywigleyblog.azurewebsites.net/?p=721

Ben

Answer 9

So I build the library, referenced it from the App and deployed. Everything worked fine.

Then I build the BAckgroundTask for IOT project, referenced the same library. And run the same code from the startup task. It fails. Next I referenced the 'SQLite for UWA' from the backroundtask project as well. Run it , again it fails.

This is the code:

public async System.Threading.Tasks.Task<bool> CreateDB()
       {
           SQLite.SQLiteAsyncConnection connection;
           bool res = await DoesDbExist("a.db");
           res = true;
           if (!res)
           {
               SQLite.SQLiteOpenFlags sqlflg = SQLite.SQLiteOpenFlags.Create;
               connection = new SQLite.SQLiteAsyncConnection("a.db", sqlflg, false);
           }
           else
           {
               connection = new SQLite.SQLiteAsyncConnection("a.db");
           }
           await connection.CreateTableAsync<Sensor>();
 
 
           return res;
       }

And this is what the Output windows shows when it executes CreateTableAsync(...):

'backgroundTaskHost.exe' (CoreCLR: CoreCLR_UWP_Domain): Loaded 'C:\Data\Users\DefaultAccount\AppxLayouts\BackgroundApplication1-uwpVS.Debug_ARM.Ben\System.Collections.dll'. Cannot find or open the PDB file.
The program '[2492] backgroundTaskHost.exe' has exited with code 1 (0x1).

or

'backgroundTaskHost.exe' (CoreCLR: CoreCLR_UWP_Domain): Loaded 'C:\Data\Users\DefaultAccount\AppxLayouts\BackgroundApplication1-uwpVS.Debug_ARM.Ben\System.Runtime.Extensions.dll'. Cannot find or open the PDB file.
The program '[3800] backgroundTaskHost.exe' has exited with code 1 (0x1).

I am certain that it cannot find the sqlite3.dll

Ben

Answer 10

Please see the reply Andy Wigley gave above about the location of the dll and how to build it. AFAIK he is the most experienced guy in MS on this topic.

From your error messages the lack of the dll does not seem to be the problem. Pdb files are debug stuff and AFAIK don't effect execution,

What exception are you getting to get to that exit?

---

riclh

Answer 11

I know the missing PDB's are no problem. And I have been unable to catch an exception, tried 'try catch' everywhere. It just exits. At one time I managed to include the SQLite code of CreateTableAsync in the project, so I could step through the debugger. It gave an exception on the first SQLite3.xxxxx call with a file not found.

Actually I think Andy had the same problem, he mentions having the same problem in his blog, that is why he wrote his UWP version in the first place:

"For some reason, when you try to new up an instance of the SQLiteWinRT Database object, we get a FileNotFoundException. That just seems a bit weird, and in these pre-RTM "

But.... I just managed to browse the directories of the Pi, and found the  directory where the Backgroundtask app is installed. And it does contain a sqlite3.dll. So now I don't know what the problem is at all.

As a normal Universal App is working, I may try to start with that and start a background task from there.

Answer 12

May I suggest that you run as similar code as you can in the Foreground task first? When it is working, only then move it to the Background.

If what you do is not UI friendly, start a Thread from the worker pool to get out of the UI in a foreground thread into a quieter space.

---

riclh

Answer 13

I'll try, thanks

Answer 14

I have updated the samples posted on my blog at http://andywigleyblog.azurewebsites.net/?p=841 .
The SQLite-NET sample now demonstrates accessing a database from a background task.

Andy

---

Technical Evangelist, Microsoft DPE

Answer 15

Great Andy,

I cannot wait to try them.

Thanks

  Ben

Answer 16

Thanks again Andy,

Got the samples to work, this is an excellent starting point.

Ben

Answer 17

I Andy, I download your EntityFramework sample and Yes that works great on my Pi3 with Windows IOT. 

But you are using old unlisted package EntityFramework.Sqlite. I think that now we must use the Microsoft.EntityFrameworkCore.Sqlite and Microsoft.EntityFrameworkCore.Tools (prerelease). I try that, and all work good in x86, but I got a sqlite3.dll mission on ARM devices. Any suggestion?

---

Pierre