Xamarin.Forms Local Databases

Xamarin.Forms supports database-driven applications using the SQLite database engine, which makes it possible to load and save objects in shared code. This article describes how Xamarin.Forms applications can read and write data to a local SQLite database using SQLite.Net.

Overview

Xamarin.Forms applications can use the SQLite.NET PCL NuGet package to incorporate database operations into shared code by referencing the SQLite classes that ship in the NuGet. Database operations can be defined in the .NET Standard library project of the Xamarin.Forms solution.

The accompanying sample application is a simple Todo-list application. The following screenshots show how the sample appears on each platform:

Xamarin.Forms database example screenshots Xamarin.Forms database example screenshots

Using SQLite

To add SQLite support to a Xamarin.Forms .NET Standard library, use NuGet's search function to find sqlite-net-pcl and install the latest package:

Add NuGet SQLite.NET PCL Package

There are a number of NuGet packages with similar names, the correct package has these attributes:

  • Created by: Frank A. Krueger
  • Id: sqlite-net-pcl
  • NuGet link: sqlite-net-pcl

Note

Despite the package name, use the sqlite-net-pcl NuGet package even in .NET Standard projects.

Once the reference has been added, add a property to the App class that returns a local file path for storing the database:

static TodoItemDatabase database;

public static TodoItemDatabase Database
{
  get
  {
    if (database == null)
    {
      database = new TodoItemDatabase(
        Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "TodoSQLite.db3"));
    }
    return database;
  }
}

The TodoItemDatabase constructor, which takes the path for the database file as an argument, is shown below:

public TodoItemDatabase(string dbPath)
{
  database = new SQLiteAsyncConnection(dbPath);
  database.CreateTableAsync<TodoItem>().Wait();
}

The advantage of exposing the database as a singleton is that a single database connection is created that's kept open while the application runs, therefore avoiding the expense of opening and closing the database file each time a database operation is performed.

The remainder of the TodoItemDatabase class contains SQLite queries that run cross-platform. Example query code is shown below (more details on the syntax can be found in Using SQLite.NET with Xamarin.iOS.

public Task<List<TodoItem>> GetItemsAsync()
{
  return database.Table<TodoItem>().ToListAsync();
}

public Task<List<TodoItem>> GetItemsNotDoneAsync()
{
  return database.QueryAsync<TodoItem>("SELECT * FROM [TodoItem] WHERE [Done] = 0");
}

public Task<TodoItem> GetItemAsync(int id)
{
  return database.Table<TodoItem>().Where(i => i.ID == id).FirstOrDefaultAsync();
}

public Task<int> SaveItemAsync(TodoItem item)
{
  if (item.ID != 0)
  {
    return database.UpdateAsync(item);
  }
  else {
    return database.InsertAsync(item);
  }
}

public Task<int> DeleteItemAsync(TodoItem item)
{
  return database.DeleteAsync(item);
}

Note

The advantage of using the asynchronous SQLite.Net API is that database operations are moved to background threads. In addition, there's no need to write additional concurrency handling code because the API takes care of it.

Summary

Xamarin.Forms supports database-driven applications using the SQLite database engine, which makes it possible to load and save objects in shared code.

This article focused on accessing a SQLite database using Xamarin.Forms. For more information on working with SQLite.Net itself, refer to the SQLite.NET on Android or SQLite.NET on iOS documentation.