Using SqlLite in Windows Phone 8

Even thought SqlLite is still not very well supported on WP8 compared with SQL CE, it can be convenient to use it expecially if you want to share some DAL code with an other platform like Android or even Windows 8 app. The portability and its semplicity are the main features of SqlLite DB.

First of all you have to choose a convenient program to handle on your PC the data file created by SqlLite. I find SQLiteStudio the more convenient and easy to configure (there is no installer, since all you need to run SQLiteStudio is to download the binary distribution from download page and just run) but in the following you can find some other valid alternatives:

SQLite Database Browser
http://www.sql-workbench.net/ (it require to use the JDBC driver http://www.sqlite.org/)
https://bitbucket.org/xerial/sqlite-jdbc => sample URL “jdbc:sqlite:C:/work/mydatabase.db”

sql workbench – JDBC driver configuration

To properly configure the solution for Handling a SqlLite DB, There are several blogs that explain it in a very similar way, for example:

Nokia developer wiki
WP8 Blog
Matteo Pagani’s blogs/articles (1, 2 , 3 )

What you need is to install SQLite for Windows Phone that install in C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\ … a C++ library (sqlite3.dll) that need to have a wrapper to be used in a C# project. So you need to add to your solution a wrapper project and make e refenence to it in your DAL project. There are two possible wrappers available:

sqlite-net-wp8 project (github.com)
sqlite-winrt project (codeplex.com)

The first wrapper library is very simple to use, but it has some limitations: the biggest one is that doesn’t support relationships out of the box.

The second wrapper doesn’t support any LINQ operation but only manual SQL statement: the biggest pro is that you can perform any type of operation, even managing relationships. The biggest cons is that you’ll have to rely on old plain SQL queries to do any operation, even the simplest ones, like creating a table or inserting some data.

For more information to make the right choice for you, se the following posts:

To see the documentation of sqlite-net (very few), go to that wiki.

For a tutorial and a reference on SqlLite you can go here. (* ); remember to put on the Build tab, Conditional compilation symbols under the General header, SILVERLIGHT;WINDOWS_PHONE;USE_WP8_NATIVE_SQLITE

To handle the GIT repository of sqlite-net and sqlite-net-wp8, you can use Visual Studio Tools for Git or better GitHub

In the following there are some links where you can find slides and examples how to use SqlLite in WP8:

Community Days 2013 site
Rajenki’s site
Patric Schouler
Nokia site
Also Matteo Pagani made some examples available, in particular one from the code for Chapter 9 of his book “Sviluppare applicazioni per Windows Phone 8” – that I bought!
If you want to use the sqlite-winrt wrapper see this post that takes into account a foreign key relationship: you can download the related project here. See also Andy Wigley blog here (slide/video, example); SQLite WinRT wrapper for Windows Phone. Be careful to put PRAGMA foreign_keys = ON to handle foreign key!
Nokia italian webinar #6: “Accesso ai dati: storage, data base, data sharing”.