Adding paging to my ASP.NET GridView

Question

I have to display and extract 100k+ record table.

I was using GridView but its not showing data as memoryException occur.

So I want to add paging system to my GridView. I tried various tutorials but all covers when page loads with gridview. But in my case GridView loads when requested with a button press.

How to bind my code for paging, so every page show 10 - 20 records?

here is my code-behind:

protected void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=Pfilename.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";

    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        GridView1.AllowPaging = false;


        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
                List<Control> controls = new List<Control>();

                //Add controls to be removed to Generic List
                foreach (Control control in cell.Controls)
                {
                    controls.Add(control);
                }

                //Loop through the controls to be removed and replace then with Literal
                foreach (Control control in controls)
                {
                    switch (control.GetType().Name)
                    {
                        case "HyperLink":
                            cell.Controls.Add(new Literal { Text = (control as HyperLink).Text });
                            break;
                        case "TextBox":
                            cell.Controls.Add(new Literal { Text = (control as TextBox).Text });
                            break;
                        case "LinkButton":
                            cell.Controls.Add(new Literal { Text = (control as LinkButton).Text });
                            break;
                        case "CheckBox":
                            cell.Controls.Add(new Literal { Text = (control as CheckBox).Text });
                            break;
                        case "RadioButton":
                            cell.Controls.Add(new Literal { Text = (control as RadioButton).Text });
                            break;
                    }
                    cell.Controls.Remove(control);
                }
            }
        }

        GridView1.RenderControl(hw);

        //style to format numbers to string
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

protected void ViewPP_Click(object sender, EventArgs e)
{
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Table"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;

                   using (DataTable dt = new DataTable())
                   {
                        sda.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                   }
              }
         }
    }
}

Updated my code with this is last part:

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind();
}

The problem is, when I export the file to excel. it creates paging in excel file and that don't work like it should. It show paging in the excel sheet and that clicks don't work.

Answer 1

Paging is a very basic task

Here is a tutorial to guide you: Paging and Sorting the GridView's Data

<asp:GridView ID="GridView1" Runat="server" 
     AutoGenerateColumns="False"
     AllowPaging="True" >

The main problem however is that you are loading all the data into a DataTable. This will load all the data in memory. You should be using a SqlDataSource instead. The tutorial above also shows you how to use a SqlDataSource.

Edit:

Set SqlDatasource on button click:

protected void Button_Click(object sender, EventArgs e)
{
  GridView1.DataSource = SqlDataSource1;
  GridView1.DataBind();
}