Local Data Access with SQLite

---

More often than not, applications work with data—and mobile apps are no exception. In many cases, mobile apps exchange data over networks and take advantage of cloud storage and services, such as push notifications. However, there are situations in which mobile apps only need to store data locally. For app settings, you can use the techniques described in Chapter 2. In the case of complex, structured data, applications need a different way to store information.

The good news is that you can easily include local databases in your mobile app using SQLite. This is an open-source, lightweight, serverless database engine that makes it simple to create local databases and perform operations on data. Information is stored inside tables, and data operations can be performed by writing C# code and LINQ queries. SQLite perfectly suits cross-platform development because it’s a portable database engine, it’s pre-installed on both iOS and Android, and it can be easily deployed to Windows.

In this chapter I will describe how to implement local data access with SQLite, but with a step further: securing the database with a password.

Note: The code example provided in this chapter is based on the Model-View-ViewModel pattern. If you are not familiar with it, you can read an introduction from the Xamarin.Forms Succinctly ebook. This is important advice, because I will assume you already know some concepts.

Using SQLite on Universal Windows Platform

While the SQLite engine is pre-installed on Android and iOS systems, it needs to be deployed to Universal Windows Platform systems. This is actually a very easy task. All you need to do is install a Visual Studio extension called SQLite for Universal Windows Platform. In Visual Studio 2019, select Extensions > Manage Extensions, and then search for SQLite. Figure 10 shows the extension you need to install.

Figure 10: The SQLite extension for UWP

Other versions of the extension can be ignored, as they are not intended to work with the latest versions of Xamarin.Forms. Once the extension is installed, it will make sure to package and deploy the SQLite engine to UWP along with the app binaries.

Choosing and installing the SQLite libraries

SQLite libraries targeting C# and .NET for your apps are available through NuGet. There are several editions, each targeting different platforms. For Xamarin.Forms, you have a couple of options:

• sqlite-net-pcl: A cross-platform, open-source library that enables creating databases, reading data, and writing data. Don’t be confused by the “pcl” (portable class library) literal; the library works with .NET Standard projects as well.
• sqlite-net-sqlcipher, also known as SQLCipher: Works like sqlite-net-pcl, but also adds the ability to secure the database with a password.

Securing the local database is a very good idea, especially if you store sensitive data, or simply because your company might want to add an additional level of security. Therefore, the sample project will be based on the sqlite-net-sqlcipher library.

Note: SQLCipher used to be a paid library and has been recently open-sourced. A paid version still exists, and the subscription offers technical support, custom builds, and additional options for the enterprise.

Assuming you have created a new blank Xamarin.Forms project, open the NuGet package manager in Visual Studio 2019 and install the sqlite-net-sqlcipher NuGet package to all the projects in your solution. NuGet will also install a number of dependencies, which are not necessary to cover in this chapter.

Tip: All the code that you will see in the next sections works exactly the same for both SQLite and SQLCipher libraries. The difference is that the latter allows for securing databases with a password, as you will see shortly. This means that you can reuse the code even if you do not need a secured database.

Setting up the database connection

Your code will access a SQLite database through a connection string, exactly like it would do with any other database. So, the connection string is the first thing you need to build. Because a SQLite database is a file that resides in a local folder, constructing the connection string requires the database pathname.

Though most of the code you will write is shared across different platforms, the way Android, iOS, and Windows handle pathnames is different, so building the connection string requires platform-specific code. You then invoke the connection string via the Xamarin.Forms dependency service. Because the example discusses SQLCipher, the connection string is also the place where you supply a password.

In the .NET Standard project, add a new interface called IDatabase­Connection.cs and write the following code:

public interface IDatabaseConnection

{

    SQLite.SQLiteConnection DbConnection();

}

This interface exposes a method called DbConnection, which will be implemented in each platform project, and will return the proper connection string. The next step is adding a DatabaseConnection (or a name of your choice) class to each platform project that implements the interface and returns the proper connection string, based on a sample database called CustomersDatabase.db3, where .db3 is the extension that identifies SQLite databases.

Code Listing 9 contains the code for Android, Code Listing 10 contains the code for iOS, and Code Listing 11 contains the code for UWP. The necessary comments will follow the listings.

Code Listing 9

Code Listing 10

Code Listing 11

What the code does on each platform project is obtain the path for a local folder where the database will be created. For Android, this is the user’s personal folder, and it is retrieved via the System.Environment.GetFolderPath method, passing the Personal value of the SpecialFolder enumeration.

For iOS, it is the user’s documents folder, obtained via the NSFileManager.GetUrls method (the arguments are self-explanatory). For UWP, it’s the local app folder, and is retrieved via the ApplicationData.Current.LocalFolder.Path object.

The last part of the code is common to all projects, and is about the connection string, which is of type SQLiteConnectionString. The constructor of this type takes the pathname, a bool parameter that specifies if the database file must be re-created (false in this case) if already existing, and a password that secures the database. This parameter is only available with SQLCipher. If you wanted to install the SQLite packages without security features, you would just write the following statement:

return new SQLiteConnection(path);

Remember the importance of the assembly attribute at the namespace level, with its Dependency argument that makes sure the runtime knows which implementation of the IDatabaseConnection interface it must use, depending on which platform the app is running on. Failure to add this attribute will result in a NullReferenceException when invoking the DependencyService.Get method.

Creating a data model

Let’s imagine the app provides a piece of user interface to manage a list of your customers. First of all, you need a Customer class to model your data. Code Listing 12 demonstrates this in a way that satisfies the needs of SQLite.

Code Listing 12

The first thing to note is the Table attribute at the class level, which maps the class to a table name. Because the SQLite libraries act as an ORM (object-relational mapping), the logic is that the library maps class properties to table columns in the database. So, at property levels, you will notice annotations such as Autoincrement and PrimaryKey for the id property (which maps the same-named column) and other annotations that apply constraints, such as NotNull and MaxLength.

The implementation of the INotifyPropertyChanged interface is useful to notify the user interface of changes on the data, but will be ignored by the SQLite library, so this interface implementation and the data annotation can coexist.

Now that you have a data model, it is a good idea to create a class that performs operations against data.

Reading, filtering, and writing data

The SQLite and SQLCipher libraries offer all the methods you need to read and write data, and you can use LINQ to filter your query results. Add a class called DataService to the .NET Standard project. The first lines of code declare a field of type SQLiteConnection that represents the connection to the database, and a static field of type object that is used to create a lock over the connection in order to avoid conflicts:

private SQLiteConnection database;

private static object collisionLock = new object();

In the constructor, you get the instance of the connection and create the table of Customer objects:

public DataService()

{

    database =

        DependencyService.Get<IDatabaseConnection>().

        DbConnection();

    database.CreateTable<Customer>();

}

The instance of the connection is retrieved via the DependencyService.Get method, which retrieves the proper implementation of the IDatabaseConnection interface, depending on the platform the app is running on. The SQLiteConnection.CreateTable<T> method creates a table based on the data model. The table is created only if it does not already exist.

Now it is time to write methods that perform operations against data.

Reading data

Let’s imagine we want to read the full list of records from the Customers table, and we want to retrieve an individual record. The first task is accomplished via the following simple method:

public IEnumerable<Customer> GetCustomers()

{

    lock (collisionLock)

    {

        return database.Table<Customer>().AsEnumerable();

    }

}

The Table<T> method returns a TableQuery<T> object, which is a collection that implements the IEnumerable interface. However, an explicit conversion via AsEnumerable is done to ensure maximum compatibility outside of the SQLite environment. Notice how a lock block surrounds the return statement, making sure other callers cannot access the database at the same time.

Retrieving an individual record is still simple, and it can be accomplished with the following code:

public Customer GetCustomer(int id)

{

    lock (collisionLock)

    {

        return database.Table<Customer>().

          FirstOrDefault(customer => customer.Id == id);

    }

}

Here you get a first example of data filtering with LINQ. The FirstOrDefault method is invoked on the collection to retrieve the first record that matches the supplied ID. More complex LINQ queries can be performed over the result of Table<T> to filter the list based on a given criterion.

The following method demonstrates how to filter the list of customers based on their country of residence:

public IEnumerable<Customer> GetFilteredCustomers(string countryName)

{

    lock (collisionLock)

    {

        var query = from cust in database.Table<Customer>()

                    where cust.Country == countryName

                    select cust;

        return query.AsEnumerable();

    }

}

It is worth mentioning that SQLite also supports the SQL language directly. For example, you could rewrite the previous method as follows:

public IEnumerable<Customer> GetFilteredCustomers(string countryName)

{

    lock (collisionLock)

    {

        return database.Query<Customer>(

          $"SELECT * FROM Item WHERE Country = '{countryName}'").AsEnumerable();

    }

}

Tip: Using the SQL language directly has the disadvantage of being more susceptible to injection attacks. You might want to use the managed approach where possible.

Writing data

SQLite supports both insert and update operations. You can easily detect if an object instance must be inserted or updated if its Id is equal to zero, like in the following method that saves an individual Customer instance:

public int SaveCustomer(Customer customerInstance)

{

    lock (collisionLock)

    {

        if (customerInstance.Id != 0)

        {

            database.Update(customerInstance);

            return customerInstance.Id;

        }

        else

        {

            database.Insert(customerInstance);

            return customerInstance.Id;

        }

    }

}

The code is very simple and does not need any further explanation. The SQLiteConnection class also exposes the InsertAll and UpdateAll methods, which allow for inserting and updating all the objects inside an IEnumerable<T> collection.

Though this is very convenient, a problem arises when you have a mixed list of new and updated objects. The solution is then represented with the following method, whose purpose is saving an entire IEnumerable<Customer> collection:

public void SaveAllCustomers(IEnumerable<Customer> customerCollection)

{

    lock (collisionLock)

    {

        foreach (var customerInstance in customerCollection)

        {

            if (customerInstance.Id != 0)

            {

                database.Update(customerInstance);

            }

            else

            {

                database.Insert(customerInstance);

            }

        }

    }

}

Basically, you apply the same logic of the previous method inside a foreach loop.

Deleting data

Deleting data is also an easy task, keeping in mind it’s a one-way operation that cannot be reverted. The following code deletes an individual record from the table:

public int DeleteCustomer(Customer customerInstance)

{

    var id = customerInstance.Id;

    if (id != 0)

    {

        lock (collisionLock)

        {

            database.Delete<Customer>(id);

        }

    }

           

    return id;

}

The Delete method requires the ID of the record you want to delete. Another method called DeleteAll allows for deleting all the objects in a table. You can also completely delete an entire table via the DropTable method. You will need to invoke the CreateTable method shown at the beginning to create the table again.

If your app handles very sensitive data, I would recommend you add a Boolean column to the table called IsDeleted or something similar, which you set as true if you want to mark the record as deleted, and then you update the record instead of physically deleting it. This allows you to keep a history of the data, even when some records will not be used anymore.

Note: There is more in the SQLite database engine. For example, it supports transactions, indexes, and backups, and the SQLiteConnection class also exposes the TableChanged event that is raised when a table is changed. For further details, have a look at the GitHub repository for the library.

Implementing Model-View-ViewModel logic

A good approach when working with databases is implementing separation between data models, data access, business logic, and user interface. The Model-View-ViewModel pattern is perfect for accomplishing this. In the previous section you wrote a data model and a class for data access. Now, it’s time to write a view model.

In summary, the view model needs to expose the following objects that will be data-bound to the user interface:

• A collection of Customer objects.
• An individual Customer object (useful to represent the selection in a list).
• Command objects that invoke methods in the DataAccess class and execute operations against data.

Code Listing 13 shows the full code for the ViewModel, and comments on the relevant points will follow.

Code Listing 13

The relevant points in Code Listing 13 are:

• The implementation of the INotifyPropertyChanged interface allows for notifying the bound views in the user interface so that they can automatically reflect changes.
• The AddCustomer method adds a new Customer object to the Customers collection and inserts the new object into the database at the same time. This demonstrates how updating an existing Customer works.
• Command objects, which will be bound to buttons in the user interface, call methods in the DataService class, according to the MVVM principles.

The final step is displaying a user interface. This is going to be very simple, but with all the pieces required to work with the database.

Creating the user interface with data binding

The user interface for the sample app is defined inside the MainPage.xaml file. A good choice to display the list of customers is the CollectionView control with a simple DataTemplate where a Label displays the value of the Customer.Id property, so that it cannot be edited, and where Entry views allows for editing the value for the other properties. Code Listing 14 shows the full XAML code.

Code Listing 24

About the CollectionView: the SelectedItem property is bound to the SelectedCustomer property of the view model; ItemsSource is bound to the Customers property, representing the full list of records coming from the database; and SelectionMode is Single to allow for one object selection.

The Label and the Entry views in the data template are bound to related properties in each instance of the Customer object. Finally, there are three buttons, each bound to a command in the ViewModel, that allow for performing operations against the data with an approach based on layer separation. The final step is instantiating the ViewModel and using this as the page’s data context. Code Listing 15 shows the C# code that you will add to the code-behind file for the page.

Code Listing 15

Now you can run the sample project and see the result of the work you’ve done so far.

Running the example

When you run the app for the first time, there will be no records in the database, so the code adds a new Customer instance, which is added to the Customers collection and automatically saved to the Customers table in the SQLite database. You can edit the new instance and add new instances, as demonstrated in Figure 11.

Figure 11: The sample app running and showing data from the database

Based on the code, when a new instance is added, it is automatically inserted to the database, but if you make changes to the values, you will need to save the data manually. This will perform and update the operation.

Chapter summary

SQLite is an open-source, serverless database that mobile apps written with Xamarin.Forms can leverage to locally store complex data within tables. In this chapter, you have seen how to secure the database with SQLCipher, create tables and read/write data, and display the tables in the user interface with data binding. The Model-View-ViewModel pattern has been used for true separation between data, data access, business logic, and user interface, which is the approach you should also use in the real world for better code maintenance.

There are certainly points for improvement which depend on your needs, but you have seen how simple it is to implement local data access securely. Managing data is clearly important, and data does not only mean databases. There are other sources for data in mobile apps, such as web APIs and JSON, which are discussed in the next chapter.