SqlBulkCopy WriteToServerAsync using IDataReader

Question

I'm trying to use SqlBulkCopy to insert the contents of the CSV file into SQL Server Database from IDataReader (the specific implementation of the IDataReader is CsvDataReader from CsvHelper package). The following is my code:

using (var bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.KeepNulls, null))
        {
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = 4000;
            bulkCopy.EnableStreaming = true;
            await bulkCopy.WriteToServerAsync(dataReader, cancellationToken).ConfigureAwait(false);
        }

When trying to run this code I receive the following exception:

System.InvalidOperationException: Synchronous operations are disallowed. Call ReadAsync or set AllowSynchronousIO to true instead.

I believe this is the result of the fact that IDataReader doesn't have ReadAsync method

Is there a way to solve this problem?

Answer 1

I ended up writing a wrapper around CsvReader, that inherits from DbDataReader. I left most of the abstract method unimplemented, because I only needed a few methods for my purposes