ASP.NET webapi HttpResponseMessage with ExecuteReaderAsync CommandBehavior.SequentialAccess

Question

I need to transfer BLOB data from Sql server via WebApi.

I do NOT want to buffer blob data in memory on a web server.

I have the following code, but it does not work - there are no exceptions.

public class AttachmentController : ApiController { public async Task<HttpResponseMessage> Get(int id) { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) { await connection.OpenAsync(); using (var command = new SqlCommand("SELECT Content FROM [Attachments] WHERE ID = @ID", connection)) { command.Parameters.AddWithValue("ID", id); using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess)) { if (await reader.ReadAsync()) { using (Stream data = reader.GetStream(0)) { var response = new HttpResponseMessage{Content = new StreamContent(data)}; //I get this from the DB else where //response.Content.Headers.ContentType = new MediaTypeHeaderValue(attachment.ContentType); //I get this from the DB else where //response.Content.Headers.ContentLength = attachment.ContentLength; return response; } } } } throw new HttpResponseException(HttpStatusCode.NotFound); } } } 

Fiddle writes the following error as a reposition: Error [Fiddler] ReadResponse (): the server did not respond to this request.

How can I transfer contents from the database to the http output stream without loading it into memory?

Answer 1

You close the stream before ASP.NET MVC has finished reading from it. It will be closed after you leave the various usage blocks that will occur immediately after the return executed.

I don’t know how easy it is to do it. A better idea would be to write a custom Stream -derived class that wraps the stream returned by ADO.NET, and once the stream is exhausted, everything has to do ( Stream , reader, command, and connection).

This solution means you cannot use blocks, etc. I really don't like this, but right now I can't think of anything better. The requirements are complex in combination: you want streaming behavior and you need to get rid of the various resources that you have discovered.