无法从未打开的数据库创建命令

我搜索了很多,我找不到任何答案.

我正在编写一个Xamarin Forms Mobile应用程序,似乎当我最小化应用程序然后重新打开它或我的一个活动启动时,会抛出以下异常:

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].b__9_0 ()
Task`1[TResult].InnerInvoke ()
Task.Execute ()

这是我的代码:

通用存储库(创建Sqlite实例的位置)

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

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

国际奥委会注册

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

在我的App.xaml.cs OnResume中

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

上面ResetPool我把它放进去看看它是否有所作为,但事实并非如此.

网址活动

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(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();
}

我不知道还能做什么或寻找什么,我似乎无法找到有关此类错误的任何信息.

更新:

经过更多调试后,似乎只有在Url活动完成后才会发生.我已从活动中删除了数据库代码,但它似乎仍然存在.一旦Activity启动了main,App()然后运行以下代码:

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

这是错误发生的地方.当Get()调用调用时会发生这种情况return _db.Table().ToListAsync();

我已经尝试使所有异步(没有帮助),使存储库,连接和我们做CreateTableAsync异步的地方仍然没有运气.

您正在进行同步阻塞调用,.Wait()并且.Result当与异步API混合时可能会导致死锁.

SQLiteAsyncConnection 意味着异步使用.

一个常见的工作是创建事件处理程序,以允许进行异步非阻塞调用.

例如,CreateTableAsync在存储库中调用时

public class Repository : IRepository 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(); //async non blocking call
    }

    //...
}

存储库抽象似乎有一个异步API,但有同步调用.

再次,这可能会导致死锁,不建议.

如果意图是具有响应式UI或使用非同步版本的SQLite.Net来进行同步调用,则需要将代码重构为异步 .

将URL活动重构为异步将按照与上面相同的格式进行重构.

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(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

同样从设计角度来看,连接的初始化应该从存储库中反转出来并在外部进行管理(SRP).

public interface ISQLiteAsyncProvider {
    SQLiteAsyncConnection GetConnection();
}

public class DefaultSQLiteAsyncProvider : ISQLiteAsyncProvider {
    private readonly Lazy connection;

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

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

使用的连接的异步延迟初始化的想法

/// 

/// Provides support for asynchronous lazy initialization.
/// 
/// 
public class LazyAsync : Lazy> {
    /// 

    ///  Initializes a new instance of the LazyAsync`1 class. When lazy initialization
    ///  occurs, the specified initialization function is used.
    /// 
    /// The delegate that is invoked to produce the lazily initialized Task when it is needed.
    public LazyAsync(Func> valueFactory) :
        base(() => Task.Run(valueFactory)) { }
}

这使得现在可以重构存储库以使用延迟初始化,这允许删除存储库中的事件处理程序

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

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

    private readonly LazyAsync connection;

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

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

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

    public async Task 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
    }
}

并注册了

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

感谢@Nkosi对他的见解和建议,非常感谢,但没有一个解决方案有效.

在拉下sqlite.net-pcl库(再次由OSS保存!)并通过它进行调试后,似乎每次我Activity启动时都会检查连接是否打开而不是,它是唯一的地方被打开的时候SqliteConnection是建造的时候.现在我写它的方式,它是一个单身,但愚蠢的我Repository实施IDisposable.所以我的IOC容器正在处理SqliteConnection它,但它从来没有重新创建它,因为它是一个单例.

TL; DR删除IDisposable了存储库上的实现,因为它SqliteConnection是一个单例.