Custom Paging for Gridview using SQL Server

 

Custom Paging for Gridview using SQL Server:

Description:

In my previous article I'm explaining Load 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">
       <Columns>
           <asp:BoundField HeaderText="StudentId" DataField="StudentID" />
           <asp:BoundField HeaderText="Student Name" DataField="Student_Name" />
           <asp:BoundField HeaderText="Address" DataField="Addres" />
       </Columns>
    </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:

Reference Link

 

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.