Custom Paging in Asp.Net without using controls like GridView and Repeater

Custom Paging in Asp.Net without using controls like GridView and Repeater

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 article Custom Paging with SQL Server 2012.

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 @[email protected]+';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 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 Products table of Northwind database).

    Once the data is filled in the dataSet variable, I am calling the CreateTable() function and passing the first of the table of the DataSet.

    I am calling the Set_Paging() 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 Records. Once the table is created I set it inside myGrid div.

  • 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>&nbsp;&nbsp;&nbsp;";
                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>&nbsp;&nbsp;&nbsp;";
                }
            }
            else
                ReturnValue = ReturnValue + "<span class='" + DisableClassName + "'>Previous</span>&nbsp;&nbsp;&nbsp;";
            if ((PageNumber - 3) > 1)
                ReturnValue = ReturnValue + "<a href='" + PageUrl.Trim() + "' class='" + ClassName + "'>1</a>&nbsp;.....&nbsp;|&nbsp;";
            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>&nbsp;|&nbsp;";
                    }
                    else
                    {
                        ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span>&nbsp;|&nbsp;";
                    }
                }
            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>&nbsp;|&nbsp;";
                    }
                    else
                    {
                        ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span>&nbsp;|&nbsp;";
                    }
                }
            if ((PageNumber + 3) < TotalPages)
            {
                ReturnValue = ReturnValue + ".....&nbsp;<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 + "&nbsp;&nbsp;&nbsp;<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 + "&nbsp;&nbsp;&nbsp;<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>
    

DEMO 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.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on “WordPress, SEO, jQuery, HTML” and more.