Exporting only filtered data from gridview asp.net

Question

So I've managed to get my admin area to export for the user to download into an xml file format. However I've put in place some filtering options so the user logged in can narrow down the results viewable. What I'm trying to acheive is then after the user applies these filters to be able to then export only the filtered data.

Code Below:

protected void ExportData_Click(object sender, EventArgs e)
    {
        string consString = ConfigurationManager.ConnectionStrings["TortoiseDBConnectionString"].ConnectionString;
        StringBuilder sb = new StringBuilder();
        using (SqlConnection con = new SqlConnection(consString))
        {
            con.Open();
            string sql = ("SELECT [ID], [HouseNumber], [PropAddress], [Town], [County], [PostCode] FROM Zoopla;");
            SqlCommand cmd = new SqlCommand(sql, con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            GridView1.DataBind();
            cmd.Dispose();
            con.Close();


            string filename = "DownloadTest.xml";
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            DataGrid dgGrid = new DataGrid();
            dgGrid.DataSource = dt;
            dgGrid.DataBind();

            dgGrid.RenderControl(hw);

            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
            this.EnableViewState = false;
            Response.Write(tw.ToString());
            Response.End();
        }
    }

Filters are set as follows:

 Filter By Weeks:
    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="TortoiseDBZoopla" DataTextField="Weeks" DataValueField="Weeks">

    </asp:DropDownList>
    Filter By Status:
    <asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True" DataSourceID="ZooplaProperties" DataTextField="PropStatus" DataValueField="PropStatus">

    </asp:DropDownList>

You've really got some bad file handling procedures in there. You're rendering a GridView to HTML, serving it with an Excel MIME type and a .XML extension? Instead of thinking of it as "exporting a GridView" why not think of it as exporting filtered data? Get the data from the source, apply the filters, then use a managed library to export as an actual HTML, XML, or .XLSX file rather than some strange hodgepodge? Also, you're leaving the SqlConnection open during the entire export. You should get out of the using statement as soon as you're done retrieving your data. — mason, Sep 11 '15 at 13:17
Well I do think of it as exporting filtered data. That's what I'm trying to achieve...The gridview is merely for viewership to which the admin can use to apply filters and then export the filtered data to use in another application. Could you recommend a better syntax to handle this process? All this button is supposed to do is export into an xml file, what the viewable data. I've made an edit to my code closing the sql connection earlier once the data has been retrieved. — SavageDragon87, Sep 11 '15 at 13:58
Sure. Pull your data directly from the database, do not put it in a GridView or DataGrid (because those are only for displaying), use an [XML serializer library to generate an XML file](http://stackoverflow.com/questions/2948255/xml-file-creation-using-xdocument-in-c-sharp), serve it up with the .xml extension and the [appropriate MIME type](http://stackoverflow.com/questions/4832357/whats-the-difference-between-text-xml-vs-application-xml-for-webservice-respons). — mason, Sep 11 '15 at 14:02

score 0 · Answer 1 · answered Sep 11 '15 at 14:12

0

Your gridview is filtered, but when you are exporting, you are running a new SQL query without the filters. You will need to either add a where clause to your query that filters the results, or persist your gridview datasource somehow (Viewstate, Session, Cache...) and use it as your export datasource.

answered Sep 11 '15 at 14:12

gotmilk13531

234
1
6

You've got the right idea of what I'm trying to achieve. I need the gridview so that non-technical users can use this setup. Ideally I want to try and get the filtered view as a new datasource in effect and export that rather than exporting everything. – SavageDragon87 Sep 11 '15 at 15:30

Exporting only filtered data from gridview asp.net

1 Answers1