在 C# 中从 SQL Server 流式传输 VARBINARY 数据 [英] Streaming VARBINARY data from SQL Server in C#

问题描述

我正在尝试使用 ASP.Net 提供存储在数据库的 VARBINARY(MAX) 字段中的图像数据.现在，代码正在填充数据表，然后将字节数组从 DataRow 中拉出并将字节数组推送到响应中.我想知道是否有一种方法可以或多或少地将数据从 SQL Server 流式传输到响应中，而无需编组这些巨大的字节数组(因为图像很大，它们会导致 OutOfMemoryExceptions).是否有一个类/机制?

I'm trying to serve image data stored in a VARBINARY(MAX) field in the database using ASP.Net. Right now, the code is filling a data table, then pulling the byte array out of the DataRow and pushing the byte array into the response. I'm wondering if there's a way to more-or-less stream the data from the SQL Server into the response without having to marshal around these huge byte arrays (since the images are large, they cause OutOfMemoryExceptions). Is there a class/mechanism for that?

当前代码看起来或多或少像:

The current code looks more or less like:

DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(commandText, connectionString);
adapter.Fill(table);
DataRow row = table.Rows[0];
byte[] imageData = row[0] as byte[];
if(imageData != null)
{
  Response.Clear();
  Response.BinaryWrite(imageData);
  Response.End();
}

提前致谢 - 感谢您的帮助.

Thanks in advance - any help is appreciated.

推荐答案

参见 下载和上传来自 SQL Server 的图片，用于涵盖该主题的文章，包括高效的流语义.您必须使用 SqlDataReader 以 CommandBehavior.SequentialAccess 打开:

See Download and Upload Images from SQL Server for an article covering the topic, including efficient streaming semantics. You must use a SqlDataReader opened with CommandBehavior.SequentialAccess:

SequentialAccess 提供了一种方式处理行的 DataReader包含大二进制的列值.而不是加载整个行，SequentialAccess 启用DataReader 将数据作为流加载.然后您可以使用 GetBytes 或GetChars 方法指定一个字节开始读取操作的位置，和有限的数据缓冲区大小被退回.

SequentialAccess Provides a way for the DataReader to handle rows that contain columns with large binary values. Rather than loading the entire row, SequentialAccess enables the DataReader to load data as a stream. You can then use the GetBytes or GetChars method to specify a byte location to start the read operation, and a limited buffer size for the data being returned.

链接的文章提供了用于创建由 SqlDataReader 支持的流的完整代码，您可以简单地Stream.CopyTo(HttpResponse.OutputStream)，如果您还没有 .Net 4.0，则使用 byte[] 分块副本.

The linked article provides full code for creating a Stream backed by an SqlDataReader, you can simply Stream.CopyTo(HttpResponse.OutputStream), or use a byte[] chunked copy if you don't have .Net 4.0 yet.

这篇后续文章解释了如何使用 FILESTREAM 列有效地将大型 VARBINARY 数据流式传输到数据库内外.

这篇关于在 C# 中从 SQL Server 流式传输 VARBINARY 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！