Grid View With Lazy Load Binding and Custom Pagination

Why we need to go for Lazy loading and Custom Paging

Whenever you want to display data in Gridview, that data might contain lacks of records. If you show all data at a time, it will take more time to get the data also you may get timed out error in application. When we need to display only some of the records in Gridview and table in database contains lacks of records then we should have to use lazy loading with custom paging instead of default paging.

Stored Procedure with Pagination:

CREATEPROCEDUREGetAll_Employees
@pageIndexint,
@pageSizeint,
@totalRecordsintOUTPUT
-- EXEC GetAll_Employees 1, 100,@totalRecords OUTPUT;PRINT @totalRecords
AS
BEGIN

SELECT@totalRecords=COUNT(EmpID)FROMdbo.Employees
SelectEmpID,EmpName,Desigantion,Address,Cityfrom
(
Select
Row_number()over(OrderbyEmpIDASC)asRowNo,
EmpName,Desigantion,Address,City
FROMdbo.Employees

)ASEmp
Where
Emp.RowNobetween((@pageIndex- 1)*@pageSize)+ 1 and(@pageIndex*@pageSize)
OrderbyEmpIDASC
END

Design the Grid View

<asp:GridViewIDasp:GridViewID="grdEmployee"runat="server"AutoGenerateColumns="false">
    <Columns>
        <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="EmpID"DataField="EmpID"/>
        <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="EmpName"DataField="EmpName"/>
        <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="Address"DataField="Address"/>
        <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="City"DataField="City"/>
    </Columns>
</asp:GridView>
<br/>
<asp:RepeaterIDasp:RepeaterID="rptPager"runat="server">
    <ItemTemplate>
        <asp:LinkButtonCssClassasp:LinkButtonCssClass="abc"ID="lnkPage"runat="server"Text='<%#Eval("Text") %>'CommandArgument='<%# Eval("Value") %>'Enabled='<%# Eval("Enabled") %>'OnClick="Page_Changed">
        </asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

Binding GridView

privatevoid BindData(int pageIndex) {
    Int standardPageSize = 100;
    string constring = ConfigurationManager.ConnectionStrings["Azure_ContentManifest"].ConnectionString;
    using(SqlConnection con = newSqlConnection(constring)) {
        using(SqlCommand cmd = newSqlCommand("GetAll_Employees", con)) {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@pageSize", standardPageSize);
            cmd.Parameters.Add("@totalRecords", SqlDbType.Int, 4);
            cmd.Parameters["@totalRecords"].Direction = ParameterDirection.Output;
            con.Open();
            DataReaderdr = cmd.ExecuteReader();
            grdEmployee.DataSource = dr;
            grdEmployee.DataBind();
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["@totalRecords"].Value);
            PopulatePager(recordCount, standardPageSize, pageIndex);
        }
    }
}

Populate Pager/Pagination

protectedvoid Page_Changed(object sender, EventArgs e) { // link button click i.e page number
    int pageIndex = int.Parse((sender asLinkButton).CommandArgument);
    BindData(pageIndex);
}
privatevoid PopulatePager(int recordCount, int pageSize, int currentPage) {
    double dblPageCount = (double)((decimal) recordCount / (decimal) pageSize);
    int pageCount = (int) Math.Ceiling(dblPageCount);

    List < ListItem > pages = newList < ListItem > ();
    if (pageCount > 0) {
        pages.Add(newListItem("First", "1", currentPage > 1));
        for (int i = 1; i <= pageCount; i++) {
            if ((i % 10 == 0)) {
                pages.Add(newListItem("...", i.ToString(), i != currentPage));
                break;

            }
            pages.Add(newListItem(i.ToString(), i.ToString(), i != currentPage));
        }
        pages.Add(newListItem("Last", pageCount.ToString(), currentPage < pageCount));
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}

Figure 1: Loading first 10 pages


Figure 2: Loading next 10 records