[API Proposal]: .NET SqlDataAdapter.Fill with CancellationToken

[tauheedul]

Background and motivation

There is currently no SqlDataAdapter.Fill functionality that supports CancellationToken as a parameter.

The following exits the query but there is a delay between the sqlCommand.Cancel() being invoked and the application exiting the fill function.

using (var registration = cancellationToken.Register(() => sqlCommand.Cancel())) { adapter.Fill(dataTable); }

• Scenario 1: SQL database is querying when the token expired.

Result: SQL query cancelled or after the SqlCommand.CommandTimeout has expired (whichever is first).

• Scenario 2: SQL database has already returned the dataset to the application, and the application is filling the data into a data table at the point the token expired.

Result: Fill is cancelled but after a significant delay. I observed a 5 minute delay between sqlCommand.Cancel() on a dataset containing 650,000+ records with 45 columns on a development server with no traffic (with CancellationToken expiry set at 60 minutes).

• Scenario 3: The data table fill is complete before token expiry.

Result: No change required.

API Proposal

Update the SqlDataAdapter.Fill or SqlDataAdapter.FillAsync to support CancellationToken parameter that will exit large batches while filling dataset or datatable object.

        /// <summary>
        /// Asynchronously fills a DataTable with data from the source using the SelectCommand.
        /// </summary>
        /// <param name="adapter">The data adapter to use for the fill operation.</param>
        /// <param name="dataTable">The DataTable to fill.</param>
        /// <param name="cancellationToken">A CancellationToken to observe while waiting for the task to complete.</param>
        /// <returns>A task that represents the asynchronous operation. The task result contains the number of rows successfully added to the DataTable.</returns>
        /// <exception cref="OperationCanceledException">The operation was cancelled via the cancellation token.</exception>
        public static Task<int> FillAsync(
            this DbDataAdapter adapter, 
            DataTable dataTable, 
            CancellationToken cancellationToken = default);

        /// <summary>
        /// Asynchronously fills a DataSet with data from the source using the SelectCommand.
        /// </summary>
        /// <param name="adapter">The data adapter to use for the fill operation.</param>
        /// <param name="dataSet">The DataSet to fill.</param>
        /// <param name="cancellationToken">A CancellationToken to observe while waiting for the task to complete.</param>
        /// <returns>A task that represents the asynchronous operation. The task result contains the number of rows successfully added to the DataSet.</returns>
        /// <exception cref="OperationCanceledException">The operation was cancelled via the cancellation token.</exception>
        public static Task<int> FillAsync(
            this DbDataAdapter adapter, 
            DataSet dataSet, 
            CancellationToken cancellationToken = default);

        /// <summary>
        /// Asynchronously fills a specified table within a DataSet with data from the source using the SelectCommand.
        /// </summary>
        /// <param name="adapter">The data adapter to use for the fill operation.</param>
        /// <param name="dataSet">The DataSet to fill.</param>
        /// <param name="srcTable">The name of the source table to use for table mapping.</param>
        /// <param name="cancellationToken">A CancellationToken to observe while waiting for the task to complete.</param>
        /// <returns>A task that represents the asynchronous operation. The task result contains the number of rows successfully added to the DataSet.</returns>
        /// <exception cref="OperationCanceledException">The operation was cancelled via the cancellation token.</exception>
        public static Task<int> FillAsync(
            this DbDataAdapter adapter, 
            DataSet dataSet, 
            string srcTable, 
            CancellationToken cancellationToken = default);

        // Additional overloads could be considered to match paging functionality, etc.
    }

• The implementation can be synchronous or asynchronous.

• Inside the loop, before adding a row to the DataTable, it would check cancellationToken.ThrowIfCancellationRequested() or check cancellationToken.IsCancellationRequested and exit using sqlCommand.Cancel() if the database is querying before returning an exception. This ensures cancellation is respected even during the client-side population phase, which is a major improvement.

API Usage

• Either Asynchronous

var tokenSource = new CancellationTokenSource(TimeSpan.FromMinutes(1));
int count = await adapter.FillAsync(table, tokenSource.Token);

• Or Synchronous

var tokenSource = new CancellationTokenSource(TimeSpan.FromMinutes(1));
int count = adapter.Fill(table, tokenSource.Token);

Alternative Designs

Synchronous implementation

        /// <summary>
        /// Synchronously fills a DataTable with data from the source using the SelectCommand.
        /// </summary>
        /// <param name="adapter">The data adapter to use for the fill operation.</param>
        /// <param name="dataTable">The DataTable to fill.</param>
        /// <param name="cancellationToken">A CancellationToken to observe while waiting for the task to complete.</param>
        /// <returns>Result contains the number of rows successfully added to the DataTable.</returns>
        /// <exception cref="OperationCanceledException">The operation was cancelled via the cancellation token.</exception>
        public static int Fill(
            this DbDataAdapter adapter, 
            DataTable dataTable, 
            CancellationToken cancellationToken = default);

        /// <summary>
        /// Synchronously fills a DataSet with data from the source using the SelectCommand.
        /// </summary>
        /// <param name="adapter">The data adapter to use for the fill operation.</param>
        /// <param name="dataSet">The DataSet to fill.</param>
        /// <param name="cancellationToken">A CancellationToken to observe while waiting for the task to complete.</param>
        /// <returns>Result contains the number of rows successfully added to the DataSet.</returns>
        /// <exception cref="OperationCanceledException">The operation was cancelled via the cancellation token.</exception>
        public static int Fill(
            this DbDataAdapter adapter, 
            DataSet dataSet, 
            CancellationToken cancellationToken = default);

        /// <summary>
        /// Synchronously fills a specified table within a DataSet with data from the source using the SelectCommand.
        /// </summary>
        /// <param name="adapter">The data adapter to use for the fill operation.</param>
        /// <param name="dataSet">The DataSet to fill.</param>
        /// <param name="srcTable">The name of the source table to use for table mapping.</param>
        /// <param name="cancellationToken">A CancellationToken to observe while waiting for the task to complete.</param>
        /// <returns>Result contains the number of rows successfully added to the DataSet.</returns>
        /// <exception cref="OperationCanceledException">The operation was cancelled via the cancellation token.</exception>
        public static int Fill(
            this DbDataAdapter adapter, 
            DataSet dataSet, 
            string srcTable, 
            CancellationToken cancellationToken = default);

        // Additional overloads could be considered to match paging functionality, etc.
    }

Risks

The current DataAdapter.Fill is synchronous. It may be simpler to implement a CancellationToken in the existing version than introducing DataAdapter.FillAsync. This would not break existing applications.

[dotnet-policy-service]

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

[roji]

The current DataAdapter.Fill is synchronous. It may be simpler to implement a CancellationToken in the existing version than introducing DataAdapter.FillAsync.

.NET synchronous methods, such as DataAdapter.Fill(), do not accept cancellation tokens; cancellation tokens are part of the async programming model - as far as I'm aware, no synchronous method in the BCL accepts one.

More importantly, even if we added a new overload of DataAdapter.Fill() which accepts a cancellation token, what would it do? DataAdapter is ultimately just a layer over the underlying ADO.NET implementations, e.g. DbCommand. Since DbCommand doesn't accept a cancellation token (and we can't add it there), the only thing DataAdapter could do would be to call DbCommand.Cancel(), which is the older, synchronous way to cancel ongoing operations in ADO.NET. That's something that the user can already do - as you've written above - and there's very little advantage in having DataAdapter do that same thing for the user.

Finally:

Result: Fill is cancelled but after a significant delay. I observed a 5 minute delay between sqlCommand.Cancel() on a dataset containing 650,000+ records with 45 columns on a development server with no traffic (with CancellationToken expiry set at 60 minutes).

That is a completely orthogonal issue, and simply making DataAdapter accept wouldn't change it in any way: cancellation tokens don't magically make cancellation work better than sqlCommand.Cancel() already does.

What you probably need to do here, is investigate why exactly sqlCommand.Cancel() is performing as expected in your scenario - this may very well be an issue in SqlClient itself, or possibly in your network. In any case, it has nothing to do with either DataAdapter or cancellation tokens. I'd suggest trying to put together some sort of repro and then opening an issue on https://github.com/dotnet/sqlclient.

[tauheedul]

Hi @roji Thank you.

My suggestion was for either DataAdapter.Fill or DataAdapter.FillAsync implementation (whichever is appropriate).

There is presently no DataAdapter.FillAsync either so my main API proposal had adapter.FillAsync with cancellation token and the alternative being adapter.Fill(). The new implementation would iterate the data from the data reader into the datatable and throw token.ThrowIfCancellationRequested(); and cancel iteration immediately when the token expires.

I can see there is already an existing proposal for this but it didn't mention a cancellation token in the proposal: Consider adding FillAsync and async APIs on data adapters so perhaps this ticket should remain open?

I understand your feedback regarding SqlClient could be a separate issue and I have noted that for the future.