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:
1 |
_dbConnection = new SQLiteConnection(dbName); |
is not correct, you’ll need to put:
1 |
_dbConnection = new SQLiteConnection(_SQLitePlatformWinRT, dbName); |
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:
1 |
StorageFile sqliteDB = await ApplicationData.Current.LocalFolder.GetFileAsync(dbName); |
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
1 2 3 |
196 #if NETFX_CORE 197 SQLite3.SetDirectory(/*temp directory type*/2, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path); 198 #endif |
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:
1 |
_dbConnection = new SQLiteConnection(_SQLitePlatform, Path.Combine(_SQLitePlatform.DatabaseRootDirectory, dbName)); |
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
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.
Hey Eric, can’t check my code right now. But I think it’s because I use the PCL version of SQLite-net! https://github.com/oysteinkrog/SQLite.Net-PCL
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.
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.
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. 🙂
Maybe if someone need about this could try EF7, for now is in RC1, but really make our lifes very easy.
http://ef.readthedocs.org/en/latest/platforms/uwp/getting-started.html
https://www.nuget.org/packages/EntityFramework.SQLite/7.0.0-rc1-final
http://ef.readthedocs.org/en/latest/providers/sqlite/index.html