Description
This article will show you how to buffer data into a MemoryStream from a query and output the buffered data back to the browser as a text file.
Introduction
There are many occasions which require web developers to provide downloadable content to end-users in the form of text files, Excel files, etc. This is pretty straight forward if the files already exist and are physically located somewhere the user can access via the browser. However, this requirement becomes a little more complicated if the data that comprises the file is dynamically generated based on user input. An example of this is an application that allows a user to select the number of widgets their company produced between DateA and DateB and then have that data immediately downloaded as a text file. In this situation the text file has to be created on the fly and sent to the browser.
Meeting the requirement is pretty straightforward. The following example shows you how to query a SQL Server database, buffer the output to a MemoryStream, and then have that data transferred to the browser as a Tab-Delimited text file. here.
The Code:
The first step in this example is to query the data out of the database and put it into a MemoryStream. The following function give shows how this can be done.
private MemoryStream GetData()
{
//Create the return memorystream object that will hold
//the buffered data.
MemoryStream ReturnStream = new MemoryStream();
//Put together the connection string
string cn = "Persist Security Info=False;Integrated Security=false;User ID=sa;Pwd=foo;database=northwind;server=foo;Connect Timeout=30";
try
{
//Get the sql string
string SqlString = "select * from dbo.Orders";
//Create a new command object
SqlCommand sc = new SqlCommand();
//Set the connection
sc.Connection = new SqlConnection(cn);
//Open the connection
sc.Connection.Open();
//Set the command type
sc.CommandType = CommandType.Text;
//Set the SQL String
sc.CommandText = SqlString;
//Execute the SQL Statement/Procedure
SqlDataReader RS = sc.ExecuteReader();
//Create a streamwriter to write to the memory stream
StreamWriter sw = new StreamWriter(ReturnStream);
if (RS.Read())
{
//Create a header for the file from the names of the
//columns that we are retrieving
string tempstring = "";
for (int counter = 0; counter < RS.FieldCount;counter++)
{
tempstring += RS.IsDBNull(counter)?"":RS.GetName(counter) + "\t";
}
//Write the row of data to the Memory Stream.
sw.WriteLine(tempstring);
//Loop through the data row and write the contents to the memory
//stream.
while (RS.Read())
{
//Put each columns' data value into a string separated by a tab.
tempstring = "";
for (int counter = 0; counter < RS.FieldCount;counter++)
{
tempstring += RS.IsDBNull(counter)?"":RS.GetValue(counter).ToString() + "\t";
}
//Write the row of data to the Memory Stream.
sw.WriteLine(tempstring);
}
}
//Clean up the stream writer
sw.Flush();
sw.Close();
//Clean up the data reader
RS.Close();
//Clean up the command object
sc.Connection.Close();
sc.Dispose();
}
catch (Exception Ex)
{
throw Ex;
}
//Return the memory Stream
return ReturnStream;
}
The previous function solves the requirement of dynamically retrieving data from a database and buffering the data. Now the only thing left to do is to take the buffered data and send it on its way to the client as a text file. The following snippet shows how this is done.
private void Page_Load(object sender, System.EventArgs e)
{
//Create and populate a memorystream with the contents of the
//database table
System.IO.MemoryStream mstream = GetData();
//Convert the memorystream to an array of bytes.
byte[] byteArray = mstream.ToArray();
//Clean up the memory stream
mstream.Flush();
mstream.Close();
// Clear all content output from the buffer stream
Response.Clear();
// Add a HTTP header to the output stream that specifies the default filename
// for the browser's download dialog
Response.AddHeader("Content-Disposition", "attachment; filename=foo.xls");
// Add a HTTP header to the output stream that contains the
// content length(File Size). This lets the browser know how much data is being transfered
Response.AddHeader("Content-Length", byteArray.Length.ToString());
// Set the HTTP MIME type of the output stream
Response.ContentType = "application/octet-stream";
// Write the data out to the client.
Response.BinaryWrite(byteArray);
}
Summary
As you can see this solution is pretty straight forward. Through the use of a memorystream and header manipulation you are able to dynamically retrieve data from a database and send it to the browser as a file.
One thing to note is that this example applies this methodology using an ASP.NET page. Though this will work, you will notice that once the text file has been downloaded the page ASP.NET still remains in the browser. This is caused by the fact that server has already sent its response back to the browser in the form of the text file. There are a couple of different ways to get around this orphaned page. One is to implement this methodology using an http handler, rather than an ASP.NET page. Another is to use post backs to the page. I will be posting examples of these solutions in the near future.