Export large amounts of data to client in asp.net

Question

Hi, I need to export a large amount of data (~100mb) from a sql table to a user via the web. What would be the best solution for doing so? One thought was to export the data to a folder on the db server, compress it (by some means) and then provide a download link for the user. Any other methods for doing so? Also, can we compress data from within sql server?

Any approaches are welcome.

Answer 1

The download link is a perfectly valid and reasonable solution. Another would be to automatically redirect the user to that file so they didn't need to click a link. It really depends on your workflow and UI experience.

I would suggest against implementing compression in the SQL Server engine. Instead look at the DotNetZip library (Or System.IO.Conpression if you think your users have the capability of uncompressing gzip archives) and implement the compression within the web application.

Answer 2

If XML is OK, one approach would be to select the data "FOR XML" like this: http://www.sqljunkies.ddj.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk

And then spit out the raw XML directly to the browser as content-type: text/xml. Also be sure to set up Gzip compression on your web server for files with XML extensions. http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/502ef631-3695-4616-b268-cbe7cf1351ce.mspx?mfr=true

This will shrink the XML file down to 1/3 or maybe 1/4 the size as it's transferred. This wouldn't be the highest performance option because of the inherent wasted space in XML files, but a lot depends on what format you're looking for in the end.

Another option would be to use the free CSharpZipLib to compress the XML (or whatever format you want) into a zip file that the user would download. Along those lines, if this is something that will be used frequently you might want to look into caching and storing the zip file on the web server with some sort of expiration so it's not regenerated for every single request.

Answer 3

You can respond to a page request with a file:

Response.AddHeader("Content-Disposition", 
    "attachment; filename=yourfile.csv");
Response.ContentType = "text/plain";

Be sure to turn buffering off, so IIS can start sending the first part of the file while you are building the second:

Response.BufferOutput = false;

After that, you can start writing the file like:

Response.Write("field1,field2,field3\r\n");

When the file is completely written, end the response, so ASP.NET doesn't append a web page to your file:

Response.End();

This way, you don't have to write files on your web servers, you just create the files in memory and send them to your users.

If compression is required, you can write a ZIP file in the same way. This is a nice free library to create ZIP files.

Answer 4

Your approach works fine. SSIS + 7zip might be useful for automating the process if you need to do it more than a couple times.

Answer 5

I wouldn't tie up the database waiting for the user to download 100Mb, even for a high speed user. When the user requests the file have them specify an email address. Then call an asynch process to pull the data, write it to a temp file (don't want > 100mb in memory after all), then zip the temp file to a storage location, then send the user an email with a link to download the file.