System.Data.SqlClient.SqlCommand has methods
BeginExecuteNonQuery
BeginExecuteReader
BeginExecuteXmlReader
and
EndExecuteNonQuery
EndExecuteReader
EndExecuteXmlReader
for asynchronous execution.
System.Data.IDbCommand only has
ExecuteNonQuery
ExecuteReader
ExecuteXmlReader
which are for synchronous operations only.
Is there any interface for asynchronous operations ?
In addition, why is there no BeginExecuteScalar ?
On
To solve exactly this problem I built a shim that calls async methods if they exist on IDbConnection.IDbCommand/IDataReader or call regular methods if they don't.
Source: https://github.com/ttrider/IDbConnection-Async
NuGet: https://www.nuget.org/packages/IDbConnection-Async/
Example:
using (IDbConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
IDbCommand command = connection.CreateCommand();
command.CommandText = "SELECT Name FROM Person;";
using (IDataReader reader = await command.ExecuteReaderAsync())
{
do
{
while (await reader.ReadAsync())
{
if (!await reader.IsDBNullAsync(0))
{
var name = reader.GetFieldValueAsync<string>(0);
Assert.IsNotNull(name);
}
}
} while (await reader.NextResultAsync());
}
}
No there are not interfaces for them
The reason why there is not a BeginExecuteScalar is because you probably won't need an async call to get one single value back which should be very quick
On
You may implement async behavior by your custom code, since it is not so complicated, as for your question - there is no any standard async operations for your goals.
On
I stumbled upon this question when I need to migrate my data calls to async methods. I've created an issue for future .NET Standard to incorporate async interface. In the mean time, I've also created a library with a set of interfaces and adapters for System.Data.
On
Actually, creating async behavior equivalent to BeginExecuteNonQuery, EndExecuteNonQuery, etc. would be rather difficult task. Implementation of these APIs are far superior to simple spawning a separate thread, waiting for the database response and invoking callback. They rely on the I/O overlapping and provide much better thread economy. No additional threads are consumed for the duration of the network hop, database processing of the command - which is probably 99% of the overall time spent on the call. For a couple of calls it makes no difference, but when you designing a high throughput server, thread economy becomes very important.
I was wondering myself why BeginExecuteScalar is missing. Also, most of other providers, including ODP.Net for example, have no async API at all!
And yes, there is no interface for async operations.
On
Even if your are retrieving "one value" most of the time will be spent on 1) network hop to the database server, 2) database server executing command. Much more time than you will spend on say reading 1000 records into data set. So, I agree, it's not clear why there is no BeginExecuteScalar...
On
I recommend to treat DbCommand and its friends as if they were interfaces when consuming database APIs. For the sake of generalizing an API over various database providers, DbCommand achieves just as well as IDbCommand—or, arguably, better, because it includes newer technologies such as proper awaitable Task *Async() members.
MS can’t add any new methods with new functionality to IDbCommand. If they were to add a method to IDbCommand, it is a breaking change because anyone is free to implement that interface in their code and MS has put much effort into preserving ABI and API compatibility in the framework. If they expanded interfaces in a release of .net, customer code which previously worked would stop compiling and existing assemblies which are not recompiled would start encountering runtime errors. Additionally, they can’t add proper *Async() or Begin*() methods via extension methods without doing ugly casting to DbCommand behind the scenes (which is a bad practice itself, breaking type safety and unnecessarily introducing dynamic runtime casting).
On the other hand, MS can add new virtual methods to DbCommand without breaking ABI. Adding new methods to a base class might be considered breaking the API (compile-time, not as bad to break as runtime) because if you inherited DbCommand and had added a member with the same name, you’ll start getting the warning CS0108: 'member1' hides inherited member 'member2'. Use the new keyword if hiding was intended.). Thus, DbCommand can get the new features with minimal impact on consuming code which follows good practices (e.g., most stuff will keep working as long as it doesn’t work against the type system and call methods using something like myCommand.GetType().GetMethods()[3].Invoke(myCommand, …)).
A possible strategy which MS could have used to support people who like interfaces would have been to introduce new interfaces with names like IAsyncDbCommand and have DbCommand implement them. They haven’t done this. I don’t know why, but they probably didn’t do this because it would increase complication and the alternative of directly consuming DbCommand provides most of the benefits to consuming interfaces with few downsides. I.e., it would be work with little return.
IDbCommanddoes not have the begin/end async methods because they did not yet exist in the original .NET 1.1 release of ADO.NET, and when the async methods were added in .NET 2.0 it would have been a breaking change to add those toIDbCommand(adding members to an interface is a breaking change for implementors of that interface).I don't know why
BeginExecuteScalardoesn't exist, but it can be implemented as an extension method that wraps aroundBeginExecuteReader. Anyway in .NET 4.5 we now haveExecuteScalarAsyncwhich is easier to use.