如何从/向 SQL Server BLOB 字段流式传输数据? [英] How to Stream data from/to SQL Server BLOB fields?

问题描述

关于这个问题的背景，请参阅 我如何序列化一个大的.NET 对象图到 SQL ServerBLOB 没有创建一个大的缓冲区?" 现在有一个大赏金.

For the background to this question, see "How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?" that now has a large bounty on it.

我希望能够使用 Stream 对象在 SQL Server 行中的 BLOB 字段中读取/写入数据，而无需将所有数据放入临时缓冲区.

I wish to be able to use a Stream object to read/write data to/from a BLOB field in a SQL Server row without having to put the all the data into a temp buffer.

如果能做到以上...

由于 Streams 类有很多 CanXXX() 方法，并非所有流都可以被所有方法使用接受流输入/输出.

As the Streams class has lot of CanXXX() methods, not all streams can be used by all methods take accept stream inputs/outputs.

那么，流与 ADO.NET 一起工作的能力有多大?向/从 SQL Server 发送数据时?

So how capable does a stream have to be to work with ADO.NET when sending data to/from SQL Server?

我希望有一个标准 Stream，我可以将它传递给其他 API.

I am looking to have a standard Stream to which I can pass it on to other APIs.

到目前为止，这两个答案仅涉及从 SqlServer 获取数据，而不是将数据发送到 SqlServer.

Also the two answers so far only covers getting data form SqlServer, not sending the data to SqlServer.

推荐答案

下面是分块读取数据的例子:

Here's an example for reading data in chunks:

    using (var conn = new SqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "select somebinary from mytable where id = 1";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                byte[] buffer = new byte[1024]; // Read chunks of 1KB
                long bytesRead = 0;
                long dataIndex = 0;
                while ((bytesRead = reader.GetBytes(0, dataIndex, buffer, 0, buffer.Length)) > 0)
                {
                    byte[] actual = new byte[bytesRead];
                    Array.Copy(buffer, 0, actual, 0, bytesRead);
                    // TODO: Do something here with the actual variable, 
                    // for example write it to a stream
                    dataIndex += bytesRead;
                }
            }

        }
    }

这篇关于如何从/向 SQL Server BLOB 字段流式传输数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！