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?

score 0 · Answer 1 · edited May 23 '17 at 12:09

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.

While that is working, reading it into a byte[] is not a tempting option. That way I completely loose the streaming benefits and will run into major trouble when doing this for large files. — magnattic, Aug 09 '13 at 18:08
I solved it now by using CommitableTransaction instead of TransactionScope and disposing it late in the Controllers dispose method. (instead of using a "using" block) Can you maybe elaborate on your Dependency Injection solution? How exactly would that work? Can you give an example? — magnattic, Aug 09 '13 at 18:11
There are many examples of dependency injection / inversion of control lifetime scoping for `DbContext` with MVC and WebAPI controller constructor injection on the web. You shouldn't need my help to find one. — danludwig, Aug 09 '13 at 18:13
Ok thanks. The problem lies with the Transaction though, because the DBContext is not needed for the SqlFileStream. — magnattic, Aug 09 '13 at 18:15

Stream SqlFileStream via WebAPI

1 Answers1