Click here to Skip to main content
14,602,559 members

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

Rate this:
4.13 (13 votes)
Please Sign up or sign in to vote.
4.13 (13 votes)
17 Feb 2016CPOL
Though GridView provides a way to implement paging however when the record count in very big we need to optimize it at the 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.

Image 1

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.

Image 2

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.

License

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

Share

About the Author

Aqeeel
Software Developer
Pakistan Pakistan
No Biography provided

Comments and Discussions

 
GeneralMy vote of 4 Pin
DataBytzAI5-Mar-16 6:45
professionalDataBytzAI5-Mar-16 6:45 
GeneralMy vote of 3 Pin
Manas_Kumar17-Feb-16 23:14
professionalManas_Kumar17-Feb-16 23:14 
QuestionGood Article Pin
Murali Maddu17-Feb-16 1:07
MemberMurali Maddu17-Feb-16 1:07 
AnswerRe: Good Article Pin
Aqeeel17-Feb-16 19:39
MemberAqeeel17-Feb-16 19:39 
QuestionNice but.. Pin
Nchantim16-Feb-16 5:23
MemberNchantim16-Feb-16 5:23 
AnswerRe: Nice but.. Pin
Aqeeel17-Feb-16 19:37
MemberAqeeel17-Feb-16 19:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 16 Feb 2016

Stats

27.4K views
16 bookmarked