Xamarin Forms Local Storage with SQLite -

In the process of rewriting the rental property cashflow calculator app from Android and Java to Xamarin, I have gotten to the point where the calculator part is working pretty much how I want and already has some improvements over the Java version.

Today I added the ability to save the properties to a local SQLite database.

Add a NuGet Package

Since this is a Xamarin Forms application, I am trying to write as little platform specific code as possible. The SQLite-net PCL NuGet package helps significantly in this regard.

You will end up adding it to your main Forms project as well as to each of the platform specific projects that you want to build for.

FileHelper Interface and Implementation

The only thing you end up needing platform specific code for when using this package is for implementing the interface to find where the SQLite file is stored on the device.

In the Forms project, add a new interface called IFileHelper that looks like this:


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

Then in your YourAppName.Droid project, you would create a new class that implements this interface. It would look something like this:


using System;
using System.IO;
using Xamarin.Forms;
using YourAppName.Droid;

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

Note the [assembly …] part of the code. This is a metadata attribute that registers this class as a FileHelper to the DependencyService. If you do not register your interface, the DependencyService will not be able to find it at run time and will throw an error.

Creating a Model

The easiest way of handling the data that goes in and out of your database is with a class that models all of the attributes of your SQLite table. In my case that is a property. Your model will be a basic class with a few fields. You will most likely want an ID field that auto increments. Here is the Property model I used for an example.


using SQLite;
namespace YourAppName
{
    public class Property
    {
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string Name { get; set; }
        public string Value { get; set; }
        public string Mortgage { get; set; }
        public string Rent { get; set; }
        public string Vacancy { get; set; }
        public string Repair { get; set; }
        public string Management { get; set; }
        public string Tax { get; set; }
    }
}

Creating Your Database Class

Now that you have your model and file helpers, you will most likely want a class to handle common operations for your Model like fetching multiple records, adding a new record, fetching a single record, and deleting a single record.

Here is an example of a class that handles these actions.


    public class PropertyDatabase
    {
        readonly SQLiteAsyncConnection database;

        public PropertyDatabase(string db_path)
        {
            database = new SQLiteAsyncConnection(db_path);
            database.CreateTableAsync().Wait();
        }

        public Task> GetPropertiesAsync()
        {
            return database.Table().ToListAsync();
        }

        public Task GetPropertyAsync(int id)
        {
            return database.Table().Where(p => p.ID == id).FirstOrDefaultAsync();
        }

        public Task SavePropertyAsync(Property property)
        {
            if (property.ID != 0)
            {
                return database.UpdateAsync(property);
            }
            else {
                return database.InsertAsync(property);
            }
        }

        public Task DeletePropertyAsync(Property property)
        {
            return database.DeleteAsync(property);
        }
    }

You may want to add the ability to get a specific set of records, for example all properties that have a value above or below a certain amount.

Also notice that we create an asynchronous database connection that is only designed to be opened once and left open waiting for commands during the lifecycle of the app. This avoids the overhead of creating a new database connection for every operation.

Setting It All Up

Now that we have a class set up to allow us to easily access the database, we need to create an instance of it and start performing some operations.


        static PropertyDatabase database;

        public static PropertyDatabase Database
        {
            get
            {
                if (database == null)
                {
                    database = new PropertyDatabase(DependencyService.Get().GetLocalFilePath("YourAppNameSQLite.db3"));
                }
                return database;
            }
        }

Here we use the DependencyService to ask for the platform specific FileHelper that we created earlier so we can access the SQLite database file.

Now you are all ready to start using the database instance. For an example, I created a Save Property button that calls the following method on the ‘Clicked’ event:


        void SaveProperty(object sender, EventArgs e)
        {
            var property = new Property();
            property.ID = property_id;
            property.Value = property_value.Text;
            property.Mortgage = mortgage.Text;
            property.Rent = monthly_rent_cash.Text;
            property.Vacancy = vacancy_cash.Text;
            property.Repair = repair_cash.Text;
            property.Management = property_management_cash.Text;
            property.Tax = property_tax_cash.Text;

            Database.SavePropertyAsync(property);
        }

My next step is to create a page for the app that lists the currently saved properties. This should be pretty straightforward with the PropertyDatabase class I created and a ListView.

Xamarin Tutorials and Documentation

If you get stuck, the tutorials on Xamarin’s Developer Center were very helpful for learning this and the source code for their ToDo app ended up being invaluable as well.

Also you can reach out to me at travis at evolvingdeveloper.com