The Three Flavors of SQLite.Net

SQLite.net appears to be the tool of choice for many data driven Xamarin applications which is not surprising because it delivers on its promise of being “fast, efficient, simple, quick and convenient database layer”.The challenge is which SQLite are we talking about? – SQLite-net or SQLite.Net or SQLite-Net Extensions?

This post is to examine The Three Flavors of SQLite.Net in classic Xamarin application. This post is prompted in part because of the challenges or often confusions that I encountered while trying to wrap my head around these excellent tools. This post is not a comparison of these tools but rather my attempt to help anyone getting started with SQLite is Xamarin.

SQLite-net

This is where it all begins, SQLite-net is an excellent light weight ORM generously contributed to the community by Frank Krueger. It is created to allow .NET and Mono applications to store data in SQLite 3 databases. Think a mini version of Entity Framework for Xamarin apps. According to Wikipedia “An ORM (Object-Relational Mapping) is a tool that lets you query and manipulate data from a database using an object paradigm.” In practice this simply mean that ORM like SQLite-net lets developers intereact with the database using the language they are familiar with instead of writing raw SQL queries. So instead of this:

private String createBookTable =
                    "CREATE TABLE " + BOOK_TABLE + "(" +
                    KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    KEY_TITLE + " TEXT NOT NULL, " +
                    KEY_ISBN + " TEXT, " +
                    KEY_AUTHOR + " TEXT, " +
                    KEY_URL + " TEXT, " +
                    KEY_CATEGORY + " TEXT, " +
                    KEY_DESCRIPTION + " TEXT, " +
                    KEY_COMMENT + " TEXT " +
                            ")";

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createBookTable);
    }

With sqlite-net you can simply use the Book class to create the Book table like this,

 CreateTable<Book>();

the above code is Java by the way and this is not an indictment again native Android Java, there are other Java ORM like SugarORM, GreenDAO which works in similar fashion. So if SQlite-net is that good why do we need another flavor of Sqlite-net. The answer is right there in the project page.

Non-goals include: Not an ADO.NET implementation. This is not a full SQLite driver. If you need that, use Mono.Data.SQLite or csharpsqlite.

Translation, SQlite-net (the version with a dash “-” in the name) creates a generic implementation of SQLite in the target platform. That means that it is not taking advantage of each platform’s unique awesomeness. What is the fix? – enter SQlite.Net, the version without the “dash”.

SQlite.Net PCL

SQlite.Net is generously contributed to the community by Oystein Krog with contribution from other people. This is a fork or rather an improvement over SQlite-net. You will often see the original SQlite-net the one created by Frank Kruegger referred to as the official SQLite-net and sometimes in Xamarin forums and StackOverFlow people use both names interchangeably.

Notice that this flavor of SQLite comes as a PCL and more importantly this version of SQLite.net encourages or requires the use of platform specific SQLite implementation. That means that instead of just adding one component or nuget package to your project and use it to implement data persistence in each app in your project, now you have to add 1+n number of packages to your project. If you are building for on iOS and Android, then you have to add 1 generic SQLite.Net PCL plus two platform specific implementation of SQLite for both iOS and Android. This enables your app to run again native SQLite drivers in the each device.

If SQLite.Net fully compliments SQlite-net why do we need yet another flavor of SQlite.Net. The answer is in the fully qualified name of modern databases which is Relational Database Management Systems. Neither SQlite.Net nor its predecessor SQLite-net handles Relationship. So if you need to handle relationship between your entities then we need another flavor of SQLite – enter SQLite-Net Extensions.

SQLite-Net Extensions

SQLite-Net Extensions is a great contribution to the community by Guillermo Gutiérrez Doral – aka TwinCoders. Not a small number of production apps are dependent on this library maintained by just one individual – long live the TwinCoder! From the project page “SQLite-Net Extensions is a very simple ORM that provides one-to-one, one-to-many, many-to-one, many-to-many, inverse and text-blobbed relationships on top of the sqlite-net library.” It does this by providing “attributes for specifying the relationships in different ways and uses reflection to read and write the objects at Runtime”.

So there you have it, the three flavors of SQLite.Net in Xamarin world. I greatly appreciate all the time and effort that went into creating and maintaining these amazing libraries. My only wish list is a little more documentation and examples. I hope that the above explanation will help anyone looking for a Hello World kind of example for these libraries. Now that we have seen where each of these fit in, next we take a look at how we put them in practice. The example project below is a CRUD app against the familiar OO objects or Customer and Order. I will create the Shared Project and then show how they can be added to client project.

Project Creation

Using either Visual Studio or Xamarin Studio create a blank solution and name it any thing that you like. I am using Visual Studio and I will name my Solution SQLiteFlavors. Go ahead and add a PCL project to the solution. This will hold the shared code especially the data access logic. For the client app, I will only actually be implementing Android client in this demo but you are welcomed to implement more.


Go ahead and add the following folders to this PCL – a Models, Business and DataAccess. And inside the Business folder you can add two more sub folders Contract and Managers. In the Models folder add the following classes: Customer, Product, Category and Order. Go ahead and flush out the classes with the following properties.

First the Customer fields

public class Customer
    {
        public int Id { get; set; }
        public string CustomerName { get; set; }
        public string EmailAddress { get; set; }
        public string PhoneNumber { get; set; }
        public string StreetAddress { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string PostalCode { get; set; }
        public DateTime LastTransactionDate { get; set; }
      
    }

And the Product

  public class Product
    {
        public int Id { get; set; }
        public string ProductName { get; set; }
        public string Description { get; set; }
        public decimal Price { get; set; }
        public string Category { get; set; }
    }

And the Order the class

    public class Order
    {
        public int Id { get; set; }
        public int OrderNumber { get; set; }
        public DateTime DateCreated { get; set; }
        public decimal SubTotal { get; set; }
        public decimal TaxRate { get; set; }
        public decimal TotalAmount { get; set; }
       
    }

And lastly the Category

 public class Category
    {
        public int Id { get; set; }
        public string CategoryName { get; set; }
    }

From the above classes it is obvious that we need four database tables and what their columns will be. The Category could be an Enum in say an ASP.Net application but we will treat it as a table here because of simplicity.

SQLite-Net in Action

At this point, our concern is to create a Database and tables for our entities, we are not bringing relationships into scope. The official SQlite-net excels in this use case. So go ahead and add SQlite-net, the classic Nuget package or its PCL equivalent will suffice. Note that the Xamarin Component store is not in scope since we have not added any Xamarin project yet.

With SQlite-Net added we can now go back to out entities and decorate them with attributes that marks them as candidates for a SQLite-net. For now, we only need to add the Primary Key and Auto Increment attributes to each Id property of the entities like so:

[PrimaryKey, AutoIncrement, Column("_id")]
public int Id { get; set; }

To add the actual database, go ahead and add a class to the DataAccess folder called SQLiteDatabase.cs, it could be called anything. This class need to extend from SQLConnection to bring the power of SQLite-Net into scope. The constructor for this class accepts a SqliteConnection implementation and string parameter which is the path where it will create the database in the device. And in the constructor is where we go ahead and create the tables.

     public class SQLiteDatabase 
    {
        static object locker = new object();

        SQLiteConnection database;

        /// <summary>
        /// Initialize a new instance of the SQLiteDatabase
        /// </summary>
        /// <param name="path">Path to db on the device</param>
        public SQLiteDatabase(SQLiteConnection conn, string path)
        {
            database = conn;
            //Create the tables
            database.CreateTable<Customer>();
            database.CreateTable<Product>();
            database.CreateTable<Order>();
            database.CreateTable<Category>();
        }

Like most other ORM, SQLite-net uses objects to save and retrieve data fro m the database. So to create our Crud method, SQLite-net needs to know the type of object that it will be working with so we cannot just say

public int SaveItem()

But we can say

public int SaveItem<Customer>

or

public int SaveItem<Order>

That means that we have to create 4 different files where we define the CRUD operations (Save, Update, Delete, Get and GetAll). Alternatively we can follow the example of the TaskyPro sample app and create a base class or an Interface that all our entities inherit from and them use the Interface to write a single CRUD operations against the database. We then surface this database to the clients via the Repositories and the Entity Managers as we shall see shortly.

In the Business folder, create a Contract folder if not exist and then add a class IEntity with one property like so:

 public interface IEntity
    {       
        int Id { get; set; }        
    }

Now go back and make all your Entities (Customer, Order, Category and Product) implement this Interface. You should not have any squiggly lines because we have already defined the Id property in all the entities. With that we can now define the CRUD methods in the SQliteDatabase class under the Constructor.

 static readonly object Locker = new object();

        //Get all items in the database method
        public IEnumerable<T> GetItems<T>() where T : IEntity, new()
        {
            lock (Locker)
            {
                return (from i in database.Table<T>() select i).ToList();
            }
        }

        //Get specific item in the database method with Id
        public T GetItem<T>(int id) where T : IEntity, new()
        {
            lock (Locker)
            {
                return database.Table<T>().FirstOrDefault(x => x.Id == id);
            }
        }

        public int SaveItem<T>(T item) where T : IEntity
        {
            lock (Locker)
            {
                if (item.Id != 0)
                {
                    database.Update(item);
                    return item.Id;
                }
                return database.Insert(item);
            }
        }

        public int DeleteItem<T>(int id) where T : IEntity, new()
        {
            lock (Locker)
            {
                return database.Delete<T>(id);
            }
        }

Repository

We need a Repsository for each of the Entity we need to manage. We have managed to only have one file for the the core database, but we need to have a different Repository class file for each entity because the persistence need of a Product object maybe different from that of an Order object. We could in theory be able to create a generic Repository or an abstract base Repository, but for this app we will proceed by creating four different files under our DataAccess folder named ProductRepository, CustomerRepository, OrderRepository and believe it or not CategoryRepository. Each of them will look similar to ProductRepository below.

public class ProductRepositoy
{
readonly SQLiteDatabase _db = null;
protected static string DbLocation;

public ProductRepositoy(SQLiteConnection conn, string dbLocation)
{
_db = new SQLiteDatabase(conn,dbLocation);
}

          public Product GetProduct(int id)
		{
            return _db.GetItem<Product>(id);
		}
		
		public IEnumerable<Product> GetProducts ()
		{
			return _db.GetItems<Product>();
		}
		
		public int SaveProduct (Product product)
		{
			return _db.SaveItem<Product>(product);
		}

		public int DeleteProduct(int id)
		{
			return _db.DeleteItem<Product>(id);
		}

At this point, all that the Repository does is call into and surface results from the database, however remember that it could as well be calling into a Web Service, an in memory list or mock database. Go ahead and add the implementation for the other Repositories, just copy and paste the content of the Product Repository and replace the entities accordingly. For example in Order repository, replace Product with Order.

Entity Managers are yet another abstraction we will create. This is the API of our App, the entity managers are what the client projects interact with. Under the Business folder, create a sub folder called Managers if it does not exist and add the following files to it: ProductManager.cs, OrderManager.cs, CustomerManager.cs and CategoryManager.cs.

The contents of the Manager classes will look similar like the ProductManager.cs below:

public class ProductManager
    {
        private readonly ProductRepositoy _repository;

        public ProductManager(SQLiteConnection conn)
        {
            _repository = new ProductRepositoy(conn, "");
        }

        public Product GetCustomer(int id)
        {
            return _repository.GetProduct(id);
        }

        public IList<Product> GetCustomerss()
        {
            return new List<Product>(_repository.GetProducts());
        }

        public int SaveCustomer(Product order)
        {
            return _repository.SaveProduct(order);
        }

        public int DeleteCustomer(int id)
        {
            return _repository.DeleteProduct(id);
        }
    }

Use the above code as template for the other Manager classes, simply copy and paste and update as needed. At this point we are done the PCL project for our CRUD app for now. To see an example of how to add this PCL to your client projects please consult the TaskyPortable sample app to see example of how you can implement this in WP and IOS. Coming up, I will write a post that converts this PCL to use SQLite Async PCL.

Advertisements

About the Author valokafor

I am a Software Engineer with expertise in Android Development. I am available for Android development projects.

follow me on:

Leave a Comment: