Stream SqlFileStream via WebAPI

Question

I have setup a SQL database with FILESTREAM support and am trying to stream files retrieved from the database with SqlFileStream through WebAPI to the browser.

For some reason it does not work, but I get no proper error message. The browser just aborts the connection, Fiddler does not show anything useful either, and no error seems to be thrown in VS.

public HttpResponseMessage Get(Guid id)
{
    if(id == null || id == Guid.Empty)
        return Request.CreateResponse(HttpStatusCode.BadRequest);

    try
    {
        FileStreamContext fsc = null;
        Document document = null;
        using(var transaction = new TransactionScope())
        using (var db = new MyEntities())
        {
            try
            {
                fsc = db.Database.SqlQuery<FileStreamContext>("SELECT [File].PathName() AS InternalPath, GET_FILESTREAM_TRANSACTION_CONTEXT() AS TransactionContext FROM Document WHERE id={0}", id).First();
            }
            catch (Exception e)
            {
                Debug.Print(e.ToString());
            }

            document = db.Documents.Where(doc => doc.ID == id).Single();

            var fileStream = new SqlFileStream(fsc.InternalPath, fsc.TransactionContext, FileAccess.Read);

            HttpResponseMessage response = new HttpResponseMessage();
            response.Content = new StreamContent(fileStream);
            //response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
            //response.Content.Headers.ContentDisposition.FileName = document.FileName;
            response.Content.Headers.ContentType = MediaTypeHeaderValue.Parse(document.ContentType);
            return response;
        }
    }
    catch (Exception e)
    {
        return Request.CreateResponse(HttpStatusCode.BadRequest);
    }
}

I suspect it might be a problem with the TransactionScope being closed to early? Not sure why I don't get any error messages though.

What is the proper way to stream a SqlFileStream over WebApi?

Answer 1

Instead of setting the content as a StreamContent, have you tried reading the fileStream in the controller, putting it into a byte array, and then setting the content using a ByteArrayContent?

var fileStream = new SqlFileStream(fsc.InternalPath, fsc.TransactionContext, FileAccess.Read);
byte[] fileContent = fileStream.ReadFully(); // you will need to implement ReadFully
HttpResponseMessage response = new HttpResponseMessage();
response.Content = new ByteArrayContent(fileContent);

For the ReadFully implementation, here is a link to a Jon Skeet method that I have used.

And I suspect you are right about the connection being closed too early. You have your DbContext / ObjectContext wrapped nicely in a using block, but I think that is the problem. That context is being disposed after the controller returns the HttpResponseMessage, but the response only has access to the stream -- it still needs to read the stream and convert it into a byte[] before passing back to the browser or client. That usually happens automatically when you are using a MemoryStream or the like, because the framework still can access the stream to read its contents. In this case though, you are disposing of your SQL connection before the stream can be read.

Another solution could be to use dependency injection for your DbContext instead of newing it up in the action method. If you set up your IoC container to automatically dispose of the context at the end of the HTTP request, then it should still be available (undisposed) when the framework goes to convert the StreamContent into a byte[] and push it back out over the network.