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;
    }
}

Did you consider using IAsyncEnumerable<T> (requires .NET Core 3.0 though). It was made specificially to support async enumeration of data, like in this case — Tseng
– Tseng, Commented Oct 25, 2019 at 5:55
Other than that, use the using pattern for resources you allocated by yourself — Tseng
– Tseng, Commented Oct 25, 2019 at 6:03
Inside the controller endpoint: Response.RegisterForDisposeAsync(...) > "Registers an object for asynchronous disposal by the host once the request has finished processing." — AnorZaken
– AnorZaken, Commented Oct 8, 2024 at 22:35

Nick Albrecht · Accepted Answer · 2019-10-29 18:47:41Z

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.

Tseng · Accepted Answer · 2019-10-25 09:19:25Z

3

After posting the comment I also remembered that there's an rarely used API.

You can register any disposable classes to be disposed at the end of the request (when the response has been written):

HttpContext.Response.RegisterForDispose(reader);

Alternatively hook into the OnCompleted response callback and do your cleanups there (i.e. if objects are not disposable or if you need to call special methods as part of the clean up)

HttpContext.Response.OnCompleted(() =>
{
    reader.Dispose();
    return Task.CompletedTask;
});

Last but not least, if done in a lot of methods, having your own SqlSequencialStreamingResult class may be your best choice

edited Oct 25, 2019 at 9:19

answered Oct 25, 2019 at 7:40

Tseng

64.5k18 gold badges200 silver badges215 bronze badges

5 Comments

Nick Albrecht Over a year ago

I didn't know about the RegisterForDispose, that's interesting. The OnComplete seems like an interesting fallback but doesn't feel as clean.

Nick Albrecht Over a year ago

Yeah, I thought of the idea of creating my own SqlSequencialStreamingResult as I was falling alseep last night 😅. Then I could register the dispose of the command/reader within the dispose of the custom ActionResult

Nick Albrecht Over a year ago

Ha, the definition of RegisterForDispose, it just adds a delegate to OnCompleted. So it's the same thing just cleaner syntax

Tseng Over a year ago

RegisterForDispose -> Cleaner syntax for one, but more importantly its easier discoverable via auto completition when it says RegisterForDispose you know what its for but its not obvious when you have to do HttpContext.Response.OnCompleted(...) that you can dispose in there too

Nick Albrecht Over a year ago

Seems like RegisterForDispose doesn't fire until the very end, so it's not executed before I try to clean up my transaction in my ActionFilterAttribute. I had to go the route of a custom ActionResult. I'll post my code for that once I get it all working 100%

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related