Streaming VARBINARY data from SQL Server in C#

Question

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.

Georama

have you tried using directly an SqlDataReader? – Thaumatrope

Answer 1

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 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.

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.

This follow up article explains how to use a FILESTREAM column for efficient streaming of large VARBINARY data in and out of the database.

Desberg

Is there much benefit implementing SqlReaderStream from the article you link than just doing a byte[] chunked copy directly using SqlDataReader.GetBytes? It looks like in .NET 4.5 there's now a SqlDataReader.GetStream method. – Stocker

@Michael: The stream classes in my article are needed primarily due to lifetime management, as the moment the stream is created (controller) is not when the stream is used (response), and they have to properly dispose resources. – Desberg

Answer 2

@Remus's answer above is out-of-date since .NET 4.5 introduced first-class SqlClient Streaming. It's no longer necessary to access the SqlDataReader's GetBytes() methods to get a stream from a SQL Query.

Now you simply call SqlDataReader.GetStream(int) to get a stream over a blob column.

Hydracid