AsyncDataAdapter

Implementation of asynchronous methods for ADO.NET

The implementation is based on source code provided by Microsoft on GitHub.

Onboarding and contributing

Version 5.0.1

Dependency changes (for all projects)
- .Net Standard and .Net Core dependencies removed since they are deprecated/obsolete
- Added .Net Core 6.0
- Added .Net 4.8
  - This was left @ 4.8 to support Windows Server 2022 and any version of Windows 10/11 patched in the last couple of years

Version 5.0

Tooling: Visual Studio 2019 with .NET Core workload
Dependencies:
- .NET Standard 2.0 for the AsyncDataAdapter library.
- .NET Core 3.1 for the AsyncDataAdapter.Test project.
  - Currently uses NUnit 3.x, but I'd like to move-over to xUnit.
Future development goals:
- I also want to get Appveyor working with this fork.
- The Update and UpdateAsync methods need working test-cases.
- Internally the project uses reflection to workaround some rather unfortunate design-decisions in the original DbDataAdapter and DbCommandBuilder classes - so these features may fail in some future update to .NET. Also they use MethodInfo.Invoke which can be improved by using Dynamic-Methods (i.e. IL-generation).
  - It would be a good idea to replace direct calls to reflected methods to interfaces that allow consumers to provide their own methods, e.g. to work-around any breaking changes to reflection in future builds of .NET, and so on.
Advisory: this project will likely be short-lived because Microsoft will eventually implement async support in their DbDataAdapter and subclasses, likely before .NET 7 around 2023.

Nuget package

Version 4.0

Version 3.0

https://www.nuget.org/packages/Jehoel.AsyncDataAdapter/

Version 1.0 - 2.0

https://www.nuget.org/packages/AsyncDataAdapter/

Usage

You can use the asynchronous methods using either the provider-specific concrete subclasses (such as SqlAsyncDbDataAdapter, for System.Data.SqlClient; and MSSqlAsyncDbDataAdapter for Microsoft.Data.SqlClient) - or via the interfaces IAsyncDataAdapter, IAsyncDbDataAdapter, IUpdatingAsyncDataAdapter, and IUpdatingAsyncDbDataAdapter.

Provider-specific subclasses

SqlAsyncDbDataAdapter
- You will need to add a dependency to the Jehoel.AsyncDataAdapter.System.Data.SqlClient NuGet package.
MSSqlAsyncDbDataAdapter
- You will need to add a dependency to the Jehoel.AsyncDataAdapter.Microsoft.Data.SqlClient NuGet package.
There is no AsyncDbDataAdapter class for System.Data.OleDb nor System.Data.Odbc because the lower-level OLE-DB and ODBC APIs do not expose an async-capable interface (at least, as far as I know).
- While the base class System.Data.Common.DbDataReader does have a RaedAsync method, it's a fake-async event: Indeed, if you look at what happens when you use OleDbCommand.ExecuteReaderAsync or OdbcCommand.ExecuteNonQueryAsync they're just thin-wrappers over fake async methods (that either block the thread, or run the method in the background in the thread pool).#
  - Because of this, there is no async support for OLE-DB and ODBC in this library. Pull-requests that implement an async DataReader with fake async will not be accepted.

Interfaces

IAsyncDataAdapter and IAsyncDbDataAdapter
These interfaces extend IDataAdapter and IDbDataAdapter respectively with these new methods:
- Task<Int32> FillAsync( DataSet, CancellationToken )
- Task<DataTable[]> FillSchemaAsync( DataSet dataSet, SchemaType schemaType, CancellationToken cancellationToken ).
IUpdatingAsyncDataAdapter and IUpdatingAsyncDbDataAdapter
These interfaces extend IAsyncDataAdapter and IAsyncDbDataAdapter respectively with this method:
- Task<Int32> UpdateAsync( DataSet dataSet, CancellationToken cancellationToken );

Sample usage for `FillAsync(DataTable)` and CancellationTokenSource

using (CancellationTokenSource cts = new CancellationTokenSource( TimeSpan.FromSeconds(15) ))
using (SqlConnection conn = new SqlConnection( this.connectionString ))
{
    await conn.OpenAsync( cts.Token );

    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "dbo.GetFast";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Number", SqlDbType.Int).Value = 100000;

        using(SqlAsyncDbDataAdapter adapter = cmd.CreateAsyncAdapter()) // `CreateAsyncAdapter` is an extension method.
        {
            DataTable singleTable = new DataTable();
            Int32 rows = await a.FillAsync(singleTable, cts.Token );
            return ds;
        }
    }
}

Sample usage for `FillAsync(DataSet)`, without any CancellationToken

using (SqlConnection conn = new SqlConnection( this.connectionString ))
{
    await conn.OpenAsync();

    using (SqlCommand c = conn.CreateCommand())
    {
        c.CommandText = "GetFast";
        c.CommandType = CommandType.StoredProcedure;
        c.Parameters.Add("@Number", SqlDbType.Int).Value = 100000;

        using(SqlAsyncDbDataAdapter adapter = cmd.CreateAsyncAdapter())
        {
            DataSet ds = new DataSet();
            Int32 rows = await a.FillAsync(ds);
            return ds;
        }
    }
}

Sample usage for `UpdateAsync(DataSet)`, without any CancellationToken

using (SqlConnection conn = new SqlConnection( this.connectionString ))
using (SqlCommand selectCmd = conn.CreateCommand())
{
    c.CommandText = "GetFast";
    c.CommandType = CommandType.StoredProcedure;
    c.Parameters.Add("@Number", SqlDbType.Int).Value = 100000;

    using(MSSqlAsyncDbDataAdapter adapter = cmd.CreateAsyncAdapter())
    using(IAsyncDbCommandBuilder cmdBuilder = await adapter.CreateCommandBuilderAsync().ConfigureAwait(false) )
    {
        DataSet dataSet = new DataSet();
        _ = await a.FillAsync( dataSet );
        
        adapter.UpdateCommand = cmdBuilder.GetUpdateCommand();
        
        _ = await adapter.UpdateAsync( dataSet );
    }
}
}

Regarding `Update` and `UpdateAsync`

This new library does support DbDataAdapter.Update and UpdateAsync with test coverage for all overloads.
- This library also includes an async DbCommandBuilder base class.
  - This DbCommandBuilder class is necessary because the current base DbCommandBuilder in ADO.NET makes synchronous database calls to populate its internal schema cache.
The UpdateAsync methods should all work in principle however I am not currently able to test them adequately.
- Indeed, the tests for Update and UpdateAsync currently fail because I honestly have no idea how to thoroughly and correctly test how Update and UpdateAsync work with respect to different Table Mappings and multi-table (DataSet and DataTable[]) updates.
- There is a dearth of documentation available about this functionality, which is unfortunate, but understandable, given that Entity Framework has fully replaced the need for DbDataAdapter in my opinion.
If you're looking for UpdateAsync support, then please see

ChristineBoersen/AsyncDataAdapter