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.


By Luke Alderton at 25 Feb 2016, 14:36 PM

Tags: Xamarin,SQLite

Comments

Post a comment
Sit tight...
We're adding your comment.
Thank you.
Your comment has been added.
There's been a problem.
Please try again later.
Share with
Tags
Latest Comments
By dipbleds on Raspberry Pi - Bluetooth using Bluecove on Raspbian
12 Dec 2017, 21:29 PM
By Ich on Replacing the Xamarin Header/Navigation bar with a custom view/template
26 Nov 2017, 17:11 PM
By Ravi Motha on My experience at Umbraco UK Festival 2017
12 Dec 2017, 08:39 AM
By Borges on How to add a Xamarin Forms Loading Screen/Overlay
7 Nov 2017, 19:11 PM
By Rutul Mehta on Auto/Custom height on Xamarin Forms WebView for Android and iOS
4 Nov 2017, 03:15 AM
By Pablo on Xamarin MasterDetailPage has large margin/padding at top
26 Oct 2017, 15:31 PM
By faiza on Using MaryTTS or OpenMary in Java
22 Oct 2017, 12:54 PM
By Nietoperz on Using MaryTTS or OpenMary in Java
29 Sep 2017, 14:34 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development