Using SQLite with async in C#

Question

I am trying to get my head around the async/await keywords and usage, and I think I got the basics. But something isn't working right in my SQLite code.

I am using the SQLite.core NuGet package in a simple project I've been working on. I noticed that the async code I wrote does not behave asynchronously (like I expected), so I created a simpler test project to test my understanding.

In my test code, I open a connection to an in-memory database (I have the same problem with a file-based database. In-memory was just easier in the test code), and issue a single "create table" command, using ExecuteNonQueryAsync. I don't immediately await for the result, but instead write something to the console, before finally using the await keyword.

I expect the console command to be executed before the ExecuteNonQueryAsync finishes, so in my test I should see "1 2 3 4". But instead I get "1 3 2 4"

I run an identical test using an SQL Server LocalDB connection (running the same code, only the DbConnection is different), and get the expected "1 2 3 4". So I guess my basic understanding of the async is not that far off from the mark.

What am I missing? Do I need to use a special connection string with the SQLite in order to support the async methods? Does it even support it?

My full test project can be found here.

And here is the main program itself:

 namespace DatabaseTest
   {
    using System;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Data.SQLite;
    using System.Threading.Tasks;
class Program
{
    static void Main(string[] args)
    {
        Task.WaitAll(TestDatabase(true), TestDatabase(false));
    }

    private static async Task TestDatabase(bool sqLite)
    {
        Console.WriteLine("Testing database, sqLite: {0}", sqLite);
        using (var connection = CreateConnection(sqLite))
        {
            connection.Open();
            var task = ExecuteNonQueryAsync(connection);
            Console.WriteLine("2");
            await task;
            Console.WriteLine("4");
        }
    }

    private static DbConnection CreateConnection(bool sqLite)
    {
        return sqLite ?
            (DbConnection)new SQLiteConnection(string.Format("Data Source=:memory:;")) :
            new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30");
    }

    private static async Task ExecuteNonQueryAsync(DbConnection connection)
    {
        var command = connection.CreateCommand();
        command.CommandText = "CREATE TABLE test (col1 integer);";
        Console.WriteLine("1");
        await command.ExecuteNonQueryAsync();
        Console.WriteLine("3");
    }
}

And the output:

Testing database, sqLite: True
1
3
2
4
Testing database, sqLite: False
1
2
3
4

Answer 1

System.Data.SQLite implementation is 100% synchronous. They don't have any async overloads, and that's Microsoft to blame for this misconception, as SQLiteCommand extends System.Data.Common.DbCommand with default implementations of *Async methods that just call synchronous versions:

/// <summary>This is the asynchronous version of <see cref="M:System.Data.Common.DbCommand.ExecuteNonQuery" />. Providers should override with an appropriate implementation. The cancellation token may optionally be ignored.The default implementation invokes the synchronous <see cref="M:System.Data.Common.DbCommand.ExecuteNonQuery" /> method and returns a completed task, blocking the calling thread. The default implementation will return a cancelled task if passed an already cancelled cancellation token.  Exceptions thrown by <see cref="M:System.Data.Common.DbCommand.ExecuteNonQuery" /> will be communicated via the returned Task Exception property.Do not invoke other methods and properties of the <see langword="DbCommand" /> object until the returned Task is complete.</summary>
/// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
/// <returns>A task representing the asynchronous operation.</returns>
/// <exception cref="T:System.Data.Common.DbException">An error occurred while executing the command text.</exception>
public virtual Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken)
{
  ...
  return Task.FromResult<int>(this.ExecuteNonQuery());
  ...
}

I just figured that the same hard way, and I'm not happy with approach they took, but that's what we got. Just for the record, I think that there should be NotSupportedException.

Answer 2

Once you have started an asynchronous task, both that task and the main thread can continue to run. So there is no guarantee which one runs faster.

SQLite is an embedded database with no client/server communication overhead, and, as a library, runs on the same CPU. So this implementation might have decided that actually supporting asynchronous execution would not make sense.