Supplying stream as a source of data for a binary column when SqlBulkCopy is used c# idatareader sqlbulkcopy sql-server


There are various options if one wants to read data from SQLServer in a streaming method. including utilizingSqlDataReader with CommandBehavior.SequentialAccess , and in particular when accessing binary column data there is theGetStream(int) approach for that

var cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = @"select 0x0123456789 as Data";

using (var dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))

    var stream = dr.GetStream(0);
    // access stream

But what about data streaming the other way, when one has to feed data to SQL Server utilizingSqlBulkCopy , and in particular if a binary column requires a stream to be given as the data source?

I attempted to adhere to

var cmd2 = new SqlCommand();
cmd2.Connection = connection;
cmd2.CommandText = @"create table #Test (ID int, Data varbinary(max))";

using (SqlBulkCopy sbc = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null))
    sbc.DestinationTableName = "#Test";
    sbc.EnableStreaming = true;

    sbc.ColumnMappings.Add(0, "ID");
    sbc.ColumnMappings.Add(1, "Data");

    sbc.WriteToServer(new TestDataReader());

Where TestDataReader implements IDataReader the following

class TestDataReader : IDataReader
    public int FieldCount { get { return 2; } }
    int rowCount = 1;
    public bool Read() { return (rowCount++) < 3; }
    public bool IsDBNull(int i) { return false; }

    public object GetValue(int i)
        switch (i)
            case 0: return rowCount;
            case 1: return new byte[] { 0x01, 0x23, 0x45, 0x67, 0x89 };
            default: throw new Exception();

    //the rest members of IDataReader

And everything went as planned.

However, altering

case 1: return new byte[] { 0x01, 0x23, 0x45, 0x67, 0x89 };


case 1: return new MemoryStream(new byte[] { 0x01, 0x23, 0x45, 0x67, 0x89 });

triggered exceptionSystem.InvalidOperationException include the message

The given value of type MemoryStream from the data source cannot be converted to type varbinary of the specified target column.

Is it possible to getStream from IDataReader (Or maybeDbDataReader ) toSqlBulkCopy without having to first transfer all of its contents into memory (byte array) as the source of data for a binary column?

5/11/2016 1:04:53 PM

Accepted Answer

I'm not sure whether this is mentioned elsewhere, but if you do a quickSqlBulkCopy code source You could discover that it handles various data readers in various ways. First,SqlBulkCopy does not permit streamingGetStream nonetheless, you could see thatIDataReader interface is devoid ofGetStream method. the next time you feed customIDataReader application ofSqlBulkCopy - It will not take values of and will not regard binary columns as streaming.Stream type.

Alternatively -DbDataReader does use this technique. When you eatSqlBulkCopy an example ofDbDataReader -inherited class, which will stream-process all binary columns and callDbDataReader.GetStream .

Therefore, to solve your issue, inherit fromDbDataReader as in this:

class TestDataReader : DbDataReader
    public override bool IsDBNull(int ordinal) {
        return false;

    public override int FieldCount { get; } = 2;
    int rowCount = 1;

    public override bool HasRows { get; } = true;
    public override bool IsClosed { get; } = false;

    public override bool Read()
        return (rowCount++) < 3;

    public override object GetValue(int ordinal) {
        switch (ordinal) {
            // do not return anything for binary column here - it will not be called
            case 0:
                return rowCount;
                throw new Exception();

    public override Stream GetStream(int ordinal) {
        // instead - return your stream here
        if (ordinal == 1)
            return new MemoryStream(new byte[] {0x01, 0x23, 0x45, 0x67, 0x89});
        throw new Exception();
    // bunch of irrelevant stuff

5/19/2016 9:18:36 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow