Hola and bon jour – my name is Rueben and I painstakingly handcraft local artisanal custom software.

Hi – my name is Matt and I’m going to translate Rueben’s hipster speak into normal English. He means when you hire him to create an app, he camps out in your backyard and takes a long time to finish.

The apps I create are true to the spirit of the device on which they run. A minimalistic design ensures nothing interferes in the experience between you and the phone. They’re designed to allow you to feel the electrons flow under your fingers.

The apps are black and white and ugly.
The data storage is stripped down to just the essential working parts – a simple CSV text file …
Wait… what?!? You’re saving your user’s data in just a CSV file? Why would you do that?
Well, I learned it from you! I was following along with your previous blog posts and you did it there! And while we’re at it, the UI of all your apps are black and white and rectangular too! And I know you ride your bike and I see you at the farmer’s market, don’t pretend you don’t try to be a hipster!
Ah… umm… well… let’s talk about data access now, shall we?

Cross Platform Storage Options

Alright… there are a couple of different ways we can store data on the device. Of course, we could just use the file system and store all data in CSV files.

That’s what I’m talking about!

But believe it or not there are other… and sometimes depending on what you want to do, better, options. For one, all of the operating systems have the ability to store app specific settings as key-value pairs. Data used for personalization of the app is a perfect fit here.

Using the file system is a perfectly valid way to store data. Caching downloaded images on the filesystem for increased performance down the line is a pretty good idea for file system storage. Using it to store user generated data that may need to be selectively retrieved, updated or deleted … not such a great idea.

This is where using a database makes sense.

How will we load SQL Server onto an iPhone?

SQLite Overview

SQLite is a file based database – the whole thing is a single file – no client/server here. It’s embedded into the application itself. However, because it is a file, the whole thing gets locked during write operations, so we’ll need to keep that in mind when writing to it. It’s small, fast and portable. It even implements most of the SQL-92 standard.

Ah – 1992 what a transformative year in SQL standards – and my 2nd birthday!

Best of all … SQLite is built into both iOS and Android based devices. It’s not in Windows by default, but we can install the database engine through Visual Studio.

Refactoring Tabata App To SQLite

Way back in my series of posts on creating cross platform code with shared projects "Cross Platform Code Sharing – Part 2 – Shared Projects") and PCLs "Cross Platform Code Sharing – Part 3 – PCLs") we created a Tabata app. Part of that app stored the completed Tabatas to a … umm … text file on the device. We’re going to refactor the PCL version of that app to use a SQLite database.

So let’s take a look through the thought process involved in moving a simple text file storage based app over to SQLite.

Where To Store The SQLite DB File?

By convention SQLite files have the extension “db3″ and as mentioned above, the single file is the entire database – engine and all. But where to actually store the file is a good question and of course depends on the operating system you’re dealing with.

• Android: The biggest decision you’ll have to make here is whether you want the database file stored on internal or external storage. However it’s common to store it in the /files folder.
• iOS: The best place to store the SQLite files in iOS is in the Library directory or a subdirectory off of there that you create.
• Windows Phone: Here we’re looking at the local directory

Of course there are other places where you can store the .db3 file, but those are the most common by convention.

Communicating With SQLite

One could add the System.Data and Mono.Data.SQLite references to their solution and access SQLite through the tried and true (and antiquated) ADO.Net. But I’ll be honest here … ADO.NET involves a lot of manual work and I’m pretty lazy.

Entity Framework 7 is coming in the near future! And it’s promised to be a much lighter framework that will be able to run on mobile devices. This will definitely be an option in the future – but for now it’s not production ready.

So that leaves us with … SQLite.Net. And really, this isn’t a let down at all. Created by Frank Krueger (praeclarum), SQLite.Net is an ORM and it’s available from GitHub, NuGet and the Xamarin Component Store. It runs on iOS, Android and WinPhone and exposes a nice LINQ interface to interact with the database.

Let’s take a deeper look at SQLite.Net

SQLite.Net Overview

SQLite.Net is an ORM library that allows us to access SQLite databases on a variety of platforms – due to the fact that it is PCL (and shared project) compliant. It is easy to integrate into Xamarin based projects (hello NuGet!) and provides a simple mechanism for CRUD operations.

It is lightweight and fast, but not as full featured as other ORMs that you may be used to, such as the Entity Framework. For example, foreign keys cannot be modeled (although there is another PCL library called SQLite.Net Extensions PCL which does provide relational functionality).

Modeling The Schema – Attributes!

By default, when we invoke the CreateTable<t> function on a SQLiteConnection object, it will create a table with the same name as the class type T represents, and will model all the properties as columns. We can use attributes to control how the schema is actually modeled.

Attributes are the mustaches of the programming world!

SQLite.Net attributes

• [Table(name)] -> specifies name of table if different from class name.
• [Column(name)] -> specifies name of column if different from property name.
• [PrimaryKey]
• [AutoIncrement] -> used to automatically increase an integer type by 1, usually on a primary key.
• [MaxLength(value)] -> specifies the maximum length of a string type column.
• [Ignore] -> specifies the property shouldn’t be included as a column in the table.
• [Unique]
• [Indexed]
• [Not Null]

The attributes are optional, but the primary key should always be an integer. To use these, make sure you import the SQLite.Net.Attributes namespace

SQLite.Net will also take care of mapping the .Net data types over to SQLite data types for us – no explicit conversion by us needed.

The refactored Tabata model class’s properties look like this – there are a lot of attributes I put in there which are not needed, but added just as a reference.

[PrimaryKey, AutoIncrement]
public int TabataID { get; set; }
 
// Change the column's name to "rest" instead of "RestInterval"
[Column ("rest")]
public int RestInterval { get; set; }
 
public int WorkInterval { get; set; }
 
public int NumberOfSets { get; set; }
 
[Unique]
public DateTime TabataDate { get; set; }

Adding SQLite.Net To The Projects

When using SQLite.Net, 2 libraries are required, both of which we’ll explain more in a bit. One is for the platform independent part of SQLite.Net and the platform dependent part. You can add these libraries through NuGet, as the following screenshots show.

Platform Independent SQLite.Net

Platform Dependent SQLite.Net

The platform independent portion of SQLite.Net (which of course could still be used in a platform specific project) holds all of the attribute information used to define the schema. It also implements the CRUD operations and holds the connection to the database. The platform dependent portion (as defined by ISQLitePlatform interface) contains the specifics & optimizations of how the platform implements SQLite.

Let’s Access Some Data!

We’re finally ready to do some data access and modification! Here are some basics, and then we’ll take a look at the Tabata app.

Connecting To The Database

Obviously we need a “connection” to, well, connect to the database. With SQLite.Net, we can use the SQLiteConnection object.

In order to instantiate the connection, we need to pass in the platform specific ISQLitePlatform we want to target as well as some other parameters to configure the connection. The most important of these is to tell the connection to store the DateTime as ticks (otherwise it will store it in human readable format, and that could lead to sorting issues down the road).

public TabataRepository (IDataPlatform dbInfo)
{
    if (_db == null) {
        _db = new SQLiteConnection (dbInfo.SQLitePlatform, dbInfo.DBFile, true);
 
        // Create the tabata table - if the table already exists, won't recreate it
        _db.CreateTable<Tabata> ();
    }
}

Once the connection object is created – the database is created!

We do need to remember that when any write operations happen to the database, the entire database is locked. So we will need to guard against locking using something like the following:

// lockGuard is an object defined at the class level
 
lock (lockGuard) {
	_db.Insert (tabataToInsert);
}

And of course, there’s always the SQLiteConnectionWithLock class as well.

Creating A Table

Once we have a SQLiteConnection object, we can go about creating the model. The syntax for doing so is quite simple.

// Assuming _db is our SQLiteConnection object
// This is all that's needed to create a table
// Or modify its schema
 
_db.CreateTable<Tabata> ();

Remember – SQLite.Net will use the attributes from the model class to create the schema.

Capabilities

We’re able to perform common queries with ease using SQLite.Net.

• Insert -> Inserts a single record
• InsertAll -> Inserts several records one by one inside a transaction
• Update / UpdateAll -> Updates one or several records using the primary key
• Delete / DeleteAll -> Deletes one or several records using the primary key
• Get -> Returns a T object based off of the primary key
• Table -> Returns a TableQuery object
• Query -> Perform a SQL query to return a T object

When dealing with the TableQuery<t> object that’s returned by the Table<t> function, we can then use LINQ to filter results or expose more advanced query capability!

public Tabata GetTabataById (int tabataId)
{
    return _db.Table<Tabata> ().Where (t => t.TabataID == tabataId).FirstOrDefault ();
}

Summary

That’s all there is to it! It’s really that easy to get going with a real database engine and

SQLite as a database and SQLite.Net as an API give us a great and easy mechanism to provide database access across platforms. SQLite is lightweight and built in to both iOS and Android. It is easily added to Windows Phone through Visual Studio. SQLite.Net provides an ORM for the database which gives us a very easy and intuitive way to interact with the underlying database.

Oh… and guess what? There’s an asynchronous version of SQLite.Net as well!

I’m never going to handcraft a storage solution from a CSV file again … SQLite is the new locally sourced organic database!