Universal App with SQLite – Part 2

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:

  1. Move one copy of “SQLite.cs” and “SQLiteAsync.cs” files to the Shared project
  2. 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!

Universal App with SQLite – Part 1
Back To the Future: Windows Batch Scripting & ASP.NET vNext

26 Comments

LEAVE A COMMENT

  • Sebastian Kralemann Reply

    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 Reply

    Jun 6, 2014

    […] This post is from Nicolò Carandini‘s blog post Universal App with SQLite – Part 2 […]

  • Nino Padrutt Reply

    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 Reply

    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 ... Reply

    Aug 30, 2014

    […] Ссылка на источник: Universal App with SQLite – Part 2 […]

  • FM Reply

    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 Reply

    Oct 7, 2014

    Thanks a lot for this tutorial!

  • Janak Reply

    Oct 9, 2014

    Great Tutorial…Thanks 🙂

  • Datenbank in Universal Apps - Windows Phone 8 Entwicklung - Windows Phone 8 Forum Reply

    Oct 10, 2014

    […] […]

  • Doumas Odysseas Reply

    Oct 17, 2014

    I just love you. After 2 days of nothing but problems, your blog saved me. Thank you.

  • kaustubh pandey Reply

    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 Reply

    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 Reply

    Dec 17, 2014

    Unfortunately, there is no Foreign Key solution available for Windows Phone 8.1 yet.

  • Chihab Hammadi Reply

    Jan 23, 2015

    Thank you very much.

  • Ron Reply

    Feb 20, 2015

    Awesome tutorial! Thank you!!!

  • Eitan Barazani Reply

    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 Reply

    Mar 24, 2015

    It does help. Thanks a lot!

  • SQLite, Solusi Database bagi Aplikasi Offline [Part 1/2] | TechnoUpdate Reply

    May 29, 2015

  • Membuat da Menggunakan SQLite [Part 2/2] | TechnoUpdate Reply

    May 29, 2015

  • JR GSC Reply

    Jul 17, 2015

    A HUGE thank you for writing this tutorial! This really helped me get started!

  • Roberto Alessi Reply

    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 Reply

    Aug 24, 2015

  • Javad Reply

    Sep 9, 2015

    Awesome ! Thank you #Nicolò for this great article !

  • Ajay Verma Reply

    Nov 16, 2015

    Woew.. !!! That’s coolest blog ever.. 🙂

  • D.Venkateswarao Reply

    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 Reply

    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

FEEDBACK