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:
- [Indexed] | [Indexed(string name, int order)]
- [MaxLength(int length)]
- [Collation(string name)]
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):
We can query directly a table object using Linq:
or using the connection query method:
The SQL command can contain parameters:
Once retrieved, the object representing the table row can be easily modified at model level and persisted at database level:
To delete a table row:
Drop a table
Deleting a table is also super easy:
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.