Chris Wohlert Chris Wohlert - 3 years ago 130
SQL Question

Asynchronous Access vs Asynchronous SQL

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

Result from SQL

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download