SQLite and ORM in Xamarin Forms

It’s about a week ago, I was looking for a complete tutorial about how to implement ORM in SQLite in Xamarin Forms. I did find out that there’s already a library called SQLiteNetExtensions that handle this kind of stuff. It’s also has complete documentation with it. Unfortunately, the documentation is not up to date. I was having difficulty to use that library at first, so I look up for other tutorials on the net. But I couldn’t find one that suit my need, a complete and up to date tutorial. This is actually pretty simple thing, but if you don’t know how to do it properly, it will cost you hours of your life before you can make it works. So, this is how I’ve done it.

Note : Nuget packages you need are SQLite.Net-PCL and SQLiteNetExtenstions

Database Strictures

First thing first. Because we’re talking about ORM, let’s have a simple data structure that represent all kind relationships in ORM; OneToMany, ManyToOne, ManyToMany and OneToOne.

public class Department
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string DepartmentName { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List Employees { get; set; }
}

public class Employee
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string IDCardNumber { get; set; }
    public string EmployeeName { get; set; }

    [ForeignKey(typeof(Department))]
    public int DepartmentID { get; set; }

    [ManyToOne(CascadeOperations = CascadeOperation.All)]
    public Department Deparment { get; set; }

    [ForeignKey(typeof(EmployeeFamily))]
    public int FamilyID { get; set; }

    [OneToOne(CascadeOperations = CascadeOperation.All)]
    public EmployeeFamily Family { get; set; }

    [ManyToMany(typeof(EmployeeBenefit))]
    public List Benefits { get; set; }
}

public class EmployeeFamily
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string SpouseName { get; set; }
    public string FirstChildName { get; set; }
    public string SecondChildName { get; set; }

    [ForeignKey(typeof(Employee))]
    public int EmployeeID { get; set; }

    [OneToOne(CascadeOperations = CascadeOperation.All)]
    public Employee Employee { get; set; }
}

public class EmployeeBenefit
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string BenefitName { get; set; }
    public double BenefitValue { get; set; }

    [ManyToMany(typeof(Employee))]
    public List Employees { get; set; }
}

One thing to take a note is make sure you’re using SQLite and SqLiteExtensions.Attributes instead of SQL.Net.Attributes or it’s not gonna works.

Dependency Services

To implement sqlite to Android and iOS, of course we gonna need dependency service classes. But first let create the interface in our PCL project.

public interface ISQLite
{
    SQLite.SQLiteConnection GetSQLiteConnection();
}

Then we move on to Android project. This is how the dependency service class looks like.

public class SQLiteAndroid : ISQLite
{
    public SQLite.SQLiteConnection GetSQLiteConnection()
    {
        var fileName = "testing.db3";
        var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        var path = Path.Combine(documentsPath, fileName);

        var platform = new SQLitePlatformAndroid();
        var connection = new SQLite.SQLiteConnection(path, true);

        return connection;
    }
}

Lastly, we also implement the dependency class to iOS project.

public class SQLite_iOS : ISQLite
{
    public SQLite.SQLiteConnection GetSQLiteConnection()
    {
        var sqliteFilename = "testing.db3";
        string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        string libraryPath = Path.Combine(documentsPath, "..", "Library");
        var path = Path.Combine(libraryPath, sqliteFilename);
        var conn = new SQLite.SQLiteConnection(path);
        return conn;
    }
}

Data Context

Next, let’s create a class that will handle all things SQLite related. So when we need to do some operations in SQLite, all we need to do just calling the method in this class. Below this is the sample code of the class, but I don’t include all operations possible, just some important operations for example.

public class EmployeeDataContext
{
    private static EmployeeDataContext instance;

    public static EmployeeDataContext Instance
    {
        get
        {
            if (instance == null)
            {
                instance = new EmployeeDataContext();
            }
            return instance;
        }
    }

    private SQLite.SQLiteConnection connection;

    private EmployeeDataContext()
    {
        connection = DependencyService.Get().GetSQLiteConnection();
        CreateTableIfNotExist();
    }

    private void CreateTableIfNotExist()
    {

        bool isTableNotExist = false;

        try
        {
            var test = connection.Table().FirstOrDefault();
        }
        catch
        {
            isTableNotExist = true;
        }

        if (isTableNotExist)
        {
            connection.CreateTable();
        }

        isTableNotExist = false;

        try
        {
            var test = connection.Table().FirstOrDefault();
        }
        catch
        {
            isTableNotExist = true;
        }

        if (isTableNotExist)
        {
            connection.CreateTable();
        }

        isTableNotExist = false;

        try
        {
            var test = connection.Table().FirstOrDefault();
        }
        catch
        {
            isTableNotExist = true;
        }

        if (isTableNotExist)
        {
            connection.CreateTable();
        }

        isTableNotExist = false;

        try
        {
            var test = connection.Table().FirstOrDefault();
        }
        catch
        {
            isTableNotExist = true;
        }

        if (isTableNotExist)
        {
            connection.CreateTable();
        }

    }

    public List GetAllDepartment()
    {
        return ReadOperations.GetAllWithChildren(connection, recursive: true).ToList();
    }

    public void RefreshDepartment(IList listDepartment)
    {
       WriteOperations.DeleteAll(connection, GetAllDepartment(), true);
       WriteOperations.InsertOrReplaceAllWithChildren(connection, listDepartment, true);
    }

    public void DeleteAllDepartment()
    {
        WriteOperations.DeleteAll(connection, GetAllDepartment(), true);
    }

    public void UpdateDepartment(Department department)
    {
        WriteOperations.UpdateWithChildren(connection, department);
    }

    public void UpdateEmployee(Employee employee)
    {
        WriteOperations.UpdateWithChildren(connection, employee);
    }
}

How to use it

Now, everything is ready, let’s see how to use it. You call the methods from view model or directly from code behind.

 


List listDepartment = new List();
Department department = new Department();
Employee employee = new Employee();

EmployeeDataContext.Instance.GetAllDepartment();
EmployeeDataContext.Instance.RefreshDepartment(listDepartment);
EmployeeDataContext.Instance.UpdateDepartment(department);
EmployeeDataContext.Instance.UpdateEmployee(employee);