Custom Paging in ASP.NET without using controls like GridView and Repeater

Last Updated: June 6, 2021

In this Tutorial I will show how to fetch data from database using Custom Paging and show it inside an HTML table. Yes I will “not” use GridView and Repeater to show the data, instead my HTML table will do this work.

The HTML table will itself look like a Grid and will also have pagination links. Here I will bind data from Northwind Products Table.

If you want to know how Custom Paging will make your website very light and super-fast then you should read my tutorial – How to implement Custom Paging with SQL Server using row_number() method.

Custom Paging Stored Procedure

The SP that will fetch records from Northwind database Products table by using the concept of custom paging is:

CREATE PROCEDURE [dbo].[sp_GetProductByCustomPaging]   
    @PageNo     INT,
    @pageSize   INT   
AS           
BEGIN
    DECLARE @qry  NVARCHAR(1000);   
    SET @qry='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS ''RowNum'',* FROM Products WHERE 1=1) a WHERE a.RowNum BETWEEN ('+CAST(@pageNo AS NVARCHAR(5))+'-1)*'+CAST(@pageSize AS NVARCHAR(5))+'+1 AND ('+CAST(@pageNo AS NVARCHAR(5))+'*'+CAST(@pageSize AS NVARCHAR(5))+')'
    SET @qry=@qry+';SELECT Count(*) As ''Total'' From Products'
    EXEC Sp_executesql @qry
END

Custom Paging without Controls like GridView Repeater

Moving straight towards the code. This is how to proceed with it:

STEP 1: First of all in the .aspx page create server side div. Inside this div an HTML Table will be created which will show the records from the database.

<div id="myGrid" runat="server"></div>

STEP 2: Create another server side div control in the .aspx page which will show the paging links.

<div id="pagingDiv" runat="server"></div>

STEP 3: In the page load event call the function which will fetch the current page records from the Custom Paging Stored Procedure.

public void Bind()
{
    int pageNo = Request.QueryString["pn"] == null ? 1 : Convert.ToInt32(Request.QueryString["pn"]);
    int pageSize = 5;
 
    SqlConnection conn = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    DataSet dataSet = new DataSet();
 
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
 
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "[dbo].[sp_GetProductByCustomPaging]";
 
    cmd.Parameters.AddWithValue("@PageNo", pageNo);
    cmd.Parameters.AddWithValue("@pageSize", pageSize);
 
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = cmd;
 
    try
    {
        sqlDataAdapter.Fill(dataSet);
        if (dataSet.Tables[0].Rows.Count > 0)
        {
            CreateTable(dataSet.Tables[0]);
            pagingDiv.InnerHtml = Set_Paging(pageNo, pageSize, Convert.ToInt32(dataSet.Tables[1].Rows[0]["Total"]), "activeLink", "index.aspx", "disableLink");
        }
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
    }
}

This function gets the current page number from the query string and sets the page size to ‘5’. It then executes the stored procedure to fetch the current page records (from the Products table of Northwind database).

Would you like to dig deep into the world of database programming with C#? Then you can check this series of tutorials on Entity Framework Core that help you to learn database programming within a few hours time.

Once the data is filled in the dataSetCreateTable() function and passing the first of the table of the DataSet.

Next, I am calling the Set_Paging() function whose work is to create the paging links. I pass to it – page number, page size, total no of records and few more thing. I will explain this function in details later on.

STEP 4: Add the CreateTable() function.

public void CreateTable(DataTable dataTable)
{
    StringBuilder sb = new StringBuilder();
    sb.Append("<table><tbody><tr>");
 
    foreach (DataColumn column in dataTable.Columns)
        sb.Append("<th>" + column.ColumnName + "</th>");
 
    sb.Append("</tr>");
 
    foreach (DataRow row in dataTable.Rows)
    {
        sb.Append("<tr>");
        foreach (DataColumn column in dataTable.Columns)
        {
            sb.Append("<th>" + row[column] + "</th>");
        }
        sb.Append("</tr>");
    }
    sb.Append("</tbody></table>");
    myGrid.InnerHtml = sb.ToString();
}

In this function I create an HTML Table by looping through all the DataTable’s Records. Once the table is created I set it inside the div called myGrid.

STEP 5: Add the Set_Paging() function which creates the paging links.

public string Set_Paging(Int32 PageNumber, int PageSize, Int64 TotalRecords, string ClassName, string PageUrl, string DisableClassName)
{
    string ReturnValue = "";
    try
    {
        Int64 TotalPages = Convert.ToInt64(Math.Ceiling((double)TotalRecords / PageSize));
        if (PageNumber > 1)
        {
            if (PageNumber == 2)
                ReturnValue = ReturnValue + "<a href='" + PageUrl.Trim() + "?pn=" + Convert.ToString(PageNumber - 1) + "' class='" + ClassName + "'>Previous</a>   ";
            else
            {
                ReturnValue = ReturnValue + "<a href='" + PageUrl.Trim();
                if (PageUrl.Contains("?"))
                    ReturnValue = ReturnValue + "&";
                else
                    ReturnValue = ReturnValue + "?";
                ReturnValue = ReturnValue + "pn=" + Convert.ToString(PageNumber - 1) + "' class='" + ClassName + "'>Previous</a>   ";
            }
        }
        else
            ReturnValue = ReturnValue + "<span class='" + DisableClassName + "'>Previous</span>   ";
        if ((PageNumber - 3) > 1)
            ReturnValue = ReturnValue + "<a href='" + PageUrl.Trim() + "' class='" + ClassName + "'>1</a> ..... | ";
        for (int i = PageNumber - 3; i <= PageNumber; i++)
            if (i >= 1)
            {
                if (PageNumber != i)
                {
                    ReturnValue = ReturnValue + "<a href='" + PageUrl.Trim();
                    if (PageUrl.Contains("?"))
                        ReturnValue = ReturnValue + "&";
                    else
                        ReturnValue = ReturnValue + "?";
                    ReturnValue = ReturnValue + "pn=" + i.ToString() + "' class='" + ClassName + "'>" + i.ToString() + "</a> | ";
                }
                else
                {
                    ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span> | ";
                }
            }
        for (int i = PageNumber + 1; i <= PageNumber + 3; i++)
            if (i <= TotalPages)
            {
                if (PageNumber != i)
                {
                    ReturnValue = ReturnValue + "<a href='" + PageUrl.Trim();
                    if (PageUrl.Contains("?"))
                        ReturnValue = ReturnValue + "&";
                    else
                        ReturnValue = ReturnValue + "?";
                    ReturnValue = ReturnValue + "pn=" + i.ToString() + "' class='" + ClassName + "'>" + i.ToString() + "</a> | ";
                }
                else
                {
                    ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span> | ";
                }
            }
        if ((PageNumber + 3) < TotalPages)
        {
            ReturnValue = ReturnValue + "..... <a href='" + PageUrl.Trim();
            if (PageUrl.Contains("?"))
                ReturnValue = ReturnValue + "&";
            else
                ReturnValue = ReturnValue + "?";
            ReturnValue = ReturnValue + "pn=" + TotalPages.ToString() + "' class='" + ClassName + "'>" + TotalPages.ToString() + "</a>";
        }
        if (PageNumber < TotalPages)
        {
            ReturnValue = ReturnValue + "   <a href='" + PageUrl.Trim();
            if (PageUrl.Contains("?"))
                ReturnValue = ReturnValue + "&";
            else
                ReturnValue = ReturnValue + "?";
            ReturnValue = ReturnValue + "pn=" + Convert.ToString(PageNumber + 1) + "' class='" + ClassName + "'>Next</a>";
        }
        else
            ReturnValue = ReturnValue + "   <span class='" + DisableClassName + "'>Next</span>";
    }
    catch (Exception ex)
    {
    }
    return (ReturnValue);
}

The Set_Paging() function takes the following parameters.

Name	Purpose
PageNumber	The current page number.
PageSize	The number of records shown per page.
TotalRecords	The Total Number of records in the table. I returned this value through the Custom Paging Stored Procedure.
ClassName	The CSS Class to set on the paging links.
PageUrl	The Page name where the HTML table is shown. In my case it is the index.asp.
DisableClassName	The CSS Class to set on the disabled paging links.

STEP 6: Add the CSS code to make the table and paging links look good.

<style>
#myGrid {
    margin: 25px auto 0 auto;
}
 
    #myGrid > table > tbody > tr:nth-child(1) {
        background-color: #0f40e0;
    }
 
    #myGrid > table > tbody > tr:not(:nth-child(1)) {
         background-color: #ff6a00;
    }
 
        #myGrid > table > tbody > tr:not(:nth-child(1)) > th {
            font-weight: 100;
        }
 
    #myGrid > table > tbody > tr.pagingDiv {
        background-color: #f2f2f2;
    }
 
       #myGrid > table > tbody > tr.pagingDiv table {
            padding-left: 10px;
            width: 35%;
       }
 
             #myGrid > table > tbody > tr.pagingDiv table td {
                        display: inline;
                    }
 
        #pagingDiv a, #pagingDiv span {
            display: inline-block;
            padding: 0px 9px;
            margin-right: 4px;
            border-radius: 3px;
            border: solid 1px #c0c0c0;
            background: #e9e9e9;
            box-shadow: inset 0px 1px 0px rgba(255,255,255, .8), 0px 1px 3px rgba(0,0,0, .1);
            font-size: .875em;
            font-weight: bold;
            text-decoration: none;
            color: #717171;
            text-shadow: 0px 1px 0px rgba(255,255,255, 1);
        }
 
            #pagingDiv a:hover {
                background: #fefefe;
                background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#FEFEFE), to(#f0f0f0));
                background: -moz-linear-gradient(0% 0% 270deg,#FEFEFE, #f0f0f0);
            }
 
            #pagingDiv a.active {
                border: none;
                background: #616161;
                box-shadow: inset 0px 0px 8px rgba(0,0,0, .5), 0px 1px 0px rgba(255,255,255, .8);
                color: #f0f0f0;
                text-shadow: 0px 0px 3px rgba(0,0,0, .5);
            }
 
        #pagingDiv span {
            color: #f0f0f0;
            background: #616161;
        }
    </style>

Download codes:

DOWNLOAD

In this way you can create simple to complex solutions where Custom Paging is involved. You can easily show your data using grid format through HTML Table control.