Streaming millions of rows as a csv in ASP.NET MVC

Generating reports using the data from the database is something any project will generally have, but to be able to actually download all of the data as a csv is not easy. Not just thousands of rows but if database has millions of rows then challange will be to decide best approach. Consider following approaches

1. Generate the report on server and then stream the file to clients machine

2. Stream data in batches onto client machine

First approach is not a good idea as there is no feedback to the user when download will actually start, but with second option user will actually see the streaming immediately.

So let's look at option 2 and how that can be achieved

1. Define a custom result that derives from FileResult

public class CsvResult : FileResult
{
   protected override void WriteFile(HttpResponseBase response)
   {
            response.Clear();
            response.ClearContent();
            response.BufferOutput = false;
            var streamWriter = new StreamWriter(response.OutputStream, Encoding.UTF8);
            //write the header line first
            streamWriter.WriteLine("InvoiceDate,InvoiceNumber,Quantity,Price");
            long count = GetInvoiceRecCount();
            
            long batchSize = 10000;
            long numberOfBatches = count / batchSize;
            long startRow = 0;
            for (int i = 0; i < numberOfBatches; i++)
            {
                startRow = i * batchSize;
                //Get batch of rows
                var invoiceRows = GetInvoiceRows(startRow, batchSize);
                foreach(DataRow row in invoiceRows)
                {
                    //write data 
                    streamWriter.WriteLine(dataLine);
                }

                streamWriter.Flush();
            }

            if (startRow + batchSize < count)
            {
                batchSize = count - startRow;
                //Get batch of rows
                var invoiceRows = GetInvoiceRows(startRow, batchSize);
                //write final batch
                foreach (DataRow row in invoiceRows)
                {
                    //write data 
                    streamWriter.WriteLine(dataLine);
                }

                streamWriter.Flush();
            }
   }

}

2. Now write controller action

public ActionResult ExportInvoicesToCsv()
{
   CsvResult csvResult = new CsvResult();
   csvResult.FileDownloadName = "InvoiceReport.csv";
   return csvResult;
}

And that's it all done. Now when user tries to download millions of rows as csv they are batches in the size of 10000 and will be streamed to users machine.

Technology is just a tool. In terms of getting the kids working together and motivating them, the teacher is the most important.
Bill Gates