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?

`WriteToServerAsync` has many overloads, so have you explored this idea? While `IDataReader` doesn't have a `ReadAsync` method, `DbDataReader` does. What is `dataReader`, can it be declared as a `DbDataReader` or one of its specific derived classes such as `SqlDataReader`? — AlwaysLearning, Feb 02 '23 at 20:56
@AlwaysLearning `CsvDataReader` only implements `IDataReader` interface. I ended up writing a custom wrapper around `CsvReader` class — JohnDiGriz, Feb 07 '23 at 11:17

score 1 · Accepted Answer · answered Feb 07 '23 at 11:16

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

answered Feb 07 '23 at 11:16

JohnDiGriz

171
13

SqlBulkCopy WriteToServerAsync using IDataReader

1 Answers1