Export to CSV ASP.NET C#

[HungryHippos]

Trying to export data from one of my ASP.NET C# pages to CSV, seems there is a lot of info out there about this but nothing I can make any sense of!

TLDR:

How can I call my Stored Procedure query and return the results into a .CSV file which the end user can either download or save? this is from my ASP.NET C# page

More Info:

Here's how the page currently works:

1. I have a DataSet .XSD file which contains my TableAdapter.
   
2. My TableAdapter contains my Stored Procedures from SQL 2008.
   
3. My .ASPX page contains an ObjectDataSource which links to the Stored Procedure in my TableAdapter
   
4. My .ASPX page also has a GridView which gets it's data from the ObjectDataSource.
   
5. The GridView uses custom paging to ensure that it only retrieves the records that are displayed for performance reasons. so if there are 1000 records in total, and 100 per page, with 10 pages, the Stored Procedure returns only 100 records and then each time another page is selected those records are returned etc.

Phew! with that out of the way, everything is working great so far. My Stored Procedures work just fine and all the data is returned as expected.

So the next thing I want to do is let people download all the records returned by the query to CSV file. You know like when you visit a website and click a download to CSV button, and it gives you the CSV file and says to open or save?

I want to put the code to do this into my code behind page inside of a button_click event.

I thought this would be simple, but it doesn't seem to be.

I am coding this site in Visual Studio 2010 and I have only been using VS for a week or two so getting to grips with it, in general I would say I am a novice with this as i'm still trying to figure things out.

I don't necessarily want to return the results from the gridview becuase as stated the gridview only has the displayed records anyway.

There must be some way to call my TableAdapter query and hold the results in some kind of temp object, then transfer them to .CSV?

 

[Pho]

You could export to XML which I imagine Excel will read as a table, there are built in functions in the DataSet class to do it for you:
http://msdn.microsoft.com/en-us/library/system.data.dataset.writexml.aspx


Or there's CsvHelper which can help you write CSV - never used it though.

 

[Delboy]

Quick and simple...

1. For each record in your tableadapter, write a comma seperated line to a csv file to a folder within your website. The file should have a unique name.
2. Present the link to the user so they can click and download it.
3. Clean up any csv files created.

 

[HungryHippos]

Sorted it myself

        protected void Button1_Click(object sender, EventArgs e)
        {
            // Remove paging to allow for exporting all rows
            GridViewUserData.AllowPaging = false;
            GridViewUserData.DataBind();

            // Define context variable
            HttpContext context = HttpContext.Current;

            // Clear response content & headers
            context.Response.Clear();
            context.Response.ClearContent();
            context.Response.ClearHeaders();

            // Define string
            string ConcatenateColumns = "";

            // Loop through the columns
            for (int ColumnCount = 0; ColumnCount < GridViewUserData.Columns.Count; ColumnCount++)
            {
                if (GridViewUserData.Columns[ColumnCount].Visible == true)
                {
                    ConcatenateColumns += "\"" + GridViewUserData.Columns[ColumnCount].HeaderText + "\"" + ",";
                }
            }

            // Write the column headings
            context.Response.Write(ConcatenateColumns);

            // Loop through the rows in the gridview
            foreach (GridViewRow row in GridViewUserData.Rows)
            {
                // New variable each row
                string ConcatenateRows = "";

                // Loop through each value of each row
                for (int ColumnCount = 0; ColumnCount < GridViewUserData.Columns.Count; ColumnCount++)
                {
                    if (GridViewUserData.Columns[ColumnCount].Visible == true)
                    {
                        ConcatenateRows += "\"" + row.Cells[ColumnCount].Text + "\"" + ",";
                    }
                }

            // Write row contents
            context.Response.Write(Environment.NewLine);
            context.Response.Write(ConcatenateRows);
            }

            // File attachment settings
            context.Response.ContentType = "text/csv";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=mailboxes.csv");
            context.Response.End();

            // Re-enable paging
            GridViewUserData.AllowPaging = true;
            GridViewUserData.DataBind();
        }

Also had to add this to my SQL Stored Procedure because otherwise with paging disabled it returned 0 rows every time

            If @maximumRows = 0
                  SET @maximumRows = 10000

 

[Pho]

What happens if row.Cells[ColumnCount].Text contains quotes/commas? .

 

[HungryHippos]

The "\"" bit seems to escape the text before and after the value, seems to work, in my CSV file exports even if the row contains a , it works and is contained in the cell correctly

The only thing it doesn't like is when the cell is empty, it leaves it as   for some reason! may have to tinker with it a little to get around that

EDIT: fixed it, just changed this line:

ConcatenateRows += "\"" + (row.Cells[ColumnCount].Text).Replace(" ","") + "\"" + ",";