System.Data.IDbCommand and asynchronous execution?

Question

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 intended only for synchronous operations.

Is there any interface for asynchronous operations?
Also, why is there no BeginExecuteScalar?

Answer 1

IDbCommand does not have asynchronous begin / end methods, since they did not yet exist in the original version of .NET 1.1 ADO.NET, and when async methods were added in .NET 2.0 , it would be a terrific change to add them to IDbCommand (adding elements to the interface is a violation of changes for developers of this interface).

I do not know why BeginExecuteScalar does not exist, but it can be implemented as an extension method that wraps around BeginExecuteReader . Anyway, in .NET 4.5 we now have ExecuteScalarAsync , which is easier to use.

Answer 2

In fact, creating asynchronous behavior equivalent to BeginExecuteNonQuery, EndExecuteNonQuery, etc., would be quite a challenge. The implementation of these APIs far exceeds the simple propagation of a single thread, waiting for a database response and calling a callback. They rely on I / O overlap and provide much better flow savings. No additional threads are consumed during the entire network break; processing the database with the command is probably 99% of the total time spent on the call. For a few calls, this does not matter, but when designing a server with high bandwidth, saving threads becomes very important.

I was wondering why BeginExecuteScalar is missing. In addition, most other providers, including ODP.Net, for example, do not have an asynchronous API at all!

And yes, there is no interface for asynchronous operations.

Answer 3

To solve this particular problem, I built a strip that calls asynchronous methods if they exist in IDbConnection.IDbCommand / IDataReader or calls ordinary methods if they do not.

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()); } } 

Answer 4

Even if you extract a “single value”, most of the time will be spent on 1) a network transition to the database server, 2) the execution of the database server command. Much more time than you spend reading 1000 records in a data set. So, I agree, it is not clear why there is no BeginExecuteScalar ...

Answer 5

I recommend treating DbCommand and his friends as if they were interfaces when using the database APIs. For the sake of generalizing the API for different database providers, DbCommand achieves exactly the same as IDbCommand — or perhaps better, because it includes newer technologies such as await Task *Async() .

MS can not add any new methods with new functionality in IDbCommand . If they added a method to IDbCommand , this would be a change, since anyone can implement this interface in their code, and MS has put a lot of effort into maintaining ABI and API compatibility within the framework. If they extended the interfaces in the .net release, client code that previously worked stopped compiling, and existing assemblies that were not recompiled would encounter runtime errors. In addition, they cannot add the correct *Async() or Begin*() methods using extension methods without doing an ugly cast to DbCommand backstage (which is the worst practice, fault security, and unnecessary implementation of dynamic execution at run time).

On the other hand, MS can add new virtual methods to DbCommand without breaking the ABI. Adding new methods to the base class may be considered an API violation (compilation time, and not so bad for interruption as the runtime), because if you inherit DbCommand and add a member with the same name, you will begin to receive warning CS0108: 'member1' hides the inherited member 'member2'. Use a new keyword if hiding was intended. ) Thus, DbCommand can get new features with minimal impact on consumer code that follows best practices (for example, most materials will work until it works against the type system and calls methods using something like myCommand.GetType().GetMethods()[3].Invoke(myCommand, …) ).

A possible strategy that MS could use to support people who like interfaces would be to introduce new interfaces with names like IAsyncDbCommand and have DbCommand implement them. They did not do this. I don’t know why, but they probably didn’t, because it would increase the complication, and the alternative to direct consumption of DbCommand provides most of the advantages for using interfaces with a few minuses. Ie, this will work with a small return.

Answer 6

You can implement asynchronous behavior in your user code, since it is not as complicated as for your question - there are no standard asynchronous operations for your purposes.

Answer 7

I came across this question when I need to transfer my data calls to async methods. I created a problem for the future .NET Standard to include an asynchronous interface . At the same time, I also created a library with a set of interfaces and adapters for System.Data .

Answer 8

No, there are no interfaces for them.

The reason BeginExecuteScalar doesn't exist is because you probably won't need an asynchronous call to get one value back, which should be very fast