在C#中使用SQLite和异步

我试图了解async/ await关键字和用法,我认为我得到了基础知识.但是我的SQLite代码中有些东西不能正常工作.

我在一个我一直在研究的简单项目中使用SQLite.core NuGet包.我注意到我写的异步代码不是异步的(就像我预期的那样),所以我创建了一个更简单的测试项目来测试我的理解.

在我的测试代码中,我打开了与内存数据库的连接(我对基于文件的数据库有同样的问题.内存在测试代码中更容易),并发出一个"create table"命令,使用ExecuteNonQueryAsync.我没有立即await得到结果,而是在最终使用await关键字之前向控制台写了一些东西.

我希望控制台命令在ExecuteNonQueryAsync完成之前执行,所以在我的测试中我应该看到"1 2 3 4".但相反,我得到"1 3 2 4"

我使用SQL Server LocalDB连接运行相同的测试(运行相同的代码,只是DbConnection不同),并获得预期的"1 2 3 4".所以我想我对它的基本理解async并不是那么遥远.

我错过了什么？我是否需要在SQLite中使用特殊的连接字符串才能支持这些async方法？它甚至支持它吗？

我的完整测试项目可以在这里找到.

以下是主程序本身:

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

并输出:

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

Andriy K.. 9

System.Data.SQLite实现是100%同步的.他们没有任何异步过载,这是微软应该为此负责的误解,如SQLiteCommand扩展System.Data.Common.DbCommand与*异步方法,只是调用同步版本的默认实现:

/// 
This is the asynchronous version of . Providers should override with an appropriate implementation. The cancellation token may optionally be ignored.The default implementation invokes the synchronous  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  will be communicated via the returned Task Exception property.Do not invoke other methods and properties of the  object until the returned Task is complete.
/// The token to monitor for cancellation requests.
/// A task representing the asynchronous operation.
/// An error occurred while executing the command text.
public virtual Task ExecuteNonQueryAsync(CancellationToken cancellationToken)
{
  ...
  return Task.FromResult(this.ExecuteNonQuery());
  ...
}

我只是想到同样艰难的方式,我对他们采取的方法不满意,但这就是我们得到的.只是为了记录,我认为应该有NotSupportedException.

System.Data.SQLite实现是100%同步的.他们没有任何异步过载,这是微软应该为此负责的误解,如SQLiteCommand扩展System.Data.Common.DbCommand与*异步方法,只是调用同步版本的默认实现:

/// 
This is the asynchronous version of . Providers should override with an appropriate implementation. The cancellation token may optionally be ignored.The default implementation invokes the synchronous  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  will be communicated via the returned Task Exception property.Do not invoke other methods and properties of the  object until the returned Task is complete.
/// The token to monitor for cancellation requests.
/// A task representing the asynchronous operation.
/// An error occurred while executing the command text.
public virtual Task ExecuteNonQueryAsync(CancellationToken cancellationToken)
{
  ...
  return Task.FromResult(this.ExecuteNonQuery());
  ...
}

我只是想到同样艰难的方式,我对他们采取的方法不满意,但这就是我们得到的.只是为了记录,我认为应该有NotSupportedException.