Closing an SQLiteAsyncConnection

Question

I am using the sqlite-net-pcl library in my Xamarin Android project (the one by Frank Krueger, not this fork by Øystein Krog). It seems that as of now, the SQLiteAsyncConnection does not have a Close or Dispose method according to the author.

I have two questions. First, would using the connection as a singleton conserve resources? Second can I use sqliteAsyncConnection.GetConnection().Close() to close a connection?

I plan to do something like:

public static async Task CloseDbConnectionAsync(SQLiteAsyncConnection dbConnection)
{
    await Task.Factory.StartNew(() => dbConnection.GetConnection().Close());
}

Answer 1

To answer my own question in two parts:

a) Would using the [SQlite database] connection as a singleton conserve resources?

Yes, according to this article, making your database instance a singleton instance across the entire application’s life cycle can prevent you from opening too many connections and forgetting to close them.

This is how I implemented it in C#:

private static SQLiteAsyncConnection _conn;
private static readonly AsyncLock _mutex = new AsyncLock();

private static async Task<SQLiteAsyncConnection> GetDb(Context context)
{
    try
    {
        using (await _mutex.LockAsync())
        {
            if (_conn != null)
            {
                return _conn;
            }

            _conn = new SQLiteAsyncConnection(GetDbPath(context), storeDateTimeAsTicks: false);

            return _conn;
        }
    }
    catch (Exception e)
    {
        throw;
    }
}

The AsycnLock is part of the Nito.AsyncEx library.

b) Second can I use sqliteAsyncConnection.GetConnection().Close() to close a connection?

The author of the library has responded me here. This is currently what my disposing method looks like (though I'm not using it anywhere).

private static async Task DisposeDbConnectionAsync()
{
    using (await _mutex.LockAsync())
    {
        if (_conn == null)
        {
            return;
        }

        await Task.Factory.StartNew(() =>
        {
            _conn.GetConnection().Close();
            _conn.GetConnection().Dispose();
            _conn = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
        });
    }
}

The reason I'm calling the GC is because I am not sure that the dispose method releases the connection immediately or until the GC runs.