In this series of posts, I’ll describe how to build a SQLite Test app that will run on Windows Runtime 8.1 Devices (Windows 8.1 and Windows Phone 8.1).
On the first post, I briefly introduced the new “Universal App” Visual Studio project type, and explained how to add SQLite to the project.
On this second post, we will see how to use the Universal App project type to avoid unnecessary duplication of code by sharing the things that remain the same for both Windows Store app and Windows Phone Store app, and how to take advantage of SQLite to manage locally stored data.
On the first post we’ve created a Universal Windows App (this is the official term for Apps built using the shared project tooling that target both Windows and Windows Phone ), with all the right references to the SQLite library.
To help us hiding all the intricacies of calling the SQLite APIs, now we will add the sqlite-net library on both Windows and Windows Phone projects, using NuGet:
As a result, we will have two copies of the two files “SQLite.cs” and “SQLiteAsync.cs”, one in the Windows project and one in the Windows Phone project:
To avoid unnecessary duplication, let’s start using the shared project:
- Move one copy of “SQLite.cs” and “SQLiteAsync.cs” files to the Shared project
- Delete the other copy.
At the end, this will be the new outcome:
Using sqlite-net is very simple and there are many posts describing how to use it, like Using SQLite in your Windows 8 Metro style applications by Matteo Pagani.
Problem is that some of them are a bit dated and something on the sqlite-net library has changed from then, so it’s better to repeat here the basic operations available:
Create / Open a database
To open a database named “People.db” (will be created if nonexistent):
By default, the sqlite-net library will create the database file on:
To check for the existence of the database file:
Create a table
We start defining the class that represent the table row:
As you can see, it’s possible to decorate the class and the properties with attributes defined in the sqlite-net library, in almost the same way we are used to do with DataAnnotation and EF. Here is the full list:
Class attribute:
- [Table(Name)]
Property attributes:
- [AutoIncrement]
- [PrimaryKey]
- [Column(Name)]
- [Indexed] | [Indexed(string name, int order)]
- [Unique]
- [Ignore]
- [MaxLength(int length)]
- [Collation(string name)]
- [NotNull]
Because the User class is part of our model that will be used by both the Windows and Windows Store app, the right place to create it is on a Model folder located into the shared project:
Now that we have defined the class that correspond to the table row, we can create the table, using the database connection:
The method will check for the existence of the table in the SQLite file and if not found it will create it. Moreover, if the class that represent the table row has changed, the method will try to update the SQLite table (actually, only the adding of new columns is supported).
It worth noting that the method will not destroy an existent table, so it can be redone without previously checking for the existence of the table.
Add a record to a table
This is super easy:
Add records to a table
Any IEnumerable collection can be added to the table (the IEnumerable item type must obviously be the same of the table creation one):
Retrieve records
We can query directly a table object using Linq:
or using the connection query method:
The SQL command can contain parameters:
Update records
Once retrieved, the object representing the table row can be easily modified at model level and persisted at database level:
Delete records
To delete a table row:
Drop a table
Deleting a table is also super easy:
Demo app
To demonstrate how to use the sqlite-net library I’ve build a very simple demo app.
Disclaimer: For the sake of simplicity, I haven’t used MVVM. Moreover, I’ve built a single XAML page for both Windows and Windows Phone project. On production code you will likely use MVVM (Model View ViewModel) and different XAML pages specifically designed for each platform (the Windows UI will surely be different from the Windows Phone UI).
To demonstrate the use (or abuse, in this case) of the Shared project, I’ve created a single XAML page with some of the converged controls of Windows Store and Windows Phone app.
Here you can see the CommandBar that on Windows Phone shows PrimaryCommands (maximum five) as AppBarButtons and SecondaryCommands as MenuItems:
while on Windows shows PrimaryCommands on one side and the SecondaryCommands on the other side, all of them as AppBarButtons:
Because the XAML file is the same for both the Windows and Windows Phone projects, I’ve moved it to the Shared project and deleted from the Windows and the Windows Phone projects.
Moreover, because the App.xaml document that has been created automatically on both the Windows and Windows Phone projects during the creation of the Universal App solution is the same, I’ve also moved it to the Shared project and deleted from the Windows and the Windows Phone projects.
At the end, here is the situation:
and here is the demo running on the Windows Phone emulator:
You can download the demo solution on my OneDrive: http://1drv.ms/1kQ9sP0
This conclude my second post on how to use the Universal App project type to avoid unnecessary duplication of code by sharing the things that remain the same for both Windows Store app and Windows Phone Store app, and how to take advantage of SQLite to manage locally stored data.
Happy coding!
Sebastian Kralemann
May 31, 2014
Hi Nicolò,
I just want to say THANK YOU for your great blog posts about using SQLite with Windows/Phone 8.1!
I have searched a lot till I have found your posts and got SQLite working with Windows/Phone 8.1.
Kind regards,
Sebastian
Universal App with SQLite – Part 2 | World Wide Code
Jun 6, 2014
[…] This post is from Nicolò Carandini‘s blog post Universal App with SQLite – Part 2 […]
Nino Padrutt
Jun 23, 2014
Hi Nicolò
thanks for your post.
How do you add FK’s? As far as I see there is not Annotation for that. Do you add This with a seperate SQL Statement after creating the table?
Regards
NPadrutt
stephane
Jul 7, 2014
Great article!!!
I have one question. I like to debug the app on the device (windowsphone) and this does not work because the x86 architecture. Did you know how i can run it on the phone. Wenn i change it to ARM i get a exception in Sqlite code. but on the emulator with a x86 architecture its work fine.
Thanks
SQLite в универсальных приложениях. Часть 2 | Меняя мир Windows ...
Aug 30, 2014
[…] Ссылка на источник: Universal App with SQLite – Part 2 […]
FM
Aug 31, 2014
I downloaded your example project, but the reference “SQLite for Windows Runtime (Windows Phone 8.1)” is unresolved so the project won’t build. Where does that reference come from?
In your part 1 you link to http://visualstudiogallery.msdn.microsoft.com/5d97faf6-39e3-4048-a0bc-adde2af75d1b but it shows up as “SQLite for Windows Phone 8.1”, not “QLite for Windows Runtime (Windows Phone 8.1)” as your screenshot shows. Where do I get this version?
Abraham Hinteregger
Oct 7, 2014
Thanks a lot for this tutorial!
Janak
Oct 9, 2014
Great Tutorial…Thanks 🙂
Datenbank in Universal Apps - Windows Phone 8 Entwicklung - Windows Phone 8 Forum
Oct 10, 2014
[…] […]
Doumas Odysseas
Oct 17, 2014
I just love you. After 2 days of nothing but problems, your blog saved me. Thank you.
kaustubh pandey
Oct 26, 2014
can you tell me how to use SQLitePCL ? Please I searched on internet a lot about it and not able to find it . The video by andy wigley and matthias shappiro also does not solve my problem . I am a beginner and don’t know much about SQLite . Also , does using different version makes any difference .
Chethan G
Nov 11, 2014
Thanks for this tutorial. It helped a lot to get started with the SQLite database for windows phone 8.1 app. I am facing another challenge with password protecting the database. Many sources suggest to use encryption since there is no password protection available with SQLite for windows phone. I had a look at SqlCipher which requires a paid license for windows phone extension. Please suggest me an open source encryption plug-in available.
John
Dec 17, 2014
Unfortunately, there is no Foreign Key solution available for Windows Phone 8.1 yet.
Chihab Hammadi
Jan 23, 2015
Thank you very much.
Ron
Feb 20, 2015
Awesome tutorial! Thank you!!!
Eitan Barazani
Mar 3, 2015
Hi,
Thank you for writing this blog articles (1&2).
Do you have a project based on these articles that I can download.
Thx
Eitan
Sam Baldwin
Mar 24, 2015
It does help. Thanks a lot!
SQLite, Solusi Database bagi Aplikasi Offline [Part 1/2] | TechnoUpdate
May 29, 2015
[…] http://blog.tpcware.com/2014/05/universal-app-with-sqlite-part-2/ […]
Membuat da Menggunakan SQLite [Part 2/2] | TechnoUpdate
May 29, 2015
[…] http://blog.tpcware.com/2014/05/universal-app-with-sqlite-part-2/ […]
JR GSC
Jul 17, 2015
A HUGE thank you for writing this tutorial! This really helped me get started!
Roberto Alessi
Jul 21, 2015
Ottimo tutorial, ma seguendolo passo passo mi scontro con il problema che non riesce a caricare sqlite3.dll
Ho cercato su stackoverflow, ecc. ma non trovo info atte a risolvere il problema.
Qualche suggerimento ? sto provando con preview vs2015.
Visual Studio Toolbox | Martin Kašpar
Aug 24, 2015
[…] http://blog.tpcware.com/2014/05/universal-app-with-sqlite-part-2/ […]
Javad
Sep 9, 2015
Awesome ! Thank you #Nicolò for this great article !
Ajay Verma
Nov 16, 2015
Woew.. !!! That’s coolest blog ever.. 🙂
D.Venkateswarao
Dec 22, 2015
thanks for writing this tutorial.but my requirement is dynamically taking input from the textbox and store that value and retrive value.
please help me anybody………….
Thanks in advance
Ali Sufian
Mar 8, 2016
Thanks its a good tutorial
But i need some help how to use sqlite with universal application i have problem how i use to retrieve data from database and then display on to UI
Copyright © 2013 Nicolò Carandini - This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License