How to use SQLITE async extensions within a transaction in a Xamarin Forms PCL?

Question

I'm trying to figure out how, in a transaction, to use SQLITE async extension methods such as InsertOrReplaceWithChildrenAsync (NuGet from TwinCoders).

On startup in my Xamarin.Forms PCL, I have:

using Xamarin.Forms;
using SQLite.Net;
using SQLite.Net.Async;
using SQLiteNetExtensions;
using SQLiteNetExtensionsAsync.Extensions;
...
...
static SQLiteAsyncConnection db;
db = DependencyService.Get<ISQLite> ().GetAsyncConnection ();

As a side note, I could additionally do the following, but I don't, having read that it is unwise to mix sync and async database operations:

dbSync = DependencyService.Get<ISQLite> ().GetSyncConnection ();

My first idea was:

await db.RunInTransactionAsync ( 
    ( SQLite.Net.Async.SQLiteAsyncConnection tran ) => 
        { tran.InsertAll WithChildren ( ...); ... }

but this gets the following warning at build time:

... RunInTransactionAsync(Action<SQLiteAsyncConnection> ... will 
cause a deadlock if .... 
Use RunInTransactionAsync(Action<SQLiteConnection>) instead.

If I use synchronous methods within the transaction, the following does work:

await db.RunInTransactionAsync ( ( SQLite.Net.SQLiteConnection tran ) =>
{
    var x = new X();
    tran.Insert (x);
    var y = new Y();
    tran.Insert (y);
});

However, tran has only the regular synchronous methods, and does not offer any of the extension methods such as InsertOrReplaceWithChildren.

My searches have not turned up any information on how to access extension methods within the transaction.

If I obtain dbSync as above, dbSync does not offer the extension methods. Maybe that has something to do with the problem. In my companion IOS project I have:

using Xamarin.Forms;
using SQLite.Net;
using SQLite.Net.Async;
using SQLite.Net.Platform.XamarinIOS;
...
...
public class Conn : Xyz.ISQLite
{
    ...
    public SQLite.Net.SQLiteConnection GetSyncConnection () { ... }
    public SQLiteAsyncConnection GetAsyncConnection() { ... }
    ...
}

It would appear that adding the async packages somehow blocks the synchronous extensions. However adding a PCL helper class library that has only the synchronous SQLite.Net and synchronous extensions, and fetching the sync connection from there, did not help.

BillF BillF · Accepted Answer · 2015-04-14T13:44:21

Guillermo Gutiérrez Doral resolved this as follows:

"Due to the nature of SQLite.Net async, only synchronous operations are supported inside a transaction. Otherwise, any failed operation between the begin and the end of the transaction may make the transaction to rollback even when the operation was not inside the transaction block. This is not a SQLite-Net Extensions limitation, but a SQLite.Net one. You can still access to all SQLite-Net Extensions synchronous operations, like InsertOrReplaceWithChildren."

https://bitbucket.org/twincoders/sqlite-net-extensions/issue/61/async-extension-methods-within-a

How to use SQLITE async extensions within a transaction in a Xamarin Forms PCL?

1 Answers