[API Proposal]: Add FillAsync and FillSchemaAsync to DataAdapter

[gumbarros]

Background and motivation

Background:
DataAdapter is a essential component for data access and manipulation in .NET applications without reeling on third party packages. The derived classes provide methods for retrieving and updating data from databases. However, with the increasing demand for asynchronous programming patterns and the advent of async/await in C#, developers often face challenges when working with synchronous data adapters in asynchronous workflows.
There is lots of discussions at #22109 and a entire PR without feedback at #36746 (credits for @maxle5 and thank you for the design at the PR).

Motivation:
The motivation behind adding FillAsync and FillSchemaAsync, stems from the need to improve performance and responsiveness in modern .NET applications. Asynchronous programming allows developers to write non-blocking code, which can significantly enhance the scalability and efficiency of applications, especially when dealing with I/O-bound operations like database queries.

By introducing FillAsync and FillSchemaAsyncto ADO.NET data adapters, developers can leverage asynchronous programming techniques more seamlessly without the need to rely to custom solutions or 3rd party packages.

API Proposal

namespace System.Data.Common;

[DynamicallyAccessedMembers(DynamicallyAccessedMemberTypes.PublicParameterlessConstructor)]
public class DataAdapter : Component, IDataAdapter
{
    public virtual Task<int> FillAsync(DataSet dataSet);
    protected virtual Task<int> FillAsync(DataSet dataSet, string srcTable, IDataReader dataReader, int startRecord, int maxRecords);
    protected virtual Task<int> FillAsync(DataTable dataTable, IDataReader dataReader);
    protected virtual Task<int> FillAsync(DataTable[] dataTables, IDataReader dataReader, int startRecord, int maxRecords);
    public virtual Task<DataTable[]> FillSchemaAsync(DataSet dataSet, SchemaType schemaType);
    protected virtual Task<DataTable[]> FillSchemaAsync(DataSet dataSet, SchemaType schemaType, string srcTable, IDataReader dataReader);
    protected virtual Task<DataTable> FillSchemaAsync(DataTable dataTable, SchemaType schemaType, IDataReader dataReader);
}

API Usage

using var dbCommand = CreateDbCommand("SELECT * FROM FOO");
dbCommand.Connection =CreateConnection();
using var dataSet = new DataSet();
using (dbCommand.Connection)
{
    using var dataAdapter = new SqlDataAdapter();
    dataAdapter!.SelectCommand = dbCommand;
    await dataAdapter.FillAsync(dataSet);
}

Alternative Designs

No response

Risks

No response

[dotnet-policy-service]

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

[roji]

@gumbarros is this a duplicate of #22109?

[gumbarros]

A formal API proposal, the PR said there is no design and was recused because of this

[roji]

@gumbarros a semi-formal API proposal was already done in #22109 (comment). In any case, an API proposal isn't blocking the progress of this issue - API-wise this is just about adding async counterparts to existing sync APIs.

The issues here are more around implementation - both the necessary usage of default interface implementations (DIMs) and in general, plumbing the entire async support through DbDataAdapter. Importantly, DbDataAdapter is also generally considered legacy, and as a result we're generally not investing much effort in features in that area; stability and not breaking the existing code is the priority. This isn't to say that async support won't happen, but so far it hasn't bubbled up to the top of the priority list.

In any case, am closing this in favor of #22109.

[roji]

Duplicate of #22109