Sqlite-net: Support all Connection members in AsyncConnection

[praeclarum]

The library has gotten out of sync with itself.

(Yes, that was a pun.)

This was brought up in Issue #101

• [x] Void .ctor(String, Boolean)
• [x] Void .ctor(String, SQLiteOpenFlags, Boolean)
• [x] System.TimeSpan BusyTimeout
• [x] Void Close()
• [x] Int32 CreateIndex(System.String, System.String, Boolean)
• [x] Int32 CreateIndex(System.String, System.String, System.String, Boolean)
• [x] Int32 CreateIndex(System.String, System.String, System.String[], Boolean)
• [x] Int32 CreateIndex(System.String, System.String[], Boolean)
• [x] Int32 CreateIndex[T](System.Linq.Expressions.Expression_1[System.Func_2[T,System.Object]], Boolean)
• [x] Int32 CreateTable(System.Type, SQLite.CreateFlags)
• [x] Int32 CreateTable[T](SQLite.CreateFlags)
• [x] SQLite.CreateTablesResult CreateTables(SQLite.CreateFlags, System.Type[])
• [x] SQLite.CreateTablesResult CreateTables[T,T2,T3,T4,T5](SQLite.CreateFlags)
• [x] SQLite.CreateTablesResult CreateTables[T,T2,T3,T4](SQLite.CreateFlags)
• [x] SQLite.CreateTablesResult CreateTables[T,T2,T3](SQLite.CreateFlags)
• [x] SQLite.CreateTablesResult CreateTables[T,T2](SQLite.CreateFlags)
• [x] System.String DatabasePath
• [x] System.Collections.Generic.IEnumerable_1[System.Object] DeferredQuery(SQLite.TableMapping, System.String, System.Object[])
• [x] System.Collections.Generic.IEnumerable_1[T] DeferredQuery[T](System.String, System.Object[])
• [x] Int32 Delete(System.Object)
• [x] Int32 Delete(System.Object, SQLite.TableMapping)
• [x] Int32 Delete[T](System.Object)
• [x] Int32 DeleteAll(SQLite.TableMapping)
• [x] Int32 DeleteAll[T]()
• [x] Int32 DropTable(SQLite.TableMapping)
• [x] Int32 DropTable[T]()
• [x] Void EnableLoadExtension(Boolean)
• [x] Boolean Equals(System.Object)
• [x] Int32 Execute(System.String, System.Object[])
• [x] T ExecuteScalar[T](System.String, System.Object[])
• [x] System.Object Find(System.Object, SQLite.TableMapping)
• [x] T Find[T](System.Linq.Expressions.Expression_1[System.Func_2[T,System.Boolean]])
• [x] T Find[T](System.Object)
• [x] System.Object FindWithQuery(SQLite.TableMapping, System.String, System.Object[])
• [x] T FindWithQuery[T](System.String, System.Object[])
• [x] System.Object Get(System.Object, SQLite.TableMapping)
• [x] T Get[T](System.Linq.Expressions.Expression_1[System.Func_2[T,System.Boolean]])
• [x] T Get[T](System.Object)
• [x] Int32 GetHashCode()
• [x] SQLite.TableMapping GetMapping(System.Type, SQLite.CreateFlags)
• [x] SQLite.TableMapping GetMapping[T](SQLite.CreateFlags)
• [x] System.Collections.Generic.List_1[SQLite.SQLiteConnection+ColumnInfo] GetTableInfo(System.String)
• [x] System.Type GetType()
• [x] Int32 Insert(System.Object)
• [x] Int32 Insert(System.Object, System.String)
• [x] Int32 Insert(System.Object, System.String, System.Type)
• [x] Int32 Insert(System.Object, System.Type)
• [x] Int32 InsertAll(System.Collections.IEnumerable, Boolean)
• [x] Int32 InsertAll(System.Collections.IEnumerable, System.String, Boolean)
• [x] Int32 InsertAll(System.Collections.IEnumerable, System.Type, Boolean)
• [x] Int32 InsertOrReplace(System.Object)
• [x] Int32 InsertOrReplace(System.Object, System.Type)
• [x] Int32 LibVersionNumber
• [x] System.Collections.Generic.List_1[System.Object] Query(SQLite.TableMapping, System.String, System.Object[])
• [x] System.Collections.Generic.List_1[T] Query[T](System.String, System.Object[])
• [x] Boolean StoreDateTimeAsTicks
• [x] System.Collections.Generic.IEnumerable_1[SQLite.TableMapping] TableMappings
• [x] Boolean TimeExecution
• [x] System.String ToString()
• [x] Boolean Trace
• [x] System.Action_1[System.String] Tracer
• [x] Int32 Update(System.Object)
• [x] Int32 Update(System.Object, System.Type)
• [x] Int32 UpdateAll(System.Collections.IEnumerable, Boolean)

0 differences

[aznveriva]

@praeclarum In the meantime can I use sqliteAsyncConnection.GetConnection().Close() to close a connection?

[aznveriva]

This is how I'm disposing the connection right now:

````
private static async Task DisposeDbConnectionAsync()
{
using (await _mutex.LockAsync()) //from the Nito.AsyncEx library
{
if (_conn != null)
{
await Task.Factory.StartNew(
() =>
{
_conn.GetConnection().Close();
_conn.GetConnection().Dispose();
_conn = null;

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

}
````

[praeclarum]

@aznveriva technically that's risky since the connections could be pooled. Practically speaking though, yeah you should be OK since I think we limited the pool to 1 instance.

[glen-nicol]

I have a wrapper class around async connection as follows:

internal sealed class SqliteAdapter : ISqliteAdapter
{
    private SQLiteAsyncConnection _sqlite;

    public SqliteAdapter(SQLiteAsyncConnection sql)
    {
        _sqlite = sql;
    }


    public async Task<IEnumerable<T>> EnumerateTable<T>() where T: new()
    {
        return await _sqlite.Table<T>().ToListAsync().DetachContext();
    }


    public void Dispose()
    {
        var c = Interlocked.Exchange(ref _sqlite, null);
        if (c != null)
        {
            c.GetConnection().Close();
            c.GetConnection().Dispose();
            c = null;
            GC.SuppressFinalize(this);
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }
}

I get a SQLiteException: Cannot create commands from unopened database
when I run the following test on the adapter. It seems equivalent to me as the example above

[Test]
public async Task CanBeCreatedTwice()
{
    var sqlFile = Path.Combine(TestContext.CurrentContext.TestDirectory, "TestFiles/db.sqlite3");
    var adapter = new SqliteAdapter(new SQLiteAsyncConnection(sqlFile));
    var products = await adapter.EnumerateTable<FlatTableView>();
    adapter.Dispose();
    adapter = new SqliteAdapter(new SQLiteAsyncConnection(sqlFile));
    products = await adapter.EnumerateTable<FlatTableView>(); //thrown from here
}

I only need to open one db at a time in my application so I am not concerned about the pool. I do however need to be able to close it on demand and reopen it later. Is that possible with the current state of the library?

[ghost]

Any chance these connection methods will get added soon? We're migrating our apps to use this library due to the issue with sqlite access on newer versions of Android and we need to be able to close async connections. I don't mind whipping up a pull request if you can just tell me what needs to be done.

[ghost]

Also, I just ran across public static void ResetPool()on SQLiteAsyncConnection and using that method (as a replacement for closing the connection) seems to fix @glen-nicol's issue with the SQLiteException.

[aphex3k]

+1

[snalesso]

+1 & UP for the Async 🙏

@praeclarum

[sunthx]

+1

[rraallvv]

Is this solved? I'm still getting the error when I try to close the connection in OnSleep to reopen it in OnStart/OnResume in a Xamarin.Forms application running on Android.

[Weldryn]

I got the same error. My context is a service bound to the main activity to which a separate async connection is cached.
When I start the app the first time everything is fine. If i send the app in the background et bring it back, I got the exception below when I try to recreate the connection. I create the connection insinde the OnCreate and I close it inside OnDestroy. I use the ReadWrite | Create | SharedCache | FullMutex flags to open the connection.

  Cannot create commands from unopened database
  at SQLite.SQLiteConnection.CreateCommand (System.String cmdText, System.Object[] ps) [0x00013] in <9de4e78befac41be9b6c4887487b1037>:0 
  at SQLite.SQLiteConnection.Execute (System.String query, System.Object[] args) [0x00000] in <9de4e78befac41be9b6c4887487b1037>:0 
  at SQLite.SQLiteConnection.CreateTable (System.Type ty, SQLite.CreateFlags createFlags) [0x0012e] in <9de4e78befac41be9b6c4887487b1037>:0 
  at SQLite.SQLiteAsyncConnection+<>c__DisplayClass11_0.<CreateTablesAsync>b__0 () [0x0002b] in <9de4e78befac41be9b6c4887487b1037>:0 
  at System.Threading.Tasks.Task`1[TResult].InnerInvoke () [0x0000f] in <f32579baafc1404fa37ba3ec1abdc0bd>:0 
  at System.Threading.Tasks.Task.Execute () [0x00010] in <f32579baafc1404fa37ba3ec1abdc0bd>:0