Implementation of asynchronous methods for ADO.NET
The implementation is based on source code provided by Microsoft on GitHub.
- 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
-
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.
- .NET Standard 2.0 for the
-
Future development goals:
- I also want to get Appveyor working with this fork.
- The
Update
andUpdateAsync
methods need working test-cases. - Internally the project uses reflection to workaround some rather unfortunate design-decisions in the original
DbDataAdapter
andDbCommandBuilder
classes - so these features may fail in some future update to .NET. Also they useMethodInfo.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.
- https://www.nuget.org/packages/Jehoel.AsyncDataAdapter/
- https://www.nuget.org/packages/Jehoel.AsyncDataAdapter.System.Data.SqlClient/
- https://www.nuget.org/packages/Jehoel.AsyncDataAdapter.Microsoft.Data.SqlClient/
https://www.nuget.org/packages/Jehoel.AsyncDataAdapter/
https://www.nuget.org/packages/AsyncDataAdapter/
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
.
SqlAsyncDbDataAdapter
- You will need to add a dependency to the
Jehoel.AsyncDataAdapter.System.Data.SqlClient
NuGet package.
- You will need to add a dependency to the
MSSqlAsyncDbDataAdapter
- You will need to add a dependency to the
Jehoel.AsyncDataAdapter.Microsoft.Data.SqlClient
NuGet package.
- You will need to add a dependency to the
- There is no
AsyncDbDataAdapter
class forSystem.Data.OleDb
norSystem.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 aRaedAsync
method, it's a fake-async event: Indeed, if you look at what happens when you useOleDbCommand.ExecuteReaderAsync
orOdbcCommand.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.
- While the base class
IAsyncDataAdapter
andIAsyncDbDataAdapter
- These interfaces extend
IDataAdapter
andIDbDataAdapter
respectively with these new methods:Task<Int32> FillAsync( DataSet, CancellationToken )
Task<DataTable[]> FillSchemaAsync( DataSet dataSet, SchemaType schemaType, CancellationToken cancellationToken )
.
IUpdatingAsyncDataAdapter
andIUpdatingAsyncDbDataAdapter
- These interfaces extend
IAsyncDataAdapter
andIAsyncDbDataAdapter
respectively with this method:Task<Int32> UpdateAsync( DataSet dataSet, CancellationToken cancellationToken );
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;
}
}
}
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;
}
}
}
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 );
}
}
}
- This new library does support
DbDataAdapter.Update
andUpdateAsync
with test coverage for all overloads.- This library also includes an async
DbCommandBuilder
base class.- This
DbCommandBuilder
class is necessary because the current baseDbCommandBuilder
in ADO.NET makes synchronous database calls to populate its internal schema cache.
- This
- This library also includes an async
- The
UpdateAsync
methods should all work in principle however I am not currently able to test them adequately.- Indeed, the tests for
Update
andUpdateAsync
currently fail because I honestly have no idea how to thoroughly and correctly test howUpdate
andUpdateAsync
work with respect to different Table Mappings and multi-table (DataSet
andDataTable[]
) 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.
- Indeed, the tests for
- If you're looking for
UpdateAsync
support, then please see