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

None of these are awaited, and therefore none of these should be async. They will both run on the same thread — Sasha, Jul 24 '17 at 13:42
I would but I dont have an environment to test it on at the moment :) but where you do `while !task.iscompleted` blocks the console thread writing hmm — Sasha, Jul 24 '17 at 13:45
This looks a lot like a sync over async anti pattern. I think you should read this [Can't specify the 'async' modifier on the 'Main' method of a console app](https://stackoverflow.com/questions/9208921/cant-specify-the-async-modifier-on-the-main-method-of-a-console-app). Async should be all the way up. — Liam, Jul 24 '17 at 13:51
This example is me trying to simplify an actual issue. Where everything is awaited properly and so on. — Chris Wohlert, Jul 24 '17 at 13:53
I mean, this isn't async, or at least your getting no benefit from async here, your using more threads and blocking, not releasing the UI thread. Just remove the async altogether or [implement it properly](https://stackoverflow.com/a/24601591/542251) — Liam, Jul 24 '17 at 13:57
@Liam Your proposed fix isn't any more async than the OP's code. Yes, it's better to not do a busy wait and to just call `Wait`, but that doesn't affect whether or not the code is asynchronous. Additionally the busy wait is clearly diagnostic code, rather than real code, so even that isn't really a problem here. Note that there is no UI thread, as this is a console app with no message loop created. — Servy, Jul 24 '17 at 14:06
Possibly related question [here](https://stackoverflow.com/q/38015089/2144390). — Gord Thompson, Jul 24 '17 at 15:42

score 1 · Accepted Answer · answered Jul 24 '17 at 14:02

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.

answered Jul 24 '17 at 14:02

Stephen Cleary

437,863
77
675
810

Thank you. It feels counterintuitive to not just throw unimplenemted exceptions. I have a oledb adapter that I might be able to hide some async calls in, since I dont wsnt Task.Run where sql also executes. It is a UI app umforunately. :) – Chris Wohlert Jul 24 '17 at 14:22
Do you kbow of any documentation verifying that tjis specific provider does not implement async? Just for bonus points ;) – Chris Wohlert Jul 24 '17 at 14:23
@ChrisWohlert: No, sorry. I just know how the architecture works, not the specific implementations. SQL is for-sure async-compatible, and they decided *not* to upgrade the Oracle one, and SQLite isn't async either. In your case, I'm not sure whether it's the OleDb-for-ADO.NET or the Access-for-OleDb component that is dropping the async support. – Stephen Cleary Jul 24 '17 at 15:38

Asynchronous Access vs Asynchronous SQL

1 Answers1