Using Sqlite in a Xamarin.Android Application Developed using Visual Studio

Introduction

If you have ever developed an Android application using the Xamarin plug-in for Visual Studio, then chances are that you will have had a requirement to store and retrieve data. Although your application may initially receive its data via a web service, you may want to store the data on the device so that you do not need to keep requesting it from your web services. Unless your data is highly volatile, then storing the data on the device in a database makes sense. Data that is retrieved from local storage will be quicker than remote data retrieved via web services and is not at the mercy of connectivity problems if you are in a bad signal area. It is up to the developer to decide exactly what data they want to store locally, and how often they update that data.

Background

Android comes with built-in integration with the database Sqlite. If you haven't already heard of Sqlite, then I suggest you read up on it before going any further.

What is Sqlite?

In summary though, Sqlite is an open source and widely used database management engine with a very small footprint that makes it ideal for use in mobile applications. It is serverless, self-contained and requires zero configuration. It provides support for standard relational database features such as prepared statements and transactions, as well as providing SQL syntax.

Sqlite and Android

Sqlite is integrated into all Android devices and therefore a Sqlite database does not require any configuration or setting up. Once you have defined the structure of your database, it is automatically managed for you by Android. Your database definition can contain tables and indexes.

As with all database engines, you are effectively accessing the file system each time you access a table or index. So you need to consider these constraints when designing your database. Creating a database connection for example essentially accesses the files stored locally on the device. Therefore you should be aware that such an operation may take relatively longer to complete than another operation that doesn't access the local file system. For this reason, it is a good idea to consider performing such tasks asynchronously.

As with any database tool, Sqlite can be used in many different ways according to the needs of the application. This article demonstrates one way in which you can use Sqlite in your own Xamarin.Android application, but it is certainly not the only one.

Adding Sqlite to your Application

To include Sqlite functionality in your application, you will need to add the following reference to your code:

using Android.Database.Sqlite;

You will also need to add a Sqlite.cs source file to your project. You can get hold of the code from GitHub.

After you have added this file to your Visual Studio project, you will need to add the following reference to use the functionality contained within it:

using SQLite;

Creating and Updating Your Database

The following code shows a skeletal example of how your database is created and updated within Android. The key points to note are:

• Create a subclass of SQLiteOpenHelper
• Provide an implementation for the virtual method OnCreate()
• Provide an implementation for the virtual method OnUpgrade()

To create a database with SQLite, you will need to create a subclass of SQLiteOpenHelper. This is an Android helper class for managing database creation and versioning.

Within your newly created subclass, you will need to provide implementations for the methods OnCreate() and OnUpgrade().

• OnCreate() is invoked when your application is installed for the first time
• OnUpgrade() is invoked when you upgrade your application

Your OnCreate() method should contain all the code necessary to create your database entities such as tables and indexes. Your OnUpgrade() method should contain the code to amend your database when you make subsequent changes to the database structure. Note that both methods take a SQLiteDatabase argument.

Note also that the OnUpgrade() method additionally accepts two int arguments. The first int argument is the current (installed) database version. The second int argument is the new (upgrade) database version.

Your OnUpgrade() method should be coded so that it checks for the smallest (earliest) database version first and then gets successively higher, i.e., database upgrades should perform their changes on the earliest versions first and get progressively higher until they reach the penultimate database version.

using Android.Database.Sqlite;

namespace DataManager
{
    public class DataManagerHelper : SQLiteOpenHelper
    {
        // specifies the database name
        private const string DatabaseName = "myDatabaseName";
        //specifies the database version (increment this number each time you make database related changes)
        private const int DatabaseVersion = 3;

        public DataManagerHelper(Context context)
            : base(context, DatabaseName, null, DatabaseVersion)
        {
        }

        public override void OnCreate(SQLiteDatabase db)
        {
            //create database tables
            db.ExecSQL(@"
                        CREATE TABLE IF NOT EXISTS Customer (
                            Id              INTEGER PRIMARY KEY AUTOINCREMENT,
                            FirstName       TEXT NOT NULL,
                            LastName        TEXT NOT NULL )");

            //create database indexes
            db.ExecSQL(@"CREATE INDEX IF NOT EXISTS FIRSTNAME_CUSTOMER ON CUSTOMER (FIRSTNAME)");
        }

        public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            if (oldVersion < 2)
            {
                //perform any database upgrade tasks for versions prior to  version 2              
            }
            if (oldVersion < 3)
            {
                //perform any database upgrade tasks for versions prior to  version 3
            }
        }
    }
}

Creating Application Classes

It is good practice to create a single class for each of your application's tables. So for example, we created a table called Customer in our OnCreate() method just now. To access the data within this table throughout your application, create a class that maps to the table. This class will also be called Customer.

using System;
using SQLite;

namespace DataManager
{
    public class Customer
    {
        [PrimaryKey, AutoIncrement]
        public long Id { get; set; }

        public String FirstName { get; set; }
        public String LastName { get; set; }
    }
}

Set the Application Context

Before we can invoke any database functionality, the SQLiteOpenHelper subclass we created above needs to know the context of the application in order for it to work. In our case, we need to set the application context to our subclass DataManagerHelper.

In our database code, we need to add a method that accepts an Android Context as an argument. This method needs to be invoked before any database calls are made.

using SQLite;

namespace DataManager
{
    public class DatabaseUpdates
    {
        private DataManagerHelper _helper;

        public void SetContext(Context context)
        {
            if (context != null)
            {
                _helper = new DataManagerHelper(context);
            }
        }
    }
}

The above method needs to be invoked before any database calls are made. Therefore, you might want to consider adding the call to this database function from your application's OnCreate() event.

namespace myApplication
{
    [Application]
    public class myApplication : Application
    {
        public override void OnCreate()
        {
            base.OnCreate();

            //This method needs to be called before any database calls can be made!
            DatabaseManager mydata = new DatabaseManager();
            mydata.SetContext(this);
        }
    }
}

Updating Data

So far, we have created our database tables and created classes that map to these tables that we can use within our application. The next logical step is to implement code that adds / edits / deletes data from our tables. It is good practice to keep these definitions in a separate class and program file from our data definition classes created above.

Here are some example methods for adding / updating / deleting data from our database tables.

using SQLite;

namespace DataManager
{
    public class DatabaseUpdates
    {
        private DataManagerHelper _helper;

        public long AddCase(Customer addCust)
        {
            using (var db = new SQLiteConnection(_helper.WritableDatabase.Path))
            {
                try
                {
                    return db.Insert(addCust);
                }
                catch (Exception ex)
                {
                    //exception handling code to go here
                }
            }
        }

        public long DeleteCase(Customer deleteCust)
        {
            using (var db = new SQLiteConnection(_helper.WritableDatabase.Path))
            {
                try
                {
                    return db.Delete(deleteCust);
                }
                catch (Exception ex)
                {
                    //exception handling code to go here
                }
            }
        }

        public long UpdateCustomer(Customer updCust)
        {
            using (var db = new SQLiteConnection(_helper.WritableDatabase.Path))
            {
                try
                {
                    return db.Update(updCust);
                }
                catch (Exception ex)
                {
                    //exception handling code to go here
                }
            }
        }
    }
}

Querying Data

We will need to select and retrieve data from our tables in addition to updating them. We can use LINQ-to-SQLto achieve these sorts of queries. Here are some examples that demonstrate how we can query our database tables.

using System.Collections.Generic;
using SQLite;

namespace DataManager
{
    public class DatabaseUpdates
    {
        private DataManagerHelper _helper;

        //Get the total number of orders for a specific customer
        public int GetTotalOrderCount(string custNo)
        {
            using (var db = new SQLiteConnection(_helper.ReadableDatabase.Path))
            {
                try
                {
                    if (!string.IsNullOrEmpty(custNo))
                    {
                        int count = db.Table<Orders>().Count(c => c.CustNo == custNo);
                        return count;
                    }
                    return 0;
                }
                catch (Exception ex)
                {
                    return 0;
                }
            }
        }

        //retrieve a specific user by querying against their first name
        public Customer GetUser(string firstname)
        {
            using (var database = new SQLiteConnection(_helper.ReadableDatabase.Path))
            {
                try
                {
                    return database.Table<Customer>().FirstOrDefault(u => u.FirstName == firstname);
                }
                catch (Exception ex)
                {
                    //exception handling code to go here
                }
            }
        }

        //retrieve a list of all customers
        public IList<customer> GetAllCustomers()
        {
            using (var database = new SQLiteConnection(_helper.ReadableDatabase.Path))
            {
                try
                {
                    return database.Table<Customer>().ToList();
                }
                catch (Exception ex)
                {
                    //exception handling code to go here
                }
            }
        }
    }
}

There are two connection types in Sqlite.

• A read-only connection - to be used when retrieving / querying data
• A read-write connection - to be used when updating the data in the tables, i.e., when performing update / add / delete operations

To obtain a read-only connection:

using (var db = new SQLiteConnection(_helper.ReadableDatabase.Path))
{
    //code to go here
}

To obtain a read-write connection:

using (var db = new SQLiteConnection(_helper.WritableDatabase.Path))
{
    //code to go here
}

A possible naming convention that you could use is to precede all database functions with Delete, Update, Add or Get depending on what action they perform. Then you could add the entity that they act on as the second part of the function name.

i.e. <ACTION><ENTITY>

For example:

• AddCustomer
• UpdateCustomer
• DeleteCustomer
• GetCustomer
• AddOrder
• UpdateOrder
• DeleteOrder
• GetOrder

Handling Exceptions in Your Database Functions

I have omitted exception handling code from the examples so far for reasons of clarity and to keep the code simple and easy to understand. However, you will need some way to handle the cases when your code throws an exception. This may be due to reasons including database locks. One simple exception handling strategy is to wait for a small period of time and then retry the function again.

I have noticed in my own applications that if accessing a table from a tight loop that Sqlite is not always able to release a lock before another lock is requested, and sqlite will throw error messages relating to being busy or locked. Trying the lock again after a small wait seems to be an acceptable strategy.

using System.Threading;

using SQLite;

public long AddCase(Customer addCust)
{
    using (var db = new SQLiteConnection(_helper.WritableDatabase.Path))
    {
        try
        {
            return db.Insert(addCust);
        }
        catch (Exception ex)
        {
            //wait half a second and then try again
            Thread.Sleep(500);
            return AddCase(addCust);
        }
    }
}

You may also wish to log your errors so that they are recorded. You can use the Android logging system for this.

Summary

Hopefully, this article has given you sufficient information to start making use of Sqlite in your own Xamarin.Android application. Feel free to leave a comment if you would like me to further elaborate on anything within this article.