Downloading >10,000 rows from database table in asp.net

Question

How should I go about providing download functionality on an asp.net page to download a series of rows from a database table represented as a linq2sql class that only has primitive types for members (ideally into a format that can be easily read by Excel)?

E.g.

public class Customer
{
public int CustomerID;
public string FirstName;
public string LastName;
}

What I have tried so far.

Initially I created a DataTable, added all the Customer data to this table and bound it to a DataGrid, then had a download button that called DataGrid1.RenderControl to an HtmlTextWriter that was then written to the response (with content type "application/vnd.ms-excel") and that worked fine for a small number of customers.

However, now the number of rows in this table is >10,000 and is expected to reach upwards of 100,000, so it is becoming prohibitive to display all this data on the page before the user can click the download button.

So the question is, how can I provide the ability to download all this data without having to display it all on a DataGrid first?

Answer 1

After the user requests the download, you could write the data to a file (.CSV, Excel, XML, etc.) on the server, then send a redirect to the file URL.

Answer 2

Why not allow them to page through the data, perhaps sorting it before paging, and then give them a button to just get everything as a cvs file.

This seems like something that DLinq would do well, both the paging, and writing it out, as it can just fetch one row at a time, so you don't read in all 100k rows before processing them.

So, for cvs, you just need to use a different LINQ query to get all of the rows, then start to save them, separating each cell by a separator, generally a comma or tab. That could be something picked by the user, perhaps.

Answer 3

OK, I think you are talking too many rows to do a DataReader and then loop thru to create the cvs file. The only workable way will be to run:

SQLCMD -S MyInstance -E -d MyDB -i MySelect.sql -o MyOutput.csv -s

For how to run this from ASP.Net code see here. Then once that is done, your ASP.Net page will continue with:

    string fileName = "MyOutput.csv";
    string filePath = Server.MapPath("~/"+fileName);
    Response.Clear();        
    Response.AppendHeader("content-disposition", 
"attachment; filename=" + fileName);        
    Response.ContentType = "application/octet-stream";        
    Response.WriteFile(filePath);        
    Response.Flush();        
    Response.End();

This will give the user the popup to save the file. If you think more than one of these will happen at a time you will have to adjust this.

Answer 4

I have used the following method on Matt Berseth blog for large record sets.

Export GridView to Excel

If you have issues with the request timing out try increasing the http request time in the web.config

Answer 5

Besides the reasonable suggestion to save the data on server first to a file in one of the answers here, I would like to also point out that there is no reason to use a DataGrid (it’s one of you questions as well). DataGrid is overkill for almost anything. You can just iterate over the records, and save them directly using HtmlTextWriter, TextWriter (or just Response.Write or similar) to the server file or to a client output stream. It seems to me like an obvious answer, so I must be missing something.

Given the number of records, you may run into a number of problems. If you write directly to the client output stream, and buffer all data on server first, it may be a strain on the server. But maybe not; it depends on the amount of memory on the serer, the actual data size and how often people will be downloading the data. This method has the advantage of not blocking a database connection for too long. Alternatively, you can write directly to the client output stream as you iterate. This may block the database connection for too long as it depends on the download speed of the client. But again; it your application is of a small or medium size (in audience) then anything is fine.

Answer 6

You should definitely check out the FileHelpers library. It's a freeware, excellent utility set of classes to handle just this situation - import and export of data, from text files; either delimited (like CSV), or fixed width.

It offer a gazillion of options and ways of doing things, and it's FREE, and it works really well in various projects that I'm using it in. You can export a DataSet, an array, a list of objects - whatever it is you have.

It even has import/export for Excel files, too - so you really get a bunch of choices.

Just start using FileHelpers - it'll save you so much boring typing and stuff, you won't believe it :-)

Marc

Answer 7

So after a bit of research, the solution I ended up trying first was to use a slightly modified version of the code sample from http://www.asp.net/learn/videos/video-449.aspx and format each row value in my DataTable for CSV using the following code to try to avoid potentially problematic text:

private static string FormatForCsv(object value)
        {
            var stringValue = value == null ? string.Empty : value.ToString();
            if (stringValue.Contains("\"")) { stringValue = stringValue.Replace("\"", "\"\""); }
            return "\"" + stringValue + "\"";
        }

For anyone who is curious about the above, I'm basically surrounding each value in quotes and also escaping any existing quotes by making them double quotes. I.e.

My Dog => "My Dog"
My "Happy" Dog => "My ""Happy"" Dog"

This appears to be doing the trick for now for small numbers of records. I will try it soon with the >10,000 records and see how it goes.

Edit: This solution has worked well in production for thousands of records.

Answer 8

Just a word of warning, Excel has a limitation on the number of rows of data - ~65k. CSV will be fine, but if your customers are importing the file into Excel they will encounter that limitation.