Asynchronous Access vs Asynchronous SQL

Question

Could someone explain to me why this is asynchronous in SQL, but not in OleDb?

And maybe how to fix it please.

SQL

static void Main(string[] args)
{
    var task = Run();
    while(!task.IsCompleted)
        Console.WriteLine("Hmm");
    task.Wait();
}

private static async Task Run()
{
    string conString = @" ... ";
    var con = new SqlConnection(conString);
    con.Open();
    using(var command = new SqlCommand("SELECT * FROM Products2;", con))
    {
        command.Parameters.Add(new SqlParameter("p", 337));
        using(var reader = await command.ExecuteReaderAsync())
            while(await reader.ReadAsync())
                Console.WriteLine(reader.GetString(2));
    }
}

ACCESS

    static void Main(string[] args)
    {
        var task = Run();
        while(!task.IsCompleted)
            Console.WriteLine("Hmm");
        task.Wait();
    }

    private static async Task Run()
    {
        string conString = @" ... ";
        var con = new OleDbConnection(conString);
        con.Open();
        using(var command = new OleDbCommand("SELECT * FROM Products2;", con))
        {
            command.Parameters.Add(new OleDbParameter("p", 337));
            using(var reader = await command.ExecuteReaderAsync())
                while(await reader.ReadAsync()) //Note - Calling Async
                    Console.WriteLine(reader.GetString(2));
        }
    }

Just so you don't have to copy this in yourself, during execution of SQL, it will print the result and "Hmm" side by side. In OleDb is just prints the result.

---

I can fix it by wrapping await Task.Run(() => Console.WriteLine(reader.GetString(2)));, but that just produces other errors.

---

Result from SQL

Answer 1

Could someone explain to me why this is asynchronous in SQL, but not in OleDb?

It's up to the ADO.NET provider whether it implements asynchronous methods or not. If a provider doesn't support asynchrony, then the asynchronous methods just run synchronously.

And maybe how to fix it please.

If your real application is on ASP.NET, just continue to call them asynchronously; they'll run synchronously, but there isn't anything you can do about it, and if they're upgraded in the future to support asynchrony, your code will automagically use that new capability.

If your real application is a UI app, then you will need to wrap your database code inside a background thread (e.g., Task.Run). It's up to you whether to use asynchronous or synchronous APIs in that case; either way, I'd be sure to leave a comment explaining that the ADO.NET provider executes synchronously and that's why it's in a background thread.