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

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

Answer 1

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.

Answer 2

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

Answer 3

IDbCommand does 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 to IDbCommand (adding members to an interface is a breaking change for implementors of that interface).

I don't know why BeginExecuteScalar doesn't 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 4

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.

Answer 5

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.

Answer 6

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...