Can the System.Data.Odbc driver support async IO?

[madelson]

Description

Today, System.Data.Odbc doesn't override async methods like DbCommand.ExecuteNonQueryAsync(...), and as a result all execution is synchronous.

This is of interest because ODBC can be used to query Apache Impala and Apache Hive from .NET using the ADO.NET framework.

Reproduction Steps

using OdbcConnection conn = new("..."); // connect to SQL server
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = "WAITFOR DELAY '00:00:10';"
using CancellationTokenSource cts = new();
var executeTask = cmd.ExecuteNonQueryAsync(cts.Token); // runs synchronously for 10s
cts.Cancel();
await executTask; // not canceled

Expected behavior

It would be nice if this used async IO under the hood. [https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/asynchronous-execution-notification-method?view=sql-server-ver16](these docs) seem to suggest it is possible, but I'm not an Odbc expert...

Actual behavior

Falls back to sync implementation.

Regression?

No

Known Workarounds

You can force async execution using Task.Run, but this blocks a thread.

Configuration

Windows + .NET 6

Other information

No response

[ghost]

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

Issue Details

---

Description

Today, System.Data.Odbc doesn't override async methods like DbCommand.ExecuteNonQueryAsync(...), and as a result all execution is synchronous.

This is of interest because ODBC can be used to query Apache Impala and Apache Hive from .NET using the ADO.NET framework.

Reproduction Steps

using OdbcConnection conn = new("..."); // connect to SQL server
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = "WAITFOR DELAY '00:00:10';"
using CancellationTokenSource cts = new();
var executeTask = cmd.ExecuteNonQueryAsync(cts.Token); // runs synchronously for 10s
cts.Cancel();
await executTask; // not canceled

Expected behavior

It would be nice if this used async IO under the hood. [https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/asynchronous-execution-notification-method?view=sql-server-ver16](these docs) seem to suggest it is possible, but I'm not an Odbc expert...

Actual behavior

Falls back to sync implementation.

Regression?

No

Known Workarounds

You can force async execution using Task.Run, but this blocks a thread.

Configuration

Windows + .NET 6

Other information

No response

Author:	madelson
Assignees:	-
Labels:	area-System.Data
Milestone:	-

[ghost]

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

Issue Details

---

Description

Today, System.Data.Odbc doesn't override async methods like DbCommand.ExecuteNonQueryAsync(...), and as a result all execution is synchronous.

This is of interest because ODBC can be used to query Apache Impala and Apache Hive from .NET using the ADO.NET framework.

Reproduction Steps

using OdbcConnection conn = new("..."); // connect to SQL server
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = "WAITFOR DELAY '00:00:10';"
using CancellationTokenSource cts = new();
var executeTask = cmd.ExecuteNonQueryAsync(cts.Token); // runs synchronously for 10s
cts.Cancel();
await executTask; // not canceled

Expected behavior

It would be nice if this used async IO under the hood. [https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/asynchronous-execution-notification-method?view=sql-server-ver16](these docs) seem to suggest it is possible, but I'm not an Odbc expert...

Actual behavior

Falls back to sync implementation.

Regression?

No

Known Workarounds

You can force async execution using Task.Run, but this blocks a thread.

Configuration

Windows + .NET 6

Other information

No response

Author:	madelson
Assignees:	-
Labels:	untriaged, area-System.Data.Odbc
Milestone:	-

[roji]

@madelson this may be possible, but we'd have to do a thorough investigation to see if it can be well-supported on other platforms as well (Linux, Mac).

Note also that the ODBC drivers themselves would obviously also need to support this. This may raise a thorny compatibility question: if System.Data.Odbc switches to using ODBC async APIs, what happens if a driver is in use which doesn't support async? We'd need to fully understand this to avoid people suddenly breaking.

Overall, we haven't received any requests for ODBC async support over the years, so this hasn't been prioritized. For most cases where a managed .NET driver exists (ADO.NET), that's usually the preferred option anyway, and async tends to be supported in ADO.NET drivers.

[ashishoffline]

@roji It would be great if System.Data.Odbc supported async APIs. I'm using it with several databases where a native ADO.NET provider either doesn't exist or isn't available for .NET Core.