8

SQLite-net and SQLite-net extensions with Universal Apps

So I wanted to get my hands dirty with some SQLite in Universal Apps.
Turns out I had my hands full with trying to figure out why stuff wasn’t working… so here a small blog post to help others if they have the same problems.

Let me explain the setup.

* Univeral app template
* Installed SQLite SDK for Windows Runtime (Windows 8.1) and Windows Phone 8.1
* Installed SQLite-net nuget ( to be able to work with helper methods and attributes )
https://github.com/praeclarum/sqlite-net
I used the PCL version https://github.com/oysteinkrog/SQLite.Net-PCL
* Installed SQLite-net extensions nuget ( to be able to handle relationships through attributes )
https://bitbucket.org/twincoders/sqlite-net-extensions

First problem, while using the SQLite-net in combination with SQLite-net extensions, the SQLConnection method needs an extra parameter to identify for what platform you want to run SQLite.
So what you’ll see in most tutorials:

is not correct, you’ll need to put:

But where do we find this SQLitePlatformWinRT implementation? Well there is a nuget for this too! BUT alas in Universal this won’t load because of the Windows Phone project that is also inside the shared solution.
Only way to circumvent this is by copy/pasting the source code, because the code is correct for both platforms in an Universal setup!
Thanks StackO for this: http://stackoverflow.com/questions/27506374/sqlite-net-platform-for-windows-phone-8-1
The source code can be found here: https://github.com/oysteinkrog/SQLite.Net-PCL/tree/master/src/SQLite.Net.Platform.WinRT

Second problem, when doing all this, everything works great – but I was unable to check if the database exists after I created it with the SQLConnection method!
Again all tutorial say, just check the local storage like this:

So if you’ll get an exception the file does not exists, so there would not be a DB. But even though I had no file, I was able to perform queries!
Seems SQLite-net uses a pre compiler directive NETFX_CORE that is being hit when I only supply a db name to the SQLConnection method. The code is in the SQLite.cs file here https://github.com/praeclarum/sqlite-net/blob/master/src/SQLite.cs

In other words the db was created in the temporaryfolder!
To fix this we need to supply a full path while first creating the db so change the SQLConnection method again to:

After this, the db will be inside the ApplicationData.Current.LocalFolder and so you can again use the GetFileAsync(dbName) method to verify if your db is available or not.

I hope this helps some people… happy coding

Uncategorized

Depechie

8 Comments

  1. Thanks for the post. Very helpful, but when I got to instantiate a SQLiteConnection, I don’t get an option for a constructor that takes the platform argument. I am trying to do this for a WinPhone 8.1 Universal app. Any idea what I am missing? I installed the visx for SQLite WinPhone 8.1, and the nuget for SQLite-net.

  2. So I figured it out. After installing the .visx for each platform, you then need to add a dll reference to “SQLite for Windows Phone 8.1” via the “Windows Phone 8.1 -> Extensions” node of the add reference dialog.

  3. Oh, and after add the reference to the dll installed by the visx, you no longer need the platform specific code put into your project. You can just move the 2 files that SQLite-net adds into your shared project and remove them from the main projects.

  4. Good introduction, thanks. I think it’s slightly confusing that there’s so many forks, Nuget packages etc. plus there’s a bunch of old blog posts (2-3 years) coming up in search results with different (outdated) explanations.

    And thanks for recommending the SQLite-net extensions, I didn’t hear about those before. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *