CODEWITHSUNDEEP
asp.netvb.netcsvasp.net-web-apistreamwriter
Nse
Nse

Reputation: 307

Correct way of streaming a CSV of unknown size from memory

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.

Upvotes: 2

Views: 1090

Answers (1)

Nse
Nse

Reputation: 307

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

Upvotes: 2

Related Questions