Local Databases

PDF for offline use
Interactive:
Sample Code:
Related Samples:

Let us know how you feel about this

Translation Quality


0/250

last updated: 2016-12

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 Portable Class Library (PCL) project of the Xamarin.Forms solution, with platform-specific projects returning a path to where the database will be stored.

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

This section shows how to add the SQLite.Net NuGet packages to a Xamarin.Forms solution, write methods to perform database operations, and use the DependencyService to determine a location to store the database on each platform.

Xamarins.Forms PCL Project

To add SQLite support to a Xamarin.Forms PCL project, right-click and choose Manage NuGet Packages...:

Manage NuGet Packages

Use NuGet's search function to find SQLite.Net PCL and install the package as shown:

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

Once the reference has been added, write an interface to abstract the platform-specific functionality, which is to determine the location of the database file. The interface used in the sample defines a single method:

public interface IFileHelper
{
  string GetLocalFilePath(string filename);
}

Once the interface has been defined, use the DependencyService to obtain an implementation and get a local file path (note that this interface has not been implemented yet). The following code gets an implementation in the App.Database property:

static TodoItemDatabase database;

public static TodoItemDatabase Database
{
  get
  {
    if (database == null)
    {
      database = new TodoItemDatabase(DependencyService.Get<IFileHelper>().GetLocalFilePath("TodoSQLite.db3"));
    }
    return database;
  }
}

The TodoItemDatabase constructor is shown below:

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

This approach creates a single database connection that is 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 the Using SQLite.NET article):

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);
}
ℹ️

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.

All the the data access code is written in the PCL project to be shared across all platforms. Only getting a local file path for the database requires platform-specific code, as outlined in the following sections.

iOS Project

To configure the Xamarin.iOS application, add the same NuGet package to the iOS project. This screenshot shows the NuGet window:

The only code required is the IFileHelper implementation that determines the data file path. The following code places the SQLite database file in the Library/Databases folder within the application's sandbox. See the iOS Working with the File System documentation for more information on the different directories that are available for storage.

[assembly: Dependency(typeof(FileHelper))]
namespace Todo.iOS
{
    public class FileHelper : IFileHelper
    {
        public string GetLocalFilePath(string filename)
        {
            string docFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            string libFolder = Path.Combine(docFolder, "..", "Library", "Databases");

            if (!Directory.Exists(libFolder))
            {
                Directory.CreateDirectory(libFolder);
            }

            return Path.Combine(libFolder, filename);
        }
    }
}

Note that the code includes the assembly:Dependency attribute so that this implementation is discoverable by the DependencyService.

Android Project

Add the same SQLite.Net PCL library to the Android project, as shown in this NuGet window screenshot:

Once this reference has been added, the only code required is the IFileHelper implementation that determines the data file path.

[assembly: Dependency(typeof(FileHelper))]
namespace Todo.Droid
{
    public class FileHelper : IFileHelper
    {
        public string GetLocalFilePath(string filename)
        {
            string path = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            return Path.Combine(path, filename);
        }
    }
}

Windows 10 Universal Windows Platform (UWP)

Add the SQLite-net PCL NuGet to the UWP project by right-clicking and choosing Manage NuGet Packages....

Once the reference is added, implement the IFileHelper interface using the platform-specific Windows.Storage API to determine the data file path.

using Windows.Storage;
...

[assembly: Dependency(typeof(FileHelper))]
namespace Todo.UWP
{
    public class FileHelper : IFileHelper
    {
        public string GetLocalFilePath(string filename)
        {
            return Path.Combine(ApplicationData.Current.LocalFolder.Path, filename);
        }
    }
}

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 Data Access: Using SQLite.NET documentation. Most SQLite.Net code is sharable across all platforms; only configuring the location of the SQLite database file requires platform-specific functionality.

Xamarin Workbook

If it's not already installed, install the Xamarin Workbooks app first. The workbook file should download automatically, but if it doesn't, just click to start the workbook download manually.