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.

Comments

Post a Comment

Popular posts from this blog

Spinner VS AutoCompleteTextView

-
Image
       We’ve all been in a situation where we needed to show a list of some sort, a list which should be triggered by a user interaction (click, tap, etc..) A list is simple enough, probably even a spinner if we don’t want to spend that much screen real-estate on showing a list which the user clicks once and probably doesn’t need to see it after that. A spinner can be a real bug once there are too much entries for the user to choose from, how much is too much? that’s a good question – probably it differs from user to user, and probably this will have some performance issues with areal large number of entries, you cannot scroll forever to find what you’re looking for. A simple solutions for the problems mentioned above is an AutoCompleteTextView. it’s a control which is basically a combination of a TextView, EditText, and a Spinner all wrapped up in a single easy to use and configurable control. let’s see how it’s done:   first, in the layout file, create an AutoCompleteTextView
Read more

How to update UI from a different thread

-
     Google has set it’s tone on some tough guidelines for developing android apps, basically there aren’t any strict rules but one very easy to misunderstand. there is a single Thread which is called the “main” thread, or “UI thread” on some places. this is the thread which is in charge on the views and interaction with the users gestures. based on the guidelines of any mobile platform at the moment, all the work that shouldn’t be done on the main thread should be done on background thread. this can be achieved in many ways, you can use AsyncTask, Or A Handler, Or a simple thread with an implementation of the   run() method within the declaration of the runnable. ok, so if I’m pulling some server data, or performing long calculations, pulling stuff from or to the Sqlite database I’ll probably use an AsyncTask. but let’s say for instance that I want to simply update the UI once every couple of seconds (update a TextView or an ImageView’s content etc..), here’s a simple example of i
Read more

Utilizing Http response cache

-
     A typical mobile application will probably consume some resources from a web server using http requests. This repeated task has some drawbacks: Bandwidth has a cost, polling the same data over and over again results in a waste of user’s quota. the responsiveness and user experience is compromised once the user keeps waiting for data. If the requested data hasn’t changed on the server, then there’s no reason for us to fetch it. Of course, local caching of data is a must, but we also need to be able to refresh the data if it has changed on the server. A simple solution which comes out of the box for API 4+ is http response caching. Http response caching is a mechanism which enables us as developers to save a footprint of the response utilizing the abilities of the Http protocol. Basically, is holds a simple cache of the response which can be defined and enabled in code, and it prevents from fetching unchanged data from the server which was already fetched and processed into t
Read more