Correct way of streaming a CSV of unknown size from memory

Question

I have a database that has a table with just over 2 million records of about 20 columns. The user is able to query the database and limit the number of records returned so the recordset may be from 1 to 2 million.

As it is tabular information I want to send the data as a CSV. I'm using a StreamWriter to write the data to memory and once the file is complete I'm sending it as an HttpResponseMessage. My code is below, and it works fine as long as I don't run out of memory. Is there a way for me to stream the file as it's being processed so that the memory used is minimal?

<HttpGet, Route("api/records/export")>
Public Function ExportRecords() As HttpResponseMessage

    Dim stream As New MemoryStream
    Dim writer As New StreamWriter(stream)
    writer.WriteLine("")

    ' Processing of data here
    writer.WriteLine("""Write Data to MemoryStream"")

    writer.Flush()
    stream.Position = 0

    Dim result As New HttpResponseMessage(HttpStatusCode.OK)
    result.Content = New StreamContent(stream)
    result.Content.Headers.ContentType = New Headers.MediaTypeHeaderValue("text/csv")
    result.Content.Headers.ContentDisposition = New Headers.ContentDispositionHeaderValue("attachment") _ 
        With {.FileName = "I" & Format(Date.Now, "yyMMdd") & ".csv"}

    Return result

End Function

I've read on StackOverflow answers to questions such as Returning binary file from controller in ASP.NET Web API but these all deal with streaming a web response from a file stored on disk and not from memory.

You could just write to Response.OutputStream and skip the memorystream. — dwilliss, Nov 29 '17 at 22:58
You can use PushStreamContent from the example you linked, you just need to replace your MemorySteam with the stream parameter from the PushStreamContent and omit the `stream.Position = 0` statement. It would be something like (please excuse me for using C# here): `result.Content = new PushStreamContent((stream, httpContent, transportContext) => { var writer = new StreamWriter(stream); \* Rest of your code *\ writer.Flush(); stream.Close(); })m "text/csv");` — ckuri, Nov 29 '17 at 23:03

score 2 · Accepted Answer · answered Jan 02 '18 at 23:38

As the comment on my question suggested I used PushStreamContent to stream the contents of my database to the browser in CSV format. I also made it asynchronous to get even more performance out of the export.

There is an important limitation with using PushStreamContent to stream content to the client from the server. Since the 200 OK header is sent first before anything is streamed, the response doesn't know ahead of time if there's going to be an error in the results as they're returned. If something goes wrong while the results are being sent, the client will just see a generic network error on its end. It's up to the server to log any error so that you can check server logs to find the specific error.

Here is the code for PushStreamContent that I used (the error checking is removed for brevity).

Dim result As HttpResponseMessage = New HttpResponseMessage(HttpStatusCode.OK) With {
    .Content = New PushStreamContent(Async Function(outStream, httpContent, context)
        Dim writer As StreamWriter = New StreamWriter(outStream)
        Await writer.WriteLineAsync("""Header 1"",""Header 2"",""Header 3""")
        For Each item In returnItems
            Await writer.WriteLineAsync("""" & item.Col1.ToString & """,=""" & item.Col2.ToString & """,=""" & item.Col3.ToString & """")
            Await writer.FlushAsync()
        Next
        outputStream.Close()
    End Function)}

result.Content.Headers.ContentType = New Headers.MediaTypeHeaderValue("text/csv")
result.Content.Headers.ContentDisposition = New _
  Headers.ContentDispositionHeaderValue("attachment") With {.FileName = "MyCSV.csv"}

Return result

Correct way of streaming a CSV of unknown size from memory

1 Answers1