Tips and Tricks on Using SQLite-Net with Xamarin.iOS - Fri, Jun 24, 2016

SQLite-Net is a nice library compatible with Xamarin that lets you create and use a SQLite database as the local data source of your application. It is ultra portable as it comes in only two C# files, one of which is for asynchronous operations. You can easily get it from NuGet and get started with it.

Creating a database (if not exists)

The first step in using SQLite-Net is creating a database, and its tables. This is automatically done by creating a SQLiteAsyncConnection and calling its CreateTableAsync<T> method, in which T is your model class. This method checks for the existence of a table with the same name as the model class, and creates it if it does not exist. For example, if we want to make a database named map.db containing a table based on the model class Pin, we can do the following:

var path = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Personal), "map.db");
var connection = new SQLiteAsyncConnection(PATH, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex, true);
await connection.CreateTableAsync<Pin>();

You can keep the connection variable in case you’d like to use the database in the future, but be careful from which thread you access it. Many of the issues with SQLite-net come from accessing the database from a wrong thread, and the error messages usually give no hint regarding this.

Model classes

SQLite-Net takes care of serializing and deserializing data between queries and .NET objects. A model class can look like this:

using System;
using SQLite;
namespace Mapp.Models
{    
    public class Pin
{
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public int ServerId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public double Latitude { get; set; }
        public double Longitude { get; set; }
    }
}

In this example, the Id field is an auto-incremented primary key for table Pin.

Injecting DTO values into a model object

You can use the neat reflection features of .NET to automatically inject values from a Data Transfer Object (DTO) that is a subset of your model fields. Here is a helper method that does this for you:

using System.Linq;
using System.Reflection;
namespace Mapp
{
    public class DTO
    {
        public void Inject(object destination)
        {
            var destFields = destination.GetType().GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
            var myFields = this.GetType().GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
            foreach (var item in myFields)
            {
                var matchingFields = from x in destFields where x.Name == item.Name select x;
                if (matchingFields.Count() == 0) continue;
                matchingFields.First().SetValue(destination, item.GetValue(this));
            }
        }
    }
}

For example if you have the following DTO, you can inject its values into a Pin object using the Inject method:

public class PinDTO : DTO
{
    public int ServerId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
}


Pin pin = new Pin();
pinDto.Inject(pin);

Querying, inserting and updating records

You can use LINQ syntax to query the database and get a list of objects of the type of your model class. For example to find a pin with a certain ServerId and update it (or create it if it does not exist) you can write:

var query = connection.Table<Pin>().Where(x => x.Id == dto.ServerId).ToListAsync().Result;
var pin = Query.Count > 0 ? query[0] : new Pin { ServerId = dto.ServerId };
dto.Inject(pin);

if (pin.Id > 0)
{
    // Record exists. Update it.
    await connection.UpdateAsync(pin);
}
else
{
    // Record does not exist. Create it.
    await connection.InsertAsync(pin);
}

Again, you have to be careful from which thread you are accessing the database. To delete a record you can use the DeleteAsync method:

await connection.DeleteAsync(pin);

Back to Home