Xamarin – Use of an ORM , SQLite.Net

 

When dealing with a mobile application, storage is a feature we cannot live without, whether it’s to handle relational data or persistent data in general, which is queried for different operations throughout it’s lifecycle.

Most if not all mobile platforms offer SQLite database storage method which consists of a helper class and some method of initialization and querying which are both declared by the programmer. In most cases, it’s just some boilerplate code consisting of declaring table names, column names and types, primary keys, foreign keys.. and all that jazz. But we are discussing cross platform native development here, let’s see what’s what in the area of local storage in Xamarin.

Ok, so the classical model of a cross platform application consists of a Core project, which is actually a .Net class library, and a couple of platform specific projects which have a link to the Core project. Each of the platform specific projects have – you guessed correctly, platform specific code.

So the Core project will consist of everything that can be shared by all platforms, that includes mostly business logic and anything that can be stripped to be separated from the specific API’s offered by each mobile platform.

So, both Android and iOS don’t come packed with an ORM – Object relational mapping (there are some wrapper libraries available which offer functionality of this sort , but then again, it’s platform specific – no use to us). However, when developing in Xamarin we are fortunate enough to have SQLite.Net, this amazing component available at the Xamarin component (free) is a beautiful tool which makes out lives easy and our code free from boilerplate unreadable SQL queries.

Here’s how it’s done,

First step, let’s create a model class called Car, and will let ID be the primary key for it, integer autoincrement.

   1:  public class Car

   2:      {

   3:          [PrimaryKey, AutoIncrement]

   4:          public int ID {

   5:              get;

   6:              set;

   7:          }

   8:   

   9:          public string Make {

  10:              get;

  11:              set;

  12:          }

  13:   

  14:          public int Year {

  15:              get;

  16:              set;

  17:          }

  18:      }

Ok, now we have the class ready, this will represent a row in a Table Named “Car” with ID being the primary key.

Then, we need to have some manager to be in charge of creating the DB when app starts, and provide us with a SQLiteConnection in order to perform database querying:

1. Set the Path for the db file to be stored at, some runtime flags due too difference in location of files in windows phone

2. The GetConnection() method returns a hook to the database connection

3. CreateTable<T> create the table of class type T

 

   1:  public class Database : SQLiteConnection

   2:      {

   3:          #if NETFX_CORE

   4:          private static readonly string Path = "Database.db";

   5:   

   6:          #elif NCRUNCH

   7:          private static readonly string Path = System.IO.Path.GetTempFileName();

   8:   

   9:          #else

  10:          private static readonly string Path = System.IO.Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments), "database.db");

  11:   

  12:          #endif

  13:   

  14:          public Database ()

  15:              : base(Path, true)

  16:          {

  17:   

  18:          }

  19:   

  20:   

  21:          public static SQLiteConnection GetConnection ()

  22:          {

  23:              return new SQLiteConnection (Path, true);

  24:          }

  25:   

  26:          public static void Initialize ()

  27:          {

  28:              CreateDatabase (GetConnection ());

  29:          }

  30:   

  31:          private static void CreateDatabase (SQLiteConnection connection)

  32:          {

  34:              //Create the tables

  35:              CreateTables ();

  37:          }

  38:   

  39:          private static void CreateTables ()

  40:          {

  41:              using (var conn = GetConnection ()) {

  42:                  conn.CreateTable<Car> ();

  43:              }

  45:          }

  48:      }

Notice that the Database Connection implements IDisposable interface, so when using it, the “using” syntax of C# takes care of open/close the connection for us.

Next : Querying

Samples:

Create a new Car instance

Insert Car to DB

Get the First Car From DB

Get Cars From db with year > 2001, and order by year

   1:   

   2:   

   3:  var newCar = new Car{Make = "Honda", year = 2008 };

   4:   

   5:  int insertResult = Database.GetConnection().Insert(c);

   6:   

   7:  var carFromDB = Database.GetConnection ().Table<Car> ().FirstOrDefault ();

   8:   

   9:  var carFrom2001 = Database.GetConnection().Table<Car>().Where(c => c.Year >=   
       2001).ToList().OrderBy(y => y.Year);
 
 

By querying using LINQ, life is easier and it’s quite quick to produce complex queries.

Downsides

The SQLite.Net component does not support Foreign key constrains, so if your needs involve a foreign key, producing it is relatively simple using an additional key representing an artificial foreign key, where you need to take take of cascading effects.

There is a component out there which supports one to many/many to many relationships:

https://bitbucket.org/twincoders/sqlite-net-extensions/overview

I did not test it just yet, but i believe it’s quite heavy with performance with many reflections being done at runtime, hopefully a working published version will find itself in the component store.

On the next post, I’ll show some nifty networking using RestSharp.