Cannot create commands from unopened database

Question

I've searched around quite a lot and I cannot find any answers to this.

I am writing a Xamarin Forms Mobile application, it seems when I minimise the application and then reopen it or one of my activities get launched the following exception gets thrown:

SQLiteConnection.CreateCommand (System.String cmdText, System.Object[] ps)
SQLite.SQLiteException: Cannot create commands from unopened database
SQLiteConnection.CreateCommand (System.String cmdText, System.Object[] ps)
TableQuery`1[T].GenerateCommand (System.String selectionList)
TableQuery`1[T].GetEnumerator ()
System.Collections.Generic.List`1[T]..ctor (System.Collections.Generic.IEnumerable`1[T] collection) [0x00062] in :0
Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable`1[T] source)
AsyncTableQuery`1[T].<ToListAsync>b__9_0 ()
Task`1[TResult].InnerInvoke ()
Task.Execute ()

Here is my code:

Generic Repository (Where the Sqlite instance gets created)

public class Repository<T> : IRepository<T> where T : Entity, new()
{
     private readonly SQLiteAsyncConnection _db;

    public Repository(string dbPath)
    {
        _db = new SQLiteAsyncConnection(dbPath);
        _db.CreateTableAsync<T>().Wait();
    }
}

The IOC registration

FreshIOC.Container.Register<IRepository<Settings>>(new Repository<Settings>(dbPath)); // FreshIOC is a wrapper around TinyIOC

In my App.xaml.cs OnResume

protected override void OnResume()
{
    SQLiteAsyncConnection.ResetPool();
}

The above with ResetPool I put that in to see if it would make a difference but it did not.

URL Activity

protected override void OnCreate(Bundle bundle)
{
    base.OnCreate(bundle);

    var url = Intent.Data.ToString();
    var split = url.Split(new[] { "ombi://", "_" }, StringSplitOptions.RemoveEmptyEntries);
    if (split.Length > 1)
    {
        var dbLocation = new FileHelper().GetLocalFilePath("ombi.db3");
        var repo = new Repository<OmbiMobile.Models.Entities.Settings>(dbLocation);
        var settings = repo.Get().Result;
        foreach (var s in settings)
        {
            var i = repo.Delete(s).Result;
        }
        repo.Save(new Settings
        {
            AccessToken = split[1],
            OmbiUrl = split[0]
        });
    }

    Intent startup = new Intent(this, typeof(MainActivity));
    StartActivity(startup);
    Finish();
}

I am not sure what else to do or look for, I can't seem to find any information about this sort of error.

Update:

After more debugging it seems to only happen after the Url activity has finished. I have removed the DB code from the Activity and it still seems to happen. Once the Activity has launched the main App() then runs this code:

var repo = FreshIOC.Container.Resolve<IRepository<Settings>>();
try
{
    Task.Run(async () =>
    {
        settings = (await repo.Get()).FirstOrDefault();
    }).Wait();
}
catch (Exception e)
{
    Debug.WriteLine(e.Message);
    throw;
}

This where the error is happening. It happens when the Get() is called which calls return _db.Table<T>().ToListAsync();

I have tried making everything async (didn't help), making the repository, connection and where we do CreateTableAsync async and still no luck.

Answer 1

I had the same error, but was not due to Disposable implementation. For some unknown reason it broke if I had the following:

lock (locker)
{
    foreach (var item in database.Table<DBItems>()) //It broke on this line
    {
        //...
    }
}

So I changed the line to

foreach (var item in database.Table<DBItems>().ToList()) //Notice the "ToList()"

Problem solved...

Answer 2

Thanks for @Nkosi to his insight and advice, that was much appreciated but none of the solutions worked.

After pulling down the sqlite.net-pcl library (saved by OSS again!) and debugging through it, it seemed that every time my Activity launched there is a check to see if the connection was open and it wasn't, the only place it was being set to open is when the SqliteConnection was constructed. Now the way I wrote it, it was a singleton but stupidly my Repository<T> implemented IDisposable. So my IOC container was disposing of the SqliteConnection but it was never recreating it due to it being a singleton.

TL;DR removed IDisposable implementation on the repository because the SqliteConnection was a singleton.

Answer 3

You are making synchronous blocking calls like .Wait() and .Result that could potentially cause deadlocks when mixed with an asynchronous API.

SQLiteAsyncConnection was meant to be used asynchronously.

One common work around is to create event handlers that would allow for async non blocking calls to be made.

For example when calling CreateTableAsync in the repository

public class Repository<T> : IRepository<T> where T : Entity, new() {
     private readonly SQLiteAsyncConnection _db;

    public Repository(string dbPath) {
        _db = new SQLiteAsyncConnection(dbPath);
        createTable += onCreateTable; //Subscribe to event
        createTable(this, EventArgs.Empty); //Raise event
    }

    private event EventHandler createTable = delegate { };
    private async void onCreateTable(object sender, EventArgs args) {
        createTable -= onCreateTable; //Unsubscribe from event
        await _db.CreateTableAsync<T>(); //async non blocking call
    }

    //...
}

The repository abstraction appears to have an asynchronous API yet there are synchronous calls.

Again this can cause deadlock and is not advised.

The code needs to be refactored to be async all the way through if the intent is to have a responsive UI or use SQLite.Net, the non-async version, to make synchronous calls.

Refactoring of the URL Activity to be asynchronous would look like this following the same format as above.

protected override void OnCreate(Bundle bundle) {
    base.OnCreate(bundle);
    creating += onCreateCore; //subscribe to event
    creating(this, EventArgs.Empty); //raise event
}

private event EventHandler creating = delegate { };
private async void onCreateCore(object sender, EventArgs args) {
    creating -= onCreateCore; //unsubscribe to event
    var url = Intent.Data.ToString();
    var split = url.Split(new[] { "ombi://", "_" }, StringSplitOptions.RemoveEmptyEntries);
    if (split.Length > 1) {
        var dbLocation = new FileHelper().GetLocalFilePath("ombi.db3");
        var repo = new Repository<OmbiMobile.Models.Entities.Settings>(dbLocation);
        var settings = await repo.Get();
        foreach (var s in settings) {
            var i = await repo.Delete(s);
        }
        repo.Save(new Settings {
            AccessToken = split[1],
            OmbiUrl = split[0]
        });
    }

    Intent startup = new Intent(this, typeof(MainActivity));
    StartActivity(startup);
    Finish();
}

UPDATE

Also from a design perspective, the initialization of the connection should be inverted out of the repository and managed externally (SRP).

public interface ISQLiteAsyncProvider {
    SQLiteAsyncConnection GetConnection();
}

public class DefaultSQLiteAsyncProvider : ISQLiteAsyncProvider {
    private readonly Lazy<SQLiteAsyncConnection> connection;

    public DefaultSQLiteAsyncProvider(string path) {
        connection = new Lazy<SQLiteAsyncConnection>(() => new SQLiteAsyncConnection(path));
    }

    public SQLiteAsyncConnection GetConnection() {
        return connection.Value;
    }
}

Playing with the idea of an asynchronous lazy initialization for the connection using

/// <summary>
/// Provides support for asynchronous lazy initialization.
/// </summary>
/// <typeparam name="T"></typeparam>
public class LazyAsync<T> : Lazy<Task<T>> {
    /// <summary>
    ///  Initializes a new instance of the LazyAsync`1 class. When lazy initialization
    ///  occurs, the specified initialization function is used.
    /// </summary>
    /// <param name="valueFactory">The delegate that is invoked to produce the lazily initialized Task when it is needed.</param>
    public LazyAsync(Func<Task<T>> valueFactory) :
        base(() => Task.Run(valueFactory)) { }
}

This makes it possible now to refactor the repository to use lazy initialization, which allowed for the removal of the event handler in the repository

public class Repository<T> : IRepository<T> where T : Entity, new() {

    public Repository(ISQLiteAsyncProvider provider) {
        this.connection = new LazyAsync<SQLiteAsyncConnection>(await () => {
            var db = provider.GetConnection();
            await db.CreateTableAsync<T>();
            return db;
        });
    }

    private readonly LazyAsync<SQLiteAsyncConnection> connection;

    public async Task<List<T>> Get() {
        var _db = await connection.Value;
        return await _db.Table<T>().ToListAsync();
    }

    public async Task<T> Get(int id) {
        var _db = await connection.Value;
        return await _db.Table<T>().Where(x => x.Id == id).FirstOrDefaultAsync();
    }

    public async Task<int> Save(T entity) {
        var _db = await connection.Value;
        return entity.Id == 0 
            ? await _db.InsertAsync(entity) 
            : await_db.UpdateAsync(entity);
    }

    public async Task<int> Delete(T entity) {
        var _db = await connection.Value;
        return await _db.DeleteAsync(entity);
    }

    public void Dispose() {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing) {
        if (disposing) {
            // get rid of managed resources
        }
        // get rid of unmanaged resources
    }
}

And registered like

// same instance should be used for other repositories
var provider = new DefaultSQLiteAsyncProvider(dbPath); 
var settingsRepository = new Repository<Settings>(provider);
FreshIOC.Container.Register<IRepository<Settings>>(settingsRepository);