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:

SQLiteNetFiles
Files added by SQLite.NET

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"
);
}

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);
}
}

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:

MoneyBack_SavingPerson.png
Adding new Person

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();
}

 

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

MoneyBack_SavingPerson_Success.png
Person saved successfully

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” 🙂

.NET full stack web developer & digital nomad
0 0 votes
Article Rating
Subscribe
Notify of
guest
7 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mark Shook
Mark Shook
6 years ago

I found this useful, but I do wish you had included a bit more, namely how read the data back. Nevertheless, thnaks for publishing it. Cheers, man.

arian
arian
5 years ago

hello
in visual studio 2017 i got this error:
‘sqliteconnection’ does not contain a constractor that takes one argumens.
how can i fix it?

arian
arian
5 years ago

hello
in visual studio 2017 i got this error:
‘sqliteconnection’ does not contain a constractor that takes one argumens.
how can i fix it?
can you help me?
thank you.

Samson
Samson
5 years ago

Visual studio 2017 does not recognize using SQLite;
I have installed the pack sqlite-net-pcl. please what can I do