Tag: SQLite

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