ASP.NET webapi HttpResponseMessage with ExecuteReaderAsync CommandBehavior.SequentialAccess

Question

I need to stream blob data from Sql Server via WebApi.

I DONT want to buffer the blob data in memory on the web server.

I have the following code, but it does not work - there is NO exception.

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 the reposnse: [Fiddler] ReadResponse() failed: The server did not return a response for this request.

How can I stream the content from the DB to the http output stream, with out buffering it in memory?

usr · Accepted Answer · 2013-08-17 18:39:46Z

1

You are closing the stream before ASP.NET MVC has finished reading from it. It will be closed once you leave the various using blocks which happens right after the return statement has executed.

I know of no easy way to accomplish this. The best idea would be to write a custom Stream-derived class that wraps the stream returned by ADO.NET and once the stream is depleted disposes of everything (the Stream, the reader, the command and the connection).

This solution would mean that you cannot use using blocks and such. I really don't like it but I cannot think of something better at the moment. The requirements are difficult in combination: you want streaming behavior and need to dispose of the various resources you opened.

answered Aug 17, 2013 at 18:39

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

svick Over a year ago

I was thinking of something similar: create a custom class derived from StreamContent that overrides the Dispose() method.

Collectives™ on Stack Overflow

ASP.NET webapi HttpResponseMessage with ExecuteReaderAsync CommandBehavior.SequentialAccess

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related