How to async stream a file from database to webcli

Question

I'm trying to async stream a file from sql server out to a web client using .net 4.5 and web api.

I'm using SqlDataReader.GetStream() to get a stream from the db. However, I'm not sure how to wire in the disposal/closing of the db connection when webapi is finished reading from the stream.

Any samples out there?

Answer 1

You can write a wrapping stream that reads from the underlying stream until it is depleted, and then disposes of all associated resources (like the SqlConnection).

I'm not a WebAPI expert so there might be a more elegant way to do it. But this will work in any case with the standard WebAPI support for streams.

Answer 2

You could do something like following in Web API. Here I am using PushStreamContent to connect to database and retrieve the Sql stream. I then copy this sql stream to the response stream directly. In Web API, when you use PushStreamContent, the client would receive response in chunked transfer encoding because you are not setting the content-length of the response. If this is fine for you, you could use the below example. Otherwise I will try to see if there are any other better ways to accomplish this.

NOTE: This is a quick example based on PushStreamContent and this article on MSDN regarding retrieving binary data from sql server.

[RoutePrefix("api/values")]
public class ValuesController : ApiController
{
    private const string connectionString = @"your connection string";

    [Route("{id}")]
    public HttpResponseMessage GetImage(int id)
    {
        HttpResponseMessage resp = new HttpResponseMessage();

        resp.Content = new PushStreamContent(async (responseStream, content, context) =>
        {
            await CopyBinaryValueToResponseStream(responseStream, id);
        });

        return resp;
    }

    // Application retrieving a large BLOB from SQL Server in .NET 4.5 using the new asynchronous capability
    private static async Task CopyBinaryValueToResponseStream(Stream responseStream, int imageId)
    {
        // PushStreamContent requires the responseStream to be closed
        // for signaling it that you have finished writing the response.
        using (responseStream)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                using (SqlCommand command = new SqlCommand("SELECT [bindata] FROM [Streams] WHERE [id]=@id", connection))
                {
                    command.Parameters.AddWithValue("id", imageId);

                    // The reader needs to be executed with the SequentialAccess behavior to enable network streaming
                    // Otherwise ReadAsync will buffer the entire BLOB into memory which can cause scalability issues or even OutOfMemoryExceptions
                    using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
                    {
                        if (await reader.ReadAsync())
                        {
                            if (!(await reader.IsDBNullAsync(0)))
                            {
                                using (Stream data = reader.GetStream(0))
                                {
                                    // Asynchronously copy the stream from the server to the response stream
                                    await data.CopyToAsync(responseStream);
                                }
                            }
                        }
                    }
                }
            }
        }// close response stream
    }
}