使用SqlBulkCopy时，将流作为二进制列的数据源 [英] Supplying stream as a source of data for a binary column when SqlBulkCopy is used

问题描述

如果需要以流方式从SqlServer中读取数据，则有一些功能。例如，将 SqlDataReader 与 CommandBehavior.SequentialAccess 一起使用，尤其是当需要访问二进制列数据时，存在 GetStream（int）方法：

If one needs to read data from SqlServer in a streamed fashion, there are some capabilities for that. Such as using SqlDataReader with CommandBehavior.SequentialAccess, and particularly when binary column data needs to be accessed there is the GetStream(int) method for that:

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

using (var dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
    dr.Read();

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

方向，当需要使用 SqlBulkCopy 将数据馈入 SqlServer时，特别是如果需要将流作为二进制列的数据源提供时？

But what about streaming data in the opposite direction, when one needs to feed data to SqlServer using SqlBulkCopy, and particularly if stream needs to be supplied as the source of data for a binary column?

我尝试了以下操作

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

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());
}

在 TestDataReader 实现的地方 IDataReader 如下：

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 it worked as expected.

但是更改

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

到

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

导致的异常 System.InvalidOperationException

来自数据源的MemoryStream类型的给定值不能被
转换为指定目标列的varbinary类型。

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

是否可以通过 IDataReader （或者可能是 DbDataReader ）到 SqlBulkCopy 作为二进制列的数据源，而没有首先将其所有数据复制到内存（字节数组）中？

Is there a way to supply Stream from IDataReader (or probably DbDataReader) to SqlBulkCopy as the source of data for a binary column, without copying all its data into memory (byte array) first?

推荐答案

不确定是否在任何地方对此进行了记录，但是如果对以下内容进行了简短检查， SqlBulkCopy 源代码，您可能会发现它以不同的方式对待不同的数据读取器。
首先， SqlBulkCopy 确实支持流传输和 GetStream ，但是您可能会注意到 IDataReader 接口不包含 GetStream 方法。因此，当您将自定义 IDataReader 实现提供给 SqlBulkCopy 时-它不会将二进制列视为流数据，并且不会接受 Stream 类型。

Not sure this is documented anywhere, but if do short inspection of SqlBulkCopy source code you may find out that it treats different data readers in different ways. First, SqlBulkCopy does support streaming and GetStream, but you might notice that IDataReader interface does not contain GetStream method. So when you feed custom IDataReader implementation to SqlBulkCopy - it will not treat binary columns as streamed and will not accept values of Stream type.

另一方面- DbDataReader 确实有这种方法。如果您提供 SqlBulkCopy 实例的 DbDataReader 继承的类，则它将以流方式处理所有二进制列，并且它将呼叫 DbDataReader.GetStream 。

On the other hand - DbDataReader does have this method. If you feed SqlBulkCopy an instance of DbDataReader-inherited class - it will treat all binary columns in streamed manner and it will call DbDataReader.GetStream.

因此，要解决您的问题-继承自 DbDataReader 像这样：

So to fix your problem - inherit from DbDataReader like 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;
            default:
                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

}

这篇关于使用SqlBulkCopy时，将流作为二进制列的数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！