A Neat Solution to GridView Custom Paging

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
}