How To Maintain SQLite Database State in Xamarin Forms Between Deploys/Builds

Question

I have a Xamarin Forms app that is using SQLite to persist data with a Repository layer abstraction on top. All of this works fine, but I cannot save the database between deploys/builds, either in the emulator or using a physical device (Android).

My App.xaml.cs currently initialises the DB like so:

var sqlite = Container.Resolve<ISQLiteConnectionService>();
var repository = Container.Resolve<IPersonRepository>();

if (!repository.DatabaseExists())
{
    sqlite.CreateDatabase();
}

if (repository.GetLoggedInUserOrReturnNull() != null)
{
    await NavigationService.NavigateAsync("NavigationPage/ActivityFeed");
}
else
{
    await NavigationService.NavigateAsync("NavigationPage/Login");
}

The DatabaseExists check is as follows:

try
{
    var dbConnection = SQLiteConnectionService.GetAsyncConnection();

    return dbConnection.TableMappings.Any(m => m.MappedType.Name == typeof(PersonDBModel).Name);
}

The connection service class looks as follows:

public class SqliteConnectionService : ISQLiteConnectionService
{
    private readonly string _databasePath;
    private SQLiteAsyncConnection _sqLiteAsyncConnection;
    private SQLiteConnection _sqLiteConnection;

    public SqliteConnectionService(IFileHelper fileHelper)
    {
        if (fileHelper == null)
        {
            throw new ArgumentNullException(nameof(fileHelper));
        }

        _databasePath = fileHelper.GetLocalFilePath(Constants.DB_NAME);

        if (!_databasePath.Contains("UWP"))
        {
            if (File.Exists(_databasePath))
            {
                File.Delete(_databasePath);
            }

            File.Create(_databasePath);
        }
    }

    public SQLiteAsyncConnection GetAsyncConnection()
    {
        return _sqLiteAsyncConnection ?? (_sqLiteAsyncConnection = new SQLiteAsyncConnection(_databasePath, SQLiteOpenFlags.Create |
                                                                                                            SQLiteOpenFlags.SharedCache |
                                                                                                            SQLiteOpenFlags.ReadWrite, false));
    }

    public SQLiteConnection GetConnection()
    {
        return _sqLiteConnection ?? (_sqLiteConnection = new SQLiteConnection(_databasePath, SQLiteOpenFlags.Create |
                                                                                             SQLiteOpenFlags.SharedCache |
                                                                                             SQLiteOpenFlags.ReadWrite, false));
    }

    public async Task CreateDatabaseAsync()
    {
        var dbConnection = GetAsyncConnection();
        await dbConnection.CreateTableAsync<PrivacySettingsDBModel>();
        await dbConnection.CreateTableAsync<PersonDBModel>();
        ...
    }

    public void CreateDatabase()
    {
        var dbConnection = GetConnection();
        dbConnection.CreateTable<PrivacySettingsDBModel>();
        dbConnection.CreateTable<PersonDBModel>();
        ...
    }
}

The path to the SQLite .db3 file is then determined by a file helper created for each platform but for Android in particular:

public class FileHelper : IFileHelper
{
    public string GetLocalFilePath(string filename)
    {
        if (string.IsNullOrWhiteSpace(filename))
        {
            throw new ArgumentException("Value cannot be null or white space.", nameof(filename));
        }

        var path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);

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

I can't see any reason why this database file is not persisted between app restarts or app redeploys.

The option:

Tools -> Xamarin -> Android Settings -> Preserve application data cache on device between deploys

is checked, so I'm stumped.

Has anybody come across this issue, or is there an obvious issue in the code?

Answer 1

Turns out it was user error!

Although I was having issues with the database on UWP.

The revised code for the connection service is as simple as:

_databasePath = fileHelper.GetLocalFilePath(Constants.DB_NAME);

if (!File.Exists(_databasePath))
{
    File.Create(_databasePath);
}

Which also seems to now work for UWP.

Either the Xamirin.Forms update fixed the issue or my machine was playing up.

Hopefully this helps anyone looking to use Prism and SQLite.