Save data records and use it again without querying again into database

Question

I have a two control, which are a Search button and Export button. The user will 'Search' the data and the data will be inserted into DataTable and bound to the GridView or Repeater, and then when user click Export, the DataTable will be passed into the class that will generate an Excel file using the data inside DataTable.

In my current code, the Search button and Export button both will query to fetch data from database. But what I need is only Search button that will query, and store the data into a DataTable or something, and when Export button is clicked it will use the earlier data to generate the Excel file.

Here is my current code: Report.aspx

    <asp:GridView ID="GridView1" runat="server">
        <Columns>
        </Columns>
    </asp:GridView>
</body>

Report.aspx.cs

protected void Page_Load(object sender, EventArgs e)
        {
            /* Page load */
        }

protected void btn_search_Click(object sender, EventArgs e)
        {
            DataTable dt = GetData();
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

protected DataTable GetData()
    {
        DataTable dataTable = new DataTable();
        /* Query operation */
            adapter.Fill(dataTable);
        return dataTable;
    }

protected void btn_export_Click(object sender, EventArgs e)
    {
        var excelClass = new ExcelClass();
        excelClass.ExcelData = GetData();
    }

I attempt to create a DataTable as a property in 'Report.aspx.cs' and fill it with the data when Search is clicked. But when I Export , the DataTable is empty again.

DataTable dataTable = new DataTable();
protected void Page_Load(object sender, EventArgs e)
        {
            /* Page load */
        }

protected void btn_search_Click(object sender, EventArgs e)
        {
            GetData();
            GridView1.DataSource = dataTable ;
            GridView1.DataBind();
        }

protected void GetData()
    {
        /* Query operation */
            adapter.Fill(dataTable);
    }

protected void btn_export_Click(object sender, EventArgs e)
    {
        var excelClass = new ExcelClass();
        excelClass.ExcelData = dataTable;
    }

But, I need to fetch the data just once, when Search is clicked, and use the data again when I Export it, so it wont query to the database twice.

Answer 1

There are three ways you can achieve this as mentioned below.

1. Using Session

You can use session for this but this will affect the performance of the application/page if, for example, the dataTable contains 10,000 records.

Add a dataTable into session:

DataTable dataTable= new DataTable();

dataTable= GetData();

Session.Add("dataTable", dataTable);

Retrive that datatable from session:

DataTable dataTable = Session["dataTable"] as DataTable

or

DataTable dataTable= (DataTable)Session["dataTable"];

2. Export a GridView1.DataSource to a dataTable

DataTable dataTable= Gridview1.DataSource as DataTable;

3. Iterate each Row from GridView to DataTable

//Create a new DataTable.
DataTable dataTable = new DataTable();

//Add columns to DataTable.
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
    dataTable.Columns.Add(cell.Text);
}

//Loop through the GridView and copy rows.
foreach (GridViewRow row in GridView1.Rows)
{
    dataTable.Rows.Add();
    for (int i = 0; i < row.Cells.Count; i++)
    {
        dataTable.Rows[row.RowIndex][i] = row.Cells[i].Text;
    }
}