SqlBulkCopy multiple bulk copy operations issue

[maxcherednik]

Describe the bug

Multiple calls to the WriteToServerAsync method are supported according to this documentation.

SqlBulkCopy fails on the second call to the WriteToServerAsync.
It seems that SqlBulkCopy keeping the link to the IDataReader instance.

// First call. DataReader provided by Dapper(2.1.28)
using (dataReader)
{
    await sqlBulkCopy.WriteToServerAsync(dataReader);
}

// second call not using datareader but an DataRow[]. 
// This is where sqlBulkCopy throws.
await sqlBulkCopy.WriteToServerAsync(dataRows);

The stack trace does not contain the sqlBulkCopy. It seem that everything got inlined.

 Unhandled exception. System.ObjectDisposedException: Cannot access a disposed object.
  Object name: 'DbDataReader'.
     at Dapper.DisposedReader.ThrowDisposed[T]() in /_/Dapper/WrappedReader.cs:line 26
     at Dapper.DisposedReader.ThrowDisposedAsync[T]() in /_/Dapper/WrappedReader.cs:line 30

Expected behavior

Every call to WriteToServerAsync is independent and should not rely on the previous calls.

Further technical details

Microsoft.Data.SqlClient(5.1.4)
System.Data.SqlClient(4.8.6)
.NET target: .net 8
SQL Server version: (e.g. SQL Server 2019)
Operating system: Win10

Additional context

There is another issue here, but I guess the root cause is the same. If the ObjectDisposedException is not thrown, then the metadata of the first IDataReader is used to access the DataRows[]. It happened that I had a mistake in the DataTable column definition. One of the columns was wrong. We were lucky that the order of the columns was the same, it worked based on the first call. The moment the first call was commented out there was another exception thrown:

System.InvalidOperationException: The given ColumnName 'Code' does not match up with any column in data source.
System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerCommon(int columnCount)at Task System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(int columnCount, CancellationToken ctoken)
at Task System.Data.SqlClient.SqlBulkCopy.WriteToServerAsync(DataRow[] rows, CancellationToken cancellationToken)

My guess is that the internal state is not properly cleaned on every call.
During the first call we set 2 data reader related fields: https://github.com/microsoft/referencesource/blob/51cf7850defa8a17d815b4700b67116e3fa283c2/System.Data/System/Data/SqlClient/SqlBulkCopy.cs#L1694

But during the second call, we cleanup only one of them:
Here for the DataRow[]: https://github.com/microsoft/referencesource/blob/51cf7850defa8a17d815b4700b67116e3fa283c2/System.Data/System/Data/SqlClient/SqlBulkCopy.cs#L1580

Here for the DataTable:
https://github.com/microsoft/referencesource/blob/51cf7850defa8a17d815b4700b67116e3fa283c2/System.Data/System/Data/SqlClient/SqlBulkCopy.cs#L1732

Workaround
Either use different instances of the SqlBulkCopy or if possible you can switch the order of the WriteToServerAsync.

[JRahnama]

We will look into this and will get back to you.