This post is from Nicolò Carandini‘s blog post 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:

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.

Advertisements

One thought on “Universal App with SQLite – Part 2

Share your thoughts

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s