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