Setting up tables in SQLite with a primary key on a Xamarin Forms app

SQLite has two API's available, the synchronous and the asynchronous API's. It's easy to set a primary key with the synchronous API because you'll be typing the SQL yourself, however the asynchronous API is much easier to use for people with less SQL knowledge and requires much less code. It's just hard sometimes to get your head around the initial setup, as is the case when setting a primary key. You'll kick yourself when you find out how.

I won't go into installing SQLite into your project in this post, but I just wanted to provide another place on the web that points you in the right direction for the simple yet sometimes hard to find things when creating an app with Xamarin and setting up your database in Xamarin Forms also requires you to do some native work in the separate projects for each device, but when it comes down to it, you'll be using an SQLiteConnection object, in this post, i'll refer to that object as 'the database'.

So say you have a model for an object you want to store in the local database, you'll need to create said database and add a table for the items defined by your model, here's my model:

public class EventItem
{
    public Int32 DBId { get; set; }
    public Int32 Id { get; set; }
    public String Title { get; set; }
    public String Summary { get; set; }
    public String Body { get; set; }
    public DateTime Date { get; set; }
    public String Address { get; set; }

    public EventItem() { }
}

And here's the code to create the table in the database.

objDatabase.CreateTable();

You'll notice I've got a DBId and an Id variable, say I want to use the DBId as the primary key for the database table and Id as the Id given to me by a web service I'm using to get these Event items. I'll need to tell SQLite that this is what I intend, so I make the model look like this:

public class EventItem
{
    [PrimaryKey, AutoIncrement]
    public Int32 DBId { get; set; }
    [Indexed]
    public Int32 Id { get; set; }
    public String Title { get; set; }
    public String Summary { get; set; }
    public String Body { get; set; }
    public DateTime Date { get; set; }
    public String Address { get; set; }

    public EventItem() { }
}

I've added a parameter to the DBId variable telling SQLite to use DBId as my primary key and that it should also automatically increment the value. Since I've also got another id given to me from a web service, chances are I'll be running queries to find items by that id too, so I've added the Indexed parameter to the Id variable. This should speed up queries.

To insert items into that table, use this handy method:

public Int32 InsertEventItem(EventItem objEventItem)
{
    return objDatabase.Insert(objEventItem);
}

And to get them, use this:

public EventItem GetEventItemByDBId (Int32 intDBId)
{
    return objDatabase.Table().FirstOrDefault(x => x.DBId == intDBId);
}

Then finally, to delete them, use this:

public int DeleteEventItem(Int32 intDBId)
{
    return objDatabase.Delete(intDBId);
}

That's it. On a side note, if you don't set a primary key, you won't be able to delete from the table, since it requires the primary key value to find the correct record.

If you wan't a full example of all the functions, go here: https://components.xamarin.com/gettingstarted/sqlite-net It'll give you some nice examples to follow.

If you want to find out more about the attributes available in SQLite, check my other post.