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.


Published at

Tags: Xamarin,SQLite

Luke Alderton

Comments

Travis
Is the [Indexed] parameter required? Or is it implied if not included?
07/07/2018
Luke
Hi Travis, If you don't specify the Indexed attribute, it won't be indexed by SQLite.
11/07/2018
Share with
Tags
Latest Comments
By Mark Gentry on Windows Server 2019 - Change product key does nothing
20 Aug 2021, 03:30 AM
By Cathy on In-Place Upgrade for a Windows Server domain controller
31 Jul 2021, 18:28 PM
By Mr. Greymatter on Raspberry Pi - Running Java app on Raspbian
16 Feb 2021, 07:35 AM
By Mikko Seittenranta on Xamarin Forms multiple instances of same app open
16 Feb 2021, 04:34 AM
By Andrew on Auto/Custom height on Xamarin Forms WebView for Android and iOS
22 Jan 2021, 22:15 PM
By Nick on Raspberry Pi - Running Java app on Raspbian
14 Oct 2020, 19:37 PM
By Ivan on Fixed: Value cannot be null Parameter Name: source
15 Sep 2020, 19:47 PM
By Anand on Raspberry Pi - Bluetooth using Bluecove on Raspbian
7 Sep 2020, 16:53 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development