Custom Paging for Gridview using SQL Server

In my previous article I'm explaining how to load data on demand without paging, but in this article I'm going to explain how to load data for particular page dynamically, for example in some scenarios we want to load huge data, usually what we are doing load all the records and display gridview and perform paging using default grid page options, but when we are looking performance and bandwidth the approach is not good, so that only we are moving forward that load fixed page records based on page size and

Custom Paging for Gridview using SQL Server:

Description:

In my previous article I'm explaining Load Data on demand, but in this article I'm going to explain how to load data for particular page dynamically using SQL server, for example in some scenarios we want to load huge data, usually what we are doing load all the records and display gridview and perform paging using default grid page options, but when we are looking performance and bandwidth the approach is not good, so that only we are moving forward that load fixed page records based on page size and currentpage index.

Stored Procedure:

As I mentioned above we want to load records based on pagesize and page index, so we need to pass those as input parameters while fetching records, and return the pagecount as a output.


CREATE PROCEDURE GetStudentsPageWise
(
	@PageIndex INT ,
	@PageSize INT ,
	@PageCount INT OUTPUT
)
AS
/*
	pagging in procedure side
*/
BEGIN
	SET NOCOUNT ON;
    SELECT	ROW_NUMBER() OVER (  ORDER BY [StudentID] ASC )AS RowNumber
			,[StudentID]
			,[Student_Name]
			,[Addres]
    INTO	#Results
    FROM	StudentInfo
    

    DECLARE @RecordCount INT
    SELECT @RecordCount = COUNT(*) FROM #Results
    
	SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
    
          
    SELECT * FROM #Results
    WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    
    DROP TABLE #Results
END

HTML Markup

Design your page like below;


<div>
            
            <asp:GridView ID="gvStudentInfo" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <Columns>
                    <asp:BoundField HeaderText="StudentId" DataField="StudentID" />
                    <asp:BoundField HeaderText="Student Name" DataField="Student_Name" />
                    <asp:BoundField HeaderText="Address" DataField="Addres" />
                </Columns>
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>
            <br />
            <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>' Enabled='<%# Eval("Enabled") %>' OnClick="Page_Changed"></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater>
        </div>

Binding the Gridview Data:


protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetStudentsPageWise(1);
        }
    }
    protected void GetStudentsPageWise(int PageIndex)
    {
        string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand("GetStudentsPageWise", con);
        
        try
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", PageIndex);
            cmd.Parameters.AddWithValue("@PageSize", 10);
            cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4);
            cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
            con.Open();
            IDataReader idr = cmd.ExecuteReader();
            gvStudentInfo.DataSource = idr;
            gvStudentInfo.DataBind();

            idr.Close();
            int PageCount = Convert.ToInt32(cmd.Parameters["@PageCount"].Value);
            PopulatePager(PageCount, PageIndex);
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {           
            con.Close();
        }
    }

Populate Paging:


protected void PopulatePager(int PageCount, int currentPage)
    {
        try
        {
            List lstPages = new List();
            if (PageCount > 0)
            {
                lstPages.Add(new ListItem("First", "1", currentPage > 1));
                for (int i = 1; i <= PageCount; i++)
                {
                    lstPages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                lstPages.Add(new ListItem("Last", PageCount.ToString(), currentPage < PageCount));
            }
            rptPager.DataSource = lstPages;
            rptPager.DataBind();
        }
        catch(Exception ex)
        {
            throw ex;
        }
       
    }

Now, the first page records alone binded to the gridview control, if you want to bind page wise records then wrote below event, this will help you to bind records for each and every page.


protected void Page_Changed(object sender, EventArgs e)
    {
        int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
        this.GetStudentsPageWise(pageIndex);
    }

The above event we are called in Onclick event of Repeater Item control. Whenever you click the page based on CommandArgument binded the respective value will return the pageindex based on Page Index we will bind the records to GridView control.

Output:

References:

Custom Paging

Conclusion:

Hope this article will help you, those who are looking for the same. If you have any questions then feel free to ask by posting your comments below.

Custom Paging for Gridview using SQL Server