Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

A Neat Solution to GridView Custom Paging

, 5 Jun 2007 CPOL
This article shows how to easily extend the GridView to support custom paging and remove the restriction of using ObjectDataSource as the data source.
PagingGridView.zip
PagingGridView
customerPaging.png
PagingGridView
bin
PagingGridView.dll
obj
Debug
TempPE
PagingGridView.csproj.user
Properties
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    private const string demoConnString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NorthwindSQL;Data Source=localhost\SQLEXPRESS";
    private const string demoTableName = "Customers";
    private const string demoTableDefaultOrderBy = "CustomerID";

    #region Page and Control event handling
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            PagingGridView1.VirtualItemCount = GetRowCount();
            BindPagingGrid();
        }
    }
    
    protected void PagingGridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        PagingGridView1.PageIndex = e.NewPageIndex;
        BindPagingGrid();
    }

    protected void PagingGridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        BindPagingGrid();
    }

    /// <summary>
    /// Helper to bind the grid to the dynamic data
    /// </summary>
    private void BindPagingGrid()
    {
        PagingGridView1.DataSource = GetDataPage(PagingGridView1.PageIndex, PagingGridView1.PageSize, PagingGridView1.OrderBy);
        PagingGridView1.DataBind();
    }
    #endregion

    #region Dynamic data query
    private int GetRowCount()
    {
        using (SqlConnection conn = new SqlConnection(demoConnString))
        {
            conn.Open();
            SqlCommand comm = new SqlCommand(@"SELECT COUNT(*) FROM " + demoTableName, conn);
            int count = Convert.ToInt32(comm.ExecuteScalar());
            conn.Close();
            return count;
        }
    }

    private DataTable GetDataPage(int pageIndex, int pageSize, string sortExpression)
    {
        using (SqlConnection conn = new SqlConnection(demoConnString))
        {
            // We always need a default sort field for ROW_NUMBER() to work correctly
            if (sortExpression.Trim().Length == 0)
                sortExpression = demoTableDefaultOrderBy;

            conn.Open();
            // This shows an example of how we can retrieve a block of records using ROW_NUMBER()
            // For example, pageIndex=3, pageSize=20 so the record we want to display is row 61-80
            // the resultant SQL will have an inner select that retrieve "TOP 80" rows, the outer 
            // select than filter out all the row <= 60 through "ROW_NUM > 60" expression to return
            // the 20 records (row 61-80 )
            string commandText = string.Format("SELECT * FROM (select TOP {0} ROW_NUMBER() OVER (ORDER BY {1}) as ROW_NUM, *  " +
                                                "FROM {2} ORDER BY ROW_NUM) innerSelect WHERE ROW_NUM > {3}",
                                                ((pageIndex + 1) * pageSize), 
                                                sortExpression, 
                                                demoTableName,
                                                (pageIndex * pageSize));
            SqlDataAdapter adapter = new SqlDataAdapter(commandText, conn);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            conn.Close();
            dt.Columns.Remove("ROW_NUM");
            return dt;
        }

    }
    #endregion
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Fadrian Sudaman
Architect SMS Management and Technology
Australia Australia
Fadrian Sudaman is an experienced IT professional who has worked with .NET technology since the early beta. His background stems from a strong C/C++ development experience in building large commercial applications and great appreciation for best practice and modern approaches for building quality software. Currently, Fadrian works as a senior consultant specialises in .NET technology involved in variety of roles including project management, solution architecture, presales and application development. Fadrian is also completing his PhD part time at Monash University, Australia.
Follow on   Twitter   Google+

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 5 Jun 2007
Article Copyright 2007 by Fadrian Sudaman
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid