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?
我正在尝试使用ASP.Net提供存储在数据库中VARBINARY（MAX）字段中的图像数据。现在，代码填充数据表，然后将字节数组拉出DataRow并将字节数组推送到响应中。我想知道是否有办法将数据从SQL Server或多或少地流式传输到响应中，而不必编组这些巨大的字节数组（因为图像很大，它们会导致OutOfMemoryExceptions）。那有一个类/机制吗？
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.
在此先感谢 - 任何帮助表示赞赏。
1 个解决方案
#1
19
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:
有关该主题的文章，请参阅从SQL Server下载和上载映像，包括高效的流式语义。您必须使用通过CommandBehavior.SequentialAccess打开的SqlDataReader：
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.
SequentialAccess为DataReader提供一种处理包含具有大二进制值的列的行的方法。 SequentialAccess不是加载整行，而是使DataReader能够将数据作为流加载。然后，您可以使用GetBytes或GetChars方法指定启动读取操作的字节位置，以及返回数据的有限缓冲区大小。
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.
链接的文章提供了用于创建由SqlDataReader支持的Stream的完整代码，您可以简单地使用Stream.CopyTo（HttpResponse.OutputStream），或者如果您还没有.Net 4.0，则使用byte [] chunked copy。
This follow up article explains how to use a FILESTREAM column for efficient streaming of large VARBINARY data in and out of the database.
这篇后续文章解释了如何使用FILESTREAM列有效地将大型VARBINARY数据流入和流出数据库。
#1
19
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:
有关该主题的文章，请参阅从SQL Server下载和上载映像，包括高效的流式语义。您必须使用通过CommandBehavior.SequentialAccess打开的SqlDataReader：
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.
SequentialAccess为DataReader提供一种处理包含具有大二进制值的列的行的方法。 SequentialAccess不是加载整行，而是使DataReader能够将数据作为流加载。然后，您可以使用GetBytes或GetChars方法指定启动读取操作的字节位置，以及返回数据的有限缓冲区大小。
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.
链接的文章提供了用于创建由SqlDataReader支持的Stream的完整代码，您可以简单地使用Stream.CopyTo（HttpResponse.OutputStream），或者如果您还没有.Net 4.0，则使用byte [] chunked copy。
This follow up article explains how to use a FILESTREAM column for efficient streaming of large VARBINARY data in and out of the database.
这篇后续文章解释了如何使用FILESTREAM列有效地将大型VARBINARY数据流入和流出数据库。