Adding a SQLite Database to iOS

My original thoughts about using a local SQLite database to store list data were to check and see if a database existed on the device at app startup. If it didn’t exist, I’d create a new database and populate it from a remote source. Using this newly-populated database I’d create the allItems list from the downloaded data (if the database already existed then the creation/population step would be skipped).

I started coding, googling as I went, and somewhere along the way the idea occurred to me that I could distribute a sample database with the app. On app startup, if a database did not already exist in a specified location, I’d copy the sample database from the app bundle (where it was read-only) into the specified read-write location and create the allItems list from this sample database. This sidestepped the need to have the remote cloud piece ready at the same time as the app (it also meant I could have my wife start field testing the app in “standalone” mode sooner rather than later and get some early feedback).

Using DB Browser for SQLite I created an ItemSQLite database with a single Item table.

DB-Browser-SQLite-1

I added the ItemSQLite.db3 database to the Viand project, setting it’s Build action  to Content and always copying it to the Output directory. So iOS could use it I added a link to the database to the Resources folder in the Viand.iOS project, set it Build action to BundleResource and flagged it as Do Not Copy to the Output directory.

I modified the Item class so it mirrored the database table columns, adding an ID property and attributes on the existing properties (the SQLite attributes control how the properties are stored in the underlying database).

public class Item : IComparable<Item>
{
	[PrimaryKey, AutoIncrement, Column("ID")]
	public int ID { get; set; }

	[Column("Name")]
	public string Name { get; private set; }

	[Column("Buy")]
	public bool Buy { get; set; }

	[Column("Quantity")]
	public int Quantity { get; set; }

	[Column("Label")]
	public string Label { get; private set; }

	// Constructor code removed for clarity
}

Xamarin recommends that you read and write data to a local SQLite database using, surprise, the Sqlite.Net database layer. For a Xamarin.Forms PCL solution you need to referencing the SQLite classes by adding the required NuGet packages to your projects.

I added the SQLite.Net-PCL NuGet package to the Viand and Viand.iOS projects by selecting the Packages folder in each project, right clicking, and selecting Add Packages.

NuGet-Add-Packages-SQLite.Net

Xamarin.Forms includes a DependencyService which allows you to resolve Interfaces to platform-specific implementations, accessing iOS, Android, and Windows Phone SDK features from a PCL (or Shared) project.

First I created an interface, ISQLite, with a single method, GetConnection.

public interface ISQLite
{
	SQLiteConnection GetConnection();
}

Then I created an ItemDatabase class in the Viand project whose constructor would call the platform-specific implementations of GetConnection using Xamarin.Form’s DependencyService. I also created a GetItems method which returns an enumerable list of Items from the database when called. (I also put the ISQLite interface at the end of the ItemDatabase class file for convenience.)

public class ItemDatabase
{
	private SQLiteConnection database; 

	public ItemDatabase()
	{
		database = DependencyService.Get<ISQLite>().GetConnection();
	}

	public IEnumerable<Item> GetItems()
	{
		return (from i in database.Table<Item>() select i).ToList();
	}

}

public interface ISQLite
{
	SQLiteConnection GetConnection();
}

The next step was to write the iOS implementation of GetConnection. Two items that might not be immediately apparent in the code below:

  • The assembly:Dependency attribute is required to make the code discoverable by the DependencyServices.
  • A parameterless constructor is required for the implementation to work with the DependencyServices.

The GetConnection method performs several functions:

  • Gets a path to a writeable directory in the application’s sandbox. There are different methods to do this depending on which iOS operating system the devices is running–iOS 7 or iOS 8.
  • Once we have a path to a writeable directory we create a folder for our SQLite database if it doesn’t exist.
  • We append the database name to the database directory pass it into CopyDatabaseIfNotExists. If the database file doesn’t exist at this location, the sample database is copied from the application bundle.
  • A new SQLiteConnection is created to this database and returned.
[assembly: Dependency(typeof(SQLite_iOS))]

namespace Viand.iOS
{
	public class SQLite_iOS : ISQLite
	{
		const string sqliteFilename = "ItemSQLite";

		public SQLite_iOS() {}

		public SQLiteConnection GetConnection()
		{
			string documentPath;
			string libraryPath;

			if (UIDevice.CurrentDevice.CheckSystemVersion(8, 0)) {
				var documentUrl = NSFileManager.DefaultManager.GetUrls(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomain.User)[0];
				documentPath = documentUrl.Path;
			} else {
				documentPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
			}

			libraryPath = Path.Combine(documentPath, "..", "Library", "Databases");

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

			var databasePath = Path.Combine(libraryPath, sqliteFilename);

			CopyDatabaseIfNotExists(databasePath);

			var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
			var connection = new SQLite.Net.SQLiteConnection(platform, databasePath);

			return connection;
		}

		private static void CopyDatabaseIfNotExists(string databasePath)
		{
			if (!File.Exists(databasePath)) {
				var existingDatabase = NSBundle.MainBundle.PathForResource(sqliteFilename, "db3");
				File.Copy(existingDatabase, databasePath);
			}
		}

	}
}

In the Viand project we modify Viand.cs, the App class, adding a singleton for our ItemDatabase

public static ItemDatabase Database
{
	get {
		if (database == null) database = new ItemDatabase();
		return database;
	}
}

And swap out our old call to GetSampleData for a new call to the database’s GetItems method.

public class App : Application
{
	public static ItemDatabase database;
	public List<Item> allItems;

	public App()
	{
//			allItems = GetSampleData();
		allItems = (List<Item>)Database.GetItems();
		this.Properties["Items"] = allItems;

		// The root page of your application
		MainPage = new NavigationPage(new TabPage());
	}

	// Other code removed for clarity

}

Some links that were helpful during this development.

This entry was posted in Side Projects, Software Development and tagged , , , . Bookmark the permalink.

One Response to Adding a SQLite Database to iOS

  1. Pingback: Adding a SQLite Database to Android | Objective.Me

Comments are closed.