Using SQLite database in Xamarin.Android

I started to define development tasks to be done within MoneyBack project in the coming days (maybe I’ll even publish my Trello board soon 🙂 ) and it turned out that each functionality the app is going to offer needs persistent storage (e.g. to store people, payments, events etc.). The obvious choice is the local database in which I could store my entities and application’s data.

In this post, I’d like to show you how quickly and easily SQLite database can be added and started to be used in Xamarin.Android project using SQLite.NET and Visual Studio 2017.

What is SQLite?

SQLite is an open-source database engine, which allows to store data in SQL structures. The whole database is stored in a single file, which makes it easily manageable on mobile devices. It’s lightweight, small and easily portable. It’s also prepared to work on multiple platforms.

There are some limitations in contrast to “classic” SQL database engines, including:

• OUTER join syntax is not fully supported
• for altering tables, only “RENAME” and “ADDCOLUMN” operations are available
• writing to views is impossible (views are read-only).

SQLite database can be easily operated using SQLite.NET ORM library available to download and install via Nuget. Let’s see how to do it.

Installing SQLite.NET package

In order to install SQLite.NET package, simply open Android.Xamarin solution in Visual Studio, open Package Manager Console and type the following command:

install-package Sqlite-Net
After few seconds, two files are added to our project:

It means we’re all set 🙂
Entity definition
We have SQLite ORM ready to use. Now we need to define our first entity. In case of MoneyBack, I decided to start with defining Person (plural: People) entity. First thing we need to do is to define entity class. I created “Entities” folder for that and added the following class file:








  





namespace MoneyBack.Entities



{



    [Table("People")]



    public class Person



    {



        [PrimaryKey, AutoIncrement]



        public int Id { get; set; }








        public string Name { get; set; }








        public string LastName { get; set; }








        public string PhoneNumber { get; set; }








        public string Email { get; set; }








        public override string ToString()



        {



            return $"[Person: Id={Id}, Name={Name}, LastName={LastName}, PhoneNumber={PhoneNumber}, Email={Email}]";



        }



    }



}






        view raw

        

          Person.cs

        

        hosted with ❤ by GitHub
      


Just a class, in which we define basic properties of a Person – Name, LastName, PhoneNumber and Email. As we are designing a database entities, we also need to have keys for each of them – in this case we add Id property, and decorate it with two attributes:

PrimaryKeyAttribute – marks the column as PRIMARY KEY
AutoIncrementAttribute – marks the column with AUTO_INCREMENT (unique number is generated when a new record is added into a table).

We also have TableAttribute defined on Person class. This attribute’s constructor takes a single argument name, which is the name of the table which will be created underneath. In our case, single entity is called Person, but the table should be named in plural (table == set of entities) so we want it to be called People.
I also implemented ToString() method in order to be able to easily see details of our entity if needed.
Location of database file
Before database can be used, a database file must be created on the device. ORM does it for us before any query is made on the database, but we need to specify where we want to keep the db file. On Android, it can be stored either on internal or external storage.
I created Constants.cs class in which I put a read-only constant string returning path to database file. As a folder to store db file, I used Environment.GetFolderPath(Environment.SpecialFolder.Personal) which returns local path defined in Android system’s environmental variable $HOME:








  





public class Constants



{



    public static readonly string DbFilePath = 



        Path.Combine(



                Environment.GetFolderPath(Environment.SpecialFolder.Personal), 



                "moneyback.db"



        );



}






        view raw

        

          Constants_dbPath.cs

        

        hosted with ❤ by GitHub
      


Database access methods
As soon as we have entity class defined and location of database file, we can start performing operations on the db – define tables, add new entities (rows), retrieve rows from the database, delete them etc. In general, it comes down to creating SQLiteConnection object and performing operations on it.
There are several approaches of exposing Data Access Layer services/methods – we can create a database service, entities repository or use complex services location and dependency injection in order to operate our persistent data. I don’t know what is a standard way in Android applications, but I decided to create a very simple repository for our Person entity and expose database-access methods in it:








  





public class PeopleRepository



{



    private SQLiteConnection db = null;



    protected static PeopleRepository me;








    static PeopleRepository()



    {



        me = new PeopleRepository();



    }








    protected PeopleRepository()



    {



        db = new SQLiteConnection(Constants.DbFilePath);



        db.CreateTable<Person>();



    }








    public static int SavePerson(Person person)



    {



        me.db.Insert(person);



        return person.Id;



    }








    public static Person GetPerson(int id)



    {



        return me.db.Get<Person>(p => p.Id == id);



    }



}






        view raw

        

          PeopleRepository.cs

        

        hosted with ❤ by GitHub
      


I haven’t even created any abstract layer for the repository (e.g. interface), as I simply don’t know what approach I will use for the next entities. I’m not sure if the repository is the right choice. On the other hand, I don’t think such rather simple app requires something more. What do you think ?


As you can see, PeopleRepository executes its static constructor when any of static methods is called for the first time, in which it instantiates db variable of type SQLiteConnection, giving it the path to db file we defined. If database file doesn’t exist, it will be automatically created. Then, db.CreateTable<Person>() creates People table if it doesn’t exist, according to what we defined in Person entity model.
Two static methods – SavePerson and GetPerson allow to save and retrieve Person entity to/from the database accordingly. The first one is additionally returning the integer Id of newly added row.
Usage in the code
I added two text fields to MoneyBack app to enter name and last name of person to be added and one button which actually allows to perform saving operation:

On clicking the button, the following method is executed:








  





private void _btnAddPerson_Click(object sender, EventArgs e)



{



    var name = _inputName.Text;



    var lastName = _inputLastName.Text;








    var id = PeopleRepository.SavePerson(new Person



    {



        Name = name,



        LastName = lastName



    });








    var person = PeopleRepository.GetPerson(id);








    if (person == null)



        Toast.MakeText(this, $"Person: Name={name}, LastName={lastName} wasn't properly saved!", ToastLength.Long).Show();



    else



        Toast.MakeText(this, $"Person saved, details: {person}", ToastLength.Long).Show();








}






        view raw

        

          addPersonButtonClick.cs

        

        hosted with ❤ by GitHub
      


 
Here’s the result of clicking the button in the app:

As you can see, in the toast message received, Id of the Person added has value 3. That’s because when I launched the app previously I’ve already added two new people. It shows that the data is really persistently stored and doesn’t disappear when the app is restarted.
Summary
Today we’ve seen that using SQLite file database in Xamarin.Android is very easy. Thanks to SQLite.NET lightweight ORM library we were able to quickly start working with the db.
To make the code ready for “production” use, I should also make it thread-safe (more details e.g. here) and add unit tests (which I’m BTW not able to do for now, I’m getting TargetInvocationException when trying to run any NUnit tests adding Unit Test App (Android) project in VS 2017…).
I’m also not sure if repository is a proper concept for managing local db entities in Android project. If you have more experience, I’d be grateful to hear how it should be done in a really “Android way” 🙂

Related Posts


Date selection using DatePickerDialog in Xamarin.Android
In this post, we're going to see how to provide a nice Android UI control…


Xamarin.Android - debugging via WiFi
In this short post, I'm going to show you a very handy feature of Android Debug…


Xamarin - Android Intents
We've already seen what are Activities, the most basic pieces of every Android app and…