Enabling Custom Paging on DataGrid Control

Abstract

When custom pagination is enabled, control does not implement any logic to page over a bound data source. Control expects the programmer to set the data source property with only records to be displayed for a given page.

The advantage that we get is only the records that fit into the page resides in memory.

So, first of all define the Datagrid on the aspx page.

<asp:DataGrid ID="grid"

Runat="server" AutoGenerateColumns="True"

AllowPaging="True"

AllowCustomPaging="True"

                                OnPageIndexChanged="grid_OnPageIndexChanged"

PageSize="5"

>

                <PagerStyle Mode="NumericPages"></PagerStyle>

</asp:DataGrid>

Now let’s take a look at the code behind of the above aspx page

// specify the connection string at the class level.

protected string connectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=PARAGA1";

private void Page_Load(object sender, System.EventArgs e)

{

      if (!Page.IsPostBack)

            SetVirtualItemCount();

      BindGrid();

}

/*

This event is fired when user clicks on a page button.

*/

public void grid_OnPageIndexChanged(object sender, DataGridPageChangedEventArgs e)

{

      grid.CurrentPageIndex = e.NewPageIndex;

      BindGrid();

}

public void BindGrid()

{

      // Bind the grid with next set of page.

      int currentPageIndex = grid.CurrentPageIndex;

      int pageSize = grid.PageSize;

      int minPID = pageSize * currentPageIndex + 1;

      int maxPID = pageSize * (currentPageIndex + 1);

      string qry = "Select * FROM Products WHERE ProductID>=" + minPID + " AND ProductID<=" + maxPID;

      DataTable dataTable = new DataTable();

      SqlDataAdapter sqlDA = new SqlDataAdapter(qry,connectionString);

      sqlDA.Fill(dataTable);

      grid.DataSource = dataTable;

      grid.DataBind();

}

/*

We are writing this method because setting AllowCustomPaging property to true instructs the control not to page through the data source but to just read and display all the data items bound to the DataSource property. Since total no. of items can’t be inferred from any available data we must provide that number using a property called VirtualItemCount at runtime.

*/

public void SetVirtualItemCount()

{

      SqlConnection cnn = new SqlConnection(connectionString);

      cnn.Open();

      string qry = "SELECT COUNT(*) FROM Products";

      SqlCommand cmd = new SqlCommand(qry,cnn);

      // Set the item count

      grid.VirtualItemCount = (int) cmd.ExecuteScalar();

}