How can I efficiently stream large data from SQL Server 2008 to a web browser?

Question

I'm using C# and ASP.NET MVC 3. Currently, I'm taking a large set of data from SQL Server, adding it into a StringBuilder, and then using System.Text.ASCIIEncoding to convert the StringBuilder object to a byte[] array.

I then send the byte[] array to the view. However, the amount of data is so large, I get the following exception:

Exception of type 'System.OutOfMemoryException' was thrown

Here's the code that I use in my controller to convert the StringBuilder object to byte[] and then return to the view. Is there a way to stream the data straight from the database to the client browser without loading it all up in memory on the server first?

System.Text.ASCIIEncoding encoding = new ASCIIEncoding();
return File(encoding.GetBytes(csv.ToString()), "text/csv", "Query_Export.csv");

Answer 1

You can do something similar to the following pseudo code

  while (sqlReader.GetBytes(params))//params is a placeholder for the actual arguments and
                                    //will have a byte array buffer and some counter indexes
  {
      context.Response.BinaryWrite(buffer);
      //somecounter
  }

SqlDataReader http://msdn.microsoft.com/en-in/library/system.data.sqlclient.sqldatareader.aspx