How to implement Paging in ASP.NET at SQL Query Level

Introduction

Implementing Paging ASP.NET at SQL Query Level rather in GridView

Background

Though GirdView is a very powerful tool for displaying data on ASP.NET WebForm. It also help us implement the Paging in case of large number of result set. However in the backend; complete data is fetched then concerned data is extracted and displayed in the GridView on the WebForm. In this case, concerned data is a small portion of the completely extracted data. The extra data is complete wastage of processing power, memory and time. In this article we will see how we can extract only the required data from the database to avoid wastage of extra processing power, memory and time.

The figure below elaborates how complete data is fetched from the database. During rendering the concerned data is extracted and populated in the GridView.

The figure below explains how filtered or concerned data is extracted from the database, resulting in smaller dataset generated by database. And in Web Application, same dataset is populated in GridView without any extraction.

Implementation

Tools

This is implemented using SQL Server 2014 and Visual Studio 2015. Previous versions of SQL Server especially prior to 2012, SQL does not support FETCH, so same can be implemented using the ROW NUMBER.

Lets set to backend first:

1. Create a database by the name TestPagingInASPNET,
2. Create two tables by the name "AdministrativeUnits" and "Cities".
3. Create the Stored Procedures that will fetch the data from the Database. Note that I have created two SPs. First with the name "SelectCitiesWithPaging" and second with the name "SelectCitiesWithPagingOldSQLVersions". As I implemected the solution in SQL Server 2014, so in the first SP we are using the OFFSET FETCH statements. For previous versions like SQL Server 2005 and SQL Server2008 we will use the ROW_NUMBER() function instead of OFFSET FETCH. So create the SP suited to your requirement. Unlike the conventional SP, this SP will have three parameters. Details are as follow:
   • @PageNumber identifing the Page Number which will be returned
   • @RowsPerPage identifing the Number of Rows per Page and
   • @TotalResords (output parameter) identifying the Total Records

-- CREATE DATABASE
CREATE DATABASE TestPagingInASPNET;
GO

-- CREATE FIRST TABLE
CREATE TABLE AdministrativeUnits (
    AdministrativeUnitID INT PRIMARY KEY IDENTITY(1, 1),
    Name VARCHAR(50)
);
GO

-- CREATE SECOND TABLE
CREATE TABLE Cities (
    CityID INT PRIMARY KEY IDENTITY(1, 1),
    AdministrativeUnitID INT,
    Name VARCHAR(50)
);
GO

-- CREATE THE STORED PROCEDURE
CREATE PROCEDURE SelectCitiesWithPaging
    @PageNumber INT,
    @RowsPerPage INT,
    @TotalRows INT OUTPUT
AS
BEGIN

    SET NOCOUNT ON;
    
    SELECT        @TotalRows = COUNT(*)
    FROM        [AdministrativeUnits] [AU]
    INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]

    SELECT        [AU].[Name] [Administrative Unit],
                [C].[Name] [City]
    FROM        [AdministrativeUnits] [AU]
    INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]
    ORDER BY    [AU].[Name], [C].[Name]
    OFFSET        ((@PageNumber - 1) * @RowsPerPage) ROWS FETCH NEXT @RowsPerPage ROWS ONLY

END
GO

-- CREATE THE STORED PROCEDURE
CREATE PROCEDURE SelectCitiesWithPagingOldSQLVersions
    @PageNumber INT,
    @RowsPerPage INT,
    @TotalRows INT OUTPUT
AS
BEGIN

    SET NOCOUNT ON;

    SELECT        @TotalRows = COUNT(*)
    FROM         [AdministrativeUnits] [AU]
    INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]

    SELECT    *
    FROM    (    SELECT        ROW_NUMBER() OVER (ORDER BY [AU].[Name], [C].[Name]) NUMBER,
                            [AU].[Name] [Administrative Unit],
                            [C].[Name] [City]
                 FROM        [AdministrativeUnits] [AU]
                 INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]
            
                 ) tbl
    WHERE    Number BETWEEN ((@PageNumber - 1) * @RowsPerPage + 1) AND (@PageNumber * @RowsPerPage)
END
GO

Now we move to the frontend of the application:

ASPX

• Draw a table with two Table Rows on the Web Form
• In first Table Row drag and drop a GridView. No need to enable paging as this is been taken care in our Stored Procedure
• In the second TableRow place two button for navigating to Previous and Next Pages. Also create the click event of both buttons
• In the third TableRow we will populate the Page Navigation Links
• Below; find the code created in .aspx file

    <table style="width:100%;">
        <tr>
            <td>
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            </td>
        </tr>
        <tr>
            <td style="text-align:center;">
                <asp:Button ID="btnGridViewPrevious" runat="server" OnClick="btnGridViewPrevious_Click" Text="&lt;" />
                <asp:TextBox ID="txtGridViewPageNumber" runat="server"></asp:TextBox>
                <asp:Button ID="btnGridViewGoToPageNumber" runat="server" Text="Go to Page" OnClick="btnGridViewGoToPageNumber_Click" />
                <asp:Button ID="btnGridViewNext" runat="server" OnClick="btnGridViewNext_Click" Text="&gt;" />
            </td>
        </tr>
        <tr>
            <td style="text-align:center;" runat="server" id="tdPage">

            </td>
        </tr>
    </table>

ASPX.cs

In .aspx.cs file we will create two functions.

• GetAndBindData()
  1. The first one will get the required data from the database. Please note that we are providing the Page Number and Rows per Page as parameters
  2. The received data is populated in the grid
  3. This function will be called on Page Load event with PageNumber as 1.
• CreatePager()
  1. The second function will create the navigational links

private void GetAndBindData(Int32 PageNumber, Int32 RowsPerPage)
{
    SqlConnection con = new SqlConnection(ConnectionString);

    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "SelectProjects";
    cmd.Connection = con;

    SqlParameter par1 = new SqlParameter();
    par1.ParameterName = "PageNumber";
    par1.DbType = System.Data.DbType.Int32;
    par1.Direction = System.Data.ParameterDirection.Input;
    par1.Value = PageNumber;
    cmd.Parameters.Add(par1);

    SqlParameter par2 = new SqlParameter();
    par2.ParameterName = "RowsPerPage";
    par2.DbType = System.Data.DbType.Int32;
    par2.Direction = System.Data.ParameterDirection.Input;
    par2.Value = RowsPerPage;
    cmd.Parameters.Add(par2);

    SqlParameter par3 = new SqlParameter();
    par3.ParameterName = "TotalRows";
    par3.DbType = System.Data.DbType.Int32;
    par3.Direction = System.Data.ParameterDirection.Output;
    cmd.Parameters.Add(par3);

    SqlDataAdapter adp = new SqlDataAdapter();
    adp.SelectCommand = cmd;

    DataSet ds = new DataSet();

    con.Open();
    adp.Fill(ds);
    Session["TotalRows"] = par3.Value.ToString();
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind();
}

private void CreatePager(Int32 TotalRecords, Int32 PageNumber, Int32 RowsPerPage)
{
    Int32 intIndex;
    Int32 intPageNumber;

    tdPage.InnerHtml = "";
    intPageNumber = 1;

    for (intIndex = 1; intIndex <= TotalRecords; intIndex+=10)
    {
        tdPage.InnerHtml += " <a href=''>" + intPageNumber.ToString() + "</a> ";
        intPageNumber++;
    }

    if (TotalRecords > intIndex) {
        tdPage.InnerHtml += " <a href=''>" + intIndex.ToString() + "</a> ";
    }
}

protected void btnGridViewNext_Click(object sender, EventArgs e)
{
    Int32 NewPageNumber = Convert.ToInt32(Session["PageNumber"]);
    NewPageNumber++;
    Session["PageNumber"] = NewPageNumber;
    txtGridViewPageNumber.Text = Session["PageNumber"].ToString();
    GetAndBindData(Convert.ToInt32(Session["PageNumber"]), 10);
    btnGridViewPrevious.Enabled = true;
}

protected void btnGridViewGoToPageNumber_Click(object sender, EventArgs e)
{
    Int32 NewPageNumber = Convert.ToInt32(txtGridViewPageNumber.Text);
    Session["PageNumber"] = NewPageNumber;
    txtGridViewPageNumber.Text = Session["PageNumber"].ToString();
    GetAndBindData(Convert.ToInt32(Session["PageNumber"]), 10);
    btnGridViewPrevious.Enabled = true;
}

Points of Interest

This technique will help the developers to bring only concerned data instead of complete data when user changes the page index. Not only concerned data will be selected from database but the processes required to filter the data to be displayed in GridView are also avoided. Hence resulting in better and optimized performance.