Skip to content

14 Local Data with SQLite

Jump to bottom
Matthew Leibowitz edited this page Apr 18, 2019 · 2 revisions

In this section, we will look at creating an alternative data store using a local SQLite database. We will support the same operations and have the same type of implementation structure.

You can view the code and the diff for this step on GitHub.

Creating a SQLite database

Creating a local database is not something that is done as an actual step, but rather we just say we want to perform an operation on a database file location. The operation will then make sure the database exists, and if not, create it.

Before we write any code, we will have to make sure we have some NuGets installed:

We don't need to use the Xamarin.Essentials package to use database, but we will use some of the features in this library. It typically comes included in the new Xamarin.Forms project templates, but some older versions of Visual Studio do not include it.

So, to actually create this local version of the cloud database, we are going to create a new class that follows the same API as the cloud database:

public class LocalDatabase
{
    private const string DatabaseName = "games.db3";

    private readonly Task initializedTask;

    public LocalDatabase()
    {
        initializedTask = Task.Run(async () =>
        {
            // TODO: connect to the database
        });
    }

    public async Task<bool> AddCompletedGameAsync(CompletedGame game)
    {
        // TODO: add a game
    }

    public async Task<int> GetGamePlayCountAsync(string board)
    {
        // TODO: count the games
    }
}

This sets up our local database class with the same structure as the cloud database. We can now add the logic to connect to a local database. In the delegate of the Task.Run method of the constructor, we can connect to a database file using a SQLiteAsyncConnection:

private SQLiteAsyncConnection connection;

public LocalDatabase()
{
    initializedTask = Task.Run(async () =>
    {
        var databasePath = Path.Combine(FileSystem.AppDataDirectory, DatabaseName);
        connection = new SQLiteAsyncConnection(databasePath);

        await connection.CreateTableAsync<CompletedGame>();
    });
}

The SQLiteAsyncConnection constructor takes a few arguments, but we just need to pass the path to the database file. If the database exists, then a connection is opened and the file can be used. If it does not exist, then the connection will create the database.

We are using FileSystem.AppDataDirectory from Xamarin.Essentials to get the root folder for the database, but we could get this value in other ways. You can read more about the FileSystem type in the docs.

After a connection is made to the database, we can start running operations on the database. The first thing we do is make sure the tables exist, using the CreateTableAsync method of the connection. If the table does not exist, this will create it. If it does exist, then any updates to the CompletedGame class will result in those updates being made to the table.

We do have to make a small adjustment to the CompletedGame type. Because this class represents a record in a table, we have to let the table know which column it should use as the primary key. To do this, we add the [PrimaryKey] attribute to the Id property:

public class CompletedGame
{
    [PrimaryKey]
    public string Id { get; set; }

    // ...
}

Adding records to the database

Now that we have a connection, we can implement the AddCompletedGameAsync method to actually store data in the database.

This method is very similar in implementation structure, we first make sure the initialization task is complete, add the record, and return true. If there are any errors, we report them:

public async Task<bool> AddCompletedGameAsync(CompletedGame game)
{
    try
    {
        await initializedTask;

        await connection.InsertAsync(game);

        return true;
    }
    catch (Exception ex)
    {
        Crashes.TrackError(ex);

        return false;
    }
}

Reading data from the database

In order to be able to read data from the database, we can just use LINQ to count the number of records that match the board - just like what we did for the cloud database.

In the GetGamePlayCountAsync method, we again follow the pattern of making sure that the initialization is done and then access the data. If there are any errors, we report them:

public async Task<int> GetGamePlayCountAsync(string board)
{
    try
    {
        await initializedTask;

        var result = await connection.Table<CompletedGame>()
            .Where(g => g.Board == board)
            .CountAsync();

        return result;
    }
    catch (Exception ex)
    {
        Crashes.TrackError(ex);

        return -1;
    }
}

Using the local database

Our local database API is now complete, so we can replace the use of the cloud database with the local implementaion.

If we go back to the App.xaml.cs file, we can switch the type of the Database property to LocalDatabase:

// public static CloudDatabase Database { get; } = new CloudDatabase();
public static LocalDatabase Database { get; } = new LocalDatabase();

If we run the game now, we will see that it still functions the same, but this time it is using a local SQLite database instead of the Azure Cosmos DB instance.

Clone this wiki locally