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:

  1. CREATEPROCEDUREGetAll_Employees  
  2. @pageIndexint,  
  3. @pageSizeint,  
  4. @totalRecordsintOUTPUT  
  5. -- EXEC GetAll_Employees 1, 100,@totalRecords OUTPUT;PRINT @totalRecords  
  6. AS  
  7. BEGIN  
  8.   
  9. SELECT@totalRecords=COUNT(EmpID)FROMdbo.Employees  
  10. SelectEmpID,EmpName,Desigantion,Address,Cityfrom  
  11. (  
  12. Select  
  13. Row_number()over(OrderbyEmpIDASC)asRowNo,  
  14. EmpName,Desigantion,Address,City  
  15. FROMdbo.Employees  
  16.   
  17. )ASEmp  
  18. Where  
  19. Emp.RowNobetween((@pageIndex- 1)*@pageSize)+ 1 and(@pageIndex*@pageSize)  
  20. OrderbyEmpIDASC  
  21. END  
Design the Grid View

  1. <asp:GridViewIDasp:GridViewID="grdEmployee"runat="server"AutoGenerateColumns="false">  
  2.     <Columns>  
  3.         <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="EmpID"DataField="EmpID"/>  
  4.         <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="EmpName"DataField="EmpName"/>  
  5.         <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="Address"DataField="Address"/>  
  6.         <asp:BoundFieldHeaderTextasp:BoundFieldHeaderText="City"DataField="City"/>  
  7.     </Columns>  
  8. </asp:GridView>  
  9. <br/>  
  10. <asp:RepeaterIDasp:RepeaterID="rptPager"runat="server">  
  11.     <ItemTemplate>  
  12.         <asp:LinkButtonCssClassasp:LinkButtonCssClass="abc"ID="lnkPage"runat="server"Text='<%#Eval("Text") %>'CommandArgument='<%# Eval("Value") %>'Enabled='<%# Eval("Enabled") %>'OnClick="Page_Changed">  
  13.         </asp:LinkButton>  
  14.     </ItemTemplate>  
  15. </asp:Repeater>  
Binding GridView
  1. privatevoid BindData(int pageIndex) {  
  2.     Int standardPageSize = 100;  
  3.     string constring = ConfigurationManager.ConnectionStrings["Azure_ContentManifest"].ConnectionString;  
  4.     using(SqlConnection con = newSqlConnection(constring)) {  
  5.         using(SqlCommand cmd = newSqlCommand("GetAll_Employees", con)) {  
  6.             cmd.CommandType = CommandType.StoredProcedure;  
  7.             cmd.Parameters.AddWithValue("@pageIndex", pageIndex);  
  8.             cmd.Parameters.AddWithValue("@pageSize", standardPageSize);  
  9.             cmd.Parameters.Add("@totalRecords", SqlDbType.Int, 4);  
  10.             cmd.Parameters["@totalRecords"].Direction = ParameterDirection.Output;  
  11.             con.Open();  
  12.             DataReaderdr = cmd.ExecuteReader();  
  13.             grdEmployee.DataSource = dr;  
  14.             grdEmployee.DataBind();  
  15.             con.Close();  
  16.             int recordCount = Convert.ToInt32(cmd.Parameters["@totalRecords"].Value);  
  17.             PopulatePager(recordCount, standardPageSize, pageIndex);  
  18.         }  
  19.     }  
  20. }  
Populate Pager/Pagination
  1. protectedvoid Page_Changed(object sender, EventArgs e) { // link button click i.e page number  
  2.     int pageIndex = int.Parse((sender asLinkButton).CommandArgument);  
  3.     BindData(pageIndex);  
  4. }  
  5. privatevoid PopulatePager(int recordCount, int pageSize, int currentPage) {  
  6.     double dblPageCount = (double)((decimal) recordCount / (decimal) pageSize);  
  7.     int pageCount = (int) Math.Ceiling(dblPageCount);  
  8.   
  9.     List < ListItem > pages = newList < ListItem > ();  
  10.     if (pageCount > 0) {  
  11.         pages.Add(newListItem("First""1", currentPage > 1));  
  12.         for (int i = 1; i <= pageCount; i++) {  
  13.             if ((i % 10 == 0)) {  
  14.                 pages.Add(newListItem("...", i.ToString(), i != currentPage));  
  15.                 break;  
  16.   
  17.             }  
  18.             pages.Add(newListItem(i.ToString(), i.ToString(), i != currentPage));  
  19.         }  
  20.         pages.Add(newListItem("Last", pageCount.ToString(), currentPage < pageCount));  
  21.     }  
  22.     rptPager.DataSource = pages;  
  23.     rptPager.DataBind();  
  24. }  
Loading first 10 pages
Figure 1: Loading first 10 pages

Loading next 10 records
Figure 2: Loading next 10 records