Streaming data from the database - ASP.NET Core & SqlDataReader.GetStream()

Question

I'm trying to minimize loading large objects into memory from the database when sending them out from an ASP.NET Core site because I'm hitting OutOfMemoryException on occasion.

I figured I'd stream it. Now from my research SQL Server supports this so long as you've specified CommandBehavior.SequentialAccess in your command. I figured if I'm going to stream it, I might as well stream it as directly as possible, so I'm pretty much streaming it directly from the DataReader to the ASP.NET MVC ActionResult.

But once the FileStreamResult (hidden under the call to File()) has finished executing, how do I clean up my reader/command? The connection was provided by DI, so that's not a problem, but I create the reader/command in the call to GetDocumentStream().

I have a subclass of ActionFilterAttribute registered in MVC, so that gives me an entry point I can call for ActionFilterAttribute.OnResultExecuted(), but I have absolutely no idea on what to put there other than my current logic that deals with cleaning up my database transaction and commit/rollback stuff (not included since it's not really relevant).

Is there a way to cleanup after my DataReader/Command and still provide a Stream to File()?

public class DocumentsController : Controller
{
    private DocumentService documentService;

    public FilesController(DocumentService documentService)
    {
        this.documentService = documentService;
    }

    public IActionResult Stream(Guid id, string contentType = "application/octet-stream") // Defaults to octet-stream when unspecified
    {
        // Simple lookup by Id so that I can use it for the Name and ContentType below
        if(!(documentService.GetDocument(id)) is Document document) 
            return NotFound();

        var cd = new System.Net.Http.Headers.ContentDispositionHeaderValue("inline") {FileNameStar = document.DocumentName};
        Response.Headers.Add(Microsoft.Net.Http.Headers.HeaderNames.ContentDisposition, cd.ToString());

        return File(documentService.GetDocumentStream(id), document.ContentType ?? contentType);
    }

    /*
    public class Document
    {
        public Guid Id { get; set; }
        public string DocumentName { get; set; }
        public string ContentType { get; set; }
    }
    */
}

public class DocumentService
{
    private readonly DbConnection connection;

    public DocumentService(DbConnection connection)
    {
        this.connection = connection;
    }

    /* Other content omitted for brevity */

    public Stream GetDocumentStream(Guid documentId)
    {
        //Source table definition
        /*
            CREATE TABLE [dbo].[tblDocuments]
            (
                [DocumentId] [uniqueidentifier] NOT NULL,
                [ContentType] [varchar](100) NULL,
                [DocumentName] [varchar](100) NULL,
                [DocumentData] [varbinary](max) NULL
            CONSTRAINT [PK_DocumentId] PRIMARY KEY NONCLUSTERED ([DocumentID] ASC)
            )
            GO
         */

        const string query = "SELECT DocumentData FROM tblDocuments WHERE DocumentId = @documentId";

        //build up the command
        var command = connection.CreateCommand();
        command.CommandText = query;
        var parameter = command.CreateParameter();
        parameter.DbType = System.Data.DbType.Guid;
        parameter.ParameterName = "@documentId";
        parameter.Value = documentId;
        command.Parameters.Add(parameter);

        //Execute commmand with SequentialAccess to support streaming the data
        var reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

        if(reader.Read())
            return reader.GetStream(0);
        else
            return Stream.Null;
    }
}

Answer 1

So I worked my way though all of @Tseng's suggestions, simple ones first, ending with the more involved one. Ultimately I couldn't use the method of registering an object to be disposed for me, because it happens too late in the process and I am cleaning up my database transaction in the OnResultExecuted method of my ActionFilter. So I went for the custom ActionResult class.

The StreamableDisposable (and its interface) are just to simplify the fact that I need to return two things, the stream and the datareader, and didn't want to expose the reader's underlying GetStream(0) method by only returning the reader.

public interface IStreamableDisposible : IDisposable
{
    public System.IO.Stream Stream { get; }
}

public class StreamableDisposible : IStreamableDisposible
{
    private readonly IDisposable toDisposeOf;

    public StreamableDisposible(System.IO.Stream stream, System.Data.Common.DbDataReader toDisposeOf)
    {
        Stream = stream ?? throw new ArgumentNullException(nameof(stream));
        this.toDisposeOf = toDisposeOf;
    }

    public System.IO.Stream Stream { get; set; }

    public void Dispose()
    {
        toDisposeOf?.Dispose();
    }
}

This is the new ActionResult class so that I can ensure that my disposable is cleaned up immediately after the stream is finished being used to execute the result.

public class DisposingFileStreamResult : FileStreamResult
{
    private readonly IStreamableDisposible streamableDisposible;

    public DisposingFileStreamResult(IStreamableDisposible streamableDisposible, string contentType)
        : base(streamableDisposible.Stream, contentType)
    {
        this.streamableDisposible = streamableDisposible ?? throw new ArgumentNullException(nameof(streamableDisposible));
    }

    public override void ExecuteResult(ActionContext context)
    {
        base.ExecuteResult(context);
        streamableDisposible.Dispose();
    }

    public override Task ExecuteResultAsync(ActionContext context)
    {
        return base.ExecuteResultAsync(context).ContinueWith(x => streamableDisposible.Dispose());
    }
}

This let me update my GetDocumentStream() method to be as follows

public StreamableDisposible GetDatabaseStream(Guid documentId)
{
    const string query = "SELECT DocumentData FROM tblDocuments WHERE DocumentId = @documentId AND DocumentData IS NOT NULL AND DATALENGTH(DocumentData) > 0";
    using var command = ((NHibernateData)Data).ManualCommand();

    command.CommandText = query;
    var parameter = command.CreateParameter();
    parameter.DbType = System.Data.DbType.Guid;
    parameter.ParameterName = "@documentId";
    parameter.Value = documentId;
    command.Parameters.Add(parameter);

    //Execute commmand with SequentialAccess to support streaming the data
    var reader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
    if(reader.Read())
        return new StreamableDisposible(reader.GetStream(0), reader);
    else
    {
        reader.Dispose();
        return null;
    }
}

And my action now looks like this

public IActionResult Stream(Guid id, string contentType = "application/octet-stream") // Defaults to octet-stream when unspecified
{
    // Simple lookup by Id so that I can use it for the Name and ContentType below
    if(!(documentService.GetDocument(id)) is Document document) 
        return NotFound();

    var cd = new System.Net.Http.Headers.ContentDispositionHeaderValue("inline") {FileNameStar = document.DocumentName};
    Response.Headers.Add(Microsoft.Net.Http.Headers.HeaderNames.ContentDisposition, cd.ToString());

    var sd = var sd = documentService.GetDocumentStream(id);
    return new DisposingFileStreamResult(sd, document.ContentType ?? contentType);
}

I added checks to the SELECT statement to account for null data columns, or just null data length to eliminate having to have checks for the both StreamableDisposable and for the Stream itself being null, or just possibly no data, etc.

This is pretty much all of the code I ended up using.