SQLite in Xamarin & MonoGame

Adding a database to your Xamarin & MonoGame projects is really easy with a couple of Nuget packages that are floating around out there. Used correctly, it is even possible to get 100& code sharing between platforms!

First add the following packages to your Android, iOS, .Net, or UWP project:
SQLite.Net-PCL
This package is the guts of SQLite on .Net
SQLite-Net Extensions
This package adds a lot of goodies like foreign-keys, code-first db development, etc.
SQLiteConnectionBuddy
This last package is a bit of code I wrote that abstracts out the platform-specific connection string used to connect to SQLite db.

Now that your project is set up, time to create the database structure. Lets pretend we are building a mobile game with one of those quest overworld maps that are in every single puzzle game out there. We need to model the quest-level as a one-to-many relationship:


[Table("Quests")]
public class Quest
{
    [PrimaryKey, AutoIncrement]
    public int? Id { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<Level> Levels { get; set; }

    public Quest()
    {
        Levels = new List<Level>();
    }
}

[Table("Levels")]
public class Level
{
    [PrimaryKey, AutoIncrement]
    public int? Id { get; set; }

    [ForeignKey(typeof(Quest))]
    public int QuestId { get; set; }

    [ManyToOne]
    public Quest Quest { get; set; }

    public Level()
    {
    }
}

Now that our basic data models are set up, we need to initialize the tables when the app starts up:


//This method gets called at the startup of the application.
public void Init()
{
    //do the database upgrades
    using (var connection = SQLiteConnectionHelper.GetConnection(@"LanguageGame.db", false))
    {
        connection.CreateTable();
        connection.CreateTable();
    }
}

The next step is to create a quest with a few levels and stuff it into the database:


var quest = new Quest();
quest.Levels.Add(new Level());
quest.Levels.Add(new Level());

using (var connection = SQLiteConnectionHelper.GetConnection(DatabaseName, LocalDatabase))
{
    connection.InsertOrReplaceWithChildren(quest);
}

//The primary key is populated by sqlite 
var questId = quest.Id;

Now when we need to retrieve our Quest with all child Levels attached, all we need to do is create a method to fetch via primary key:


public Quest GetQuest(int questId)
{
    using (var connection = SQLiteConnectionHelper.GetConnection(DatabaseName, LocalDatabase))
    {
        return connection.GetWithChildren(questId);
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s