Paging and sorting are the most commonly used features of a ListView Control. But this features becomes a time killer when we have large data in a select query (the rows count is greater than thousands/Lacs). The data binding time can be reduced if we fetch a portion of data that is required to display on the current page instead of fetching the complete data set.

Overview

First, we will optimize the select query used for the binding. Instead of writing a conventional select query we will write a Stored Procedure that will return a single page of records. The Stored Procedure will have StartIndex, SortBy Expression, Filter Expression and TotalRows will be the Output Parameter.

Finally, In the presentation layer we will have a ListView as the Presentation Control and Custom Paging. Also a few hidden fields to maintain the current Sort Expression, StartIndex and TotalPages.

Database details

I have dummy data as an employee table.




Stored Procedure

  1. -- =============================================
  2. -- USP_GetGVData 0, 0 ,'gender' ,'-1'
  3. -- USP_GetGVData 0, 0 ,'MaritalStatus','-1'
  4. -- =============================================
  5. CREATE PROCEDURE [dbo].[USP_GetGVData]
  6. @startIndex INT ,
  7. @totalRows INT OUTPUT ,
  8. @sortBy VARCHAR(50) ,
  9. @jobTitle VARCHAR(50)
  11. AS
  12. BEGIN
  13. DECLARE @sqlStatement NVARCHAR(MAX),
  14. @upperBound INT,
  15. @pageSize AS INT = 9;
  17. -- page size is declared as 10 records/ page
  18. -- calculate row number to be fetched = startindex + pagesize
  19. IF @startIndex < 1
  20. SET @startIndex = 1
  22. IF @pageSize < 1 SET @pageSize = 1
  23. SET @upperBound = @startIndex + @pageSize
  25. -- calculate total rows
  26. SELECT @totalRows = Count(*)
  27. FROM Employee
  28. WHERE JobTitle = CASE @jobTitle WHEN '-1' THEN JobTitle ELSE @jobTitle END
  30. ---- select data
  32. ;WITH T AS (
  33. SELECT ROW_NUMBER () OVER ( ORDER BY
  34. CASE @sortBy WHEN 'EmployeeNumber' THEN [EmployeeNumber]
  35. WHEN 'JobTitle' THEN [JobTitle]
  36. WHEN 'MaritalStatus' THEN [MaritalStatus]
  37. WHEN 'Gender' THEN [Gender]
  38. ELSE [EmployeeNumber] END
  39. ) AS ROWNUM
  40. , *
  41. FROM Employee
  42. WHERE JobTitle = CASE @jobTitle WHEN '-1' THEN JobTitle ELSE @jobTitle END )
  44. SELECT * FROM T
  45. WHERE ROWNUM BETWEEN @startIndex AND @upperBound
  47. END

The preceding Stored Procedure will always return <= 10 records with RowNumber manipulated depending on sortBy and Filter expression. Also the OutPut parameter @totalRows returns TotalRows for calculating the pages requred to display the data for the selected sortBy and Filter criteria.



Presentaion Layer

When to use Gridview, ListView and Repetear ??
For data presentation a GridView, ListView or a Repeater Control can be used. But among them Repeater is the fastest and most optimized since it is made up of HTML tags as well as it has lesser viewstate, due to which page has less payload for a postback. But it cannot have the functionality to handle events such as edit, delete and so on and also requires separate coding for paging.

A GridView is the slowest but it has built-in support for sorting, paging, deleting, editing and so on that can be added using less code. Many times a GridView has a huge ViewState that increases the payload for a postback. Hence the page becomes a slow performer.

A ListView is fast and has a few features that a Repeater and GridView has making it an average performer. It has less ViewState, less than a GridView and is faster than a GridView but slower than a Repeater. It doesn't however have built-in support for paging, inserting, deleting and updating the data.

aspx Page Implementation

.aspx script
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TestApplication.Default" %>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  4. <html xmlns="http://www.w3.org/1999/xhtml">
  5. <head runat="server">
  6. <title></title>
  7. </head>
  8. <body>
  9. <form id="form1" runat="server">
  10. <div>
  11. <table width="100%">
  12. <tr>
  13. <td colspan="5" align="center">
  14. <asp:Label ID="lblError" runat="server"></asp:Label>
  15. <asp:HiddenField ID="TotalRows" runat="server" Value="0" />
  16. <asp:HiddenField ID="startIndex" runat="server" Value="0" />
  17. </td>
  18. </tr>
  19. <tr>
  20. <td align="right">
  21. Job Title :
  22. </td>
  23. <td align="left">
  24. <asp:DropDownList ID="ddlJobTitle" runat="server">
  25. <asp:ListItem Text="Select" Value="-1" Selected="True"></asp:ListItem>
  26. <asp:ListItem Text="Chief Executive Officer" Value="Chief Executive Officer"></asp:ListItem>
  27. <asp:ListItem Text="Design Engineer" Value="Design Engineer"></asp:ListItem>
  28. <asp:ListItem Text="Senior Tool Designer" Value="Senior Tool Designer"></asp:ListItem>
  29. <asp:ListItem Text="Marketing Manager" Value="Marketing Manager"></asp:ListItem>
  30. <asp:ListItem Text="Marketing Specialist" Value="Marketing Specialist"></asp:ListItem>
  31. </asp:DropDownList>
  32. </td>
  33. <td>
  34. <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
  35. </td>
  36. </tr>
  37. <tr>
  38. <td colspan="5" align="center">
  39. <br />
  40. <br />
  41. <asp:ListView ID="lvData" runat="server" OnSorting="lvData_Sorting">
  42. <LayoutTemplate>
  43. <table border="0" cellpadding="1" width="100%">
  44. <tr style="background-color: #E5E5FE">
  45. <th>
  46. SrNo
  47. </th>
  48. <th>
  49. LoginID
  50. </th>
  51. <th>
  52. <asp:LinkButton ID="EmpNumber" runat="server" CommandName="Sort" CommandArgument="EmpNumber">EmpNumber</asp:LinkButton>
  53. </th>
  54. <th>
  55. <asp:LinkButton ID="JobTitle" runat="server" CommandName="Sort" CommandArgument="JobTitle">Job Title</asp:LinkButton>
  56. </th>
  57. <th>
  58. Birth Date
  59. </th>
  60. <th>
  61. <asp:LinkButton ID="MaritalStatus" runat="server" CommandName="Sort" CommandArgument="MaritalStatus">Marital Status</asp:LinkButton>
  62. </th>
  63. <th>
  64. <asp:LinkButton ID="Gender" runat="server" CommandName="Sort" CommandArgument="Gender">Gender</asp:LinkButton>
  65. </th>
  66. <th>
  67. Edit
  68. </th>
  69. </tr>
  70. <tr id="itemPlaceholder" runat="server">
  71. </tr>
  72. </table>
  73. </LayoutTemplate>
  74. <ItemTemplate>
  75. <tr>
  76. <td>
  77. <%# Eval("ROWNUM")%>
  78. </td>
  79. <td>
  80. <%# Eval("LoginID")%>
  81. </td>
  82. <td>
  83. <%# Eval("EmployeeNumber")%>
  84. </td>
  85. <td>
  86. <%# Eval("JobTitle")%>
  87. </td>
  88. <td>
  89. <%# Eval("BirthDate","{0:d}")%>
  90. </td>
  91. <td>
  92. <%# Eval("MaritalStatus")%>
  93. </td>
  94. <td>
  95. <%# Eval("Gender")%>
  96. </td>
  97. <th>
  98. Edit
  99. </th>
  100. </tr>
  101. </ItemTemplate>
  102. <AlternatingItemTemplate>
  103. <tr style="background-color: #cecece">
  104. <td>
  105. <%# Eval("ROWNUM")%>
  106. </td>
  107. <td>
  108. <%# Eval("LoginID")%>
  109. </td>
  110. <td>
  111. <%# Eval("EmployeeNumber")%>
  112. </td>
  113. <td>
  114. <%# Eval("JobTitle")%>
  115. </td>
  116. <td>
  117. <%# Eval("BirthDate","{0:d}")%>
  118. </td>
  119. <td>
  120. <%# Eval("MaritalStatus")%>
  121. </td>
  122. <td>
  123. <%# Eval("Gender")%>
  124. </td>
  125. <th>
  126. Edit
  127. </th>
  128. </tr>
  129. </AlternatingItemTemplate>
  130. </asp:ListView>
  131. </td>
  132. </tr>
  133. <tr>
  134. <td colspan="5">
  135. <table>
  136. <tr>
  137. <td>
  138. <asp:PlaceHolder ID="plcPaging" runat="server" />
  139. <br />
  140. <asp:Label runat="server" ID="lblPageName" />
  141. </td>
  142. </tr>
  143. <tr>
  144. <td>
  145. <asp:Label runat="server" ID="lblPage" ForeColor="Green" />
  146. </td>
  147. </tr>
  148. </table>
  149. </td>
  150. </tr>
  151. </table>
  152. </div>
  153. </form>
  154. </body>
  155. </html>

The code behind for the .aspx.cs is as below:

  1. using System;
  2. using System.Web.UI.WebControls;
  3. using System.Data;
  4. using System.Data.SqlClient;
  5. using System.Configuration;
  7. namespace TestApplication
  8. {
  9. public partial class Default : System.Web.UI.Page
  10. {
  11. int totalCnt = 0;
  13. protected void Page_Load(object sender, EventArgs e)
  14. {
  15. if (!IsPostBack)
  16. { }
  17. else
  18. {
  19. plcPaging.Controls.Clear();
  20. CreatePagingControl();
  21. }
  22. }
  25. protected void btnSearch_Click(object sender, EventArgs e)
  26. {
  27. ViewState["SortExpression"] = string.Empty;
  28. TotalRows.Value = "0";
  29. startIndex.Value = "0";
  30. getLvData(Convert.ToInt32(startIndex.Value), ref totalCnt, Convert.ToString(ViewState["SortExpression"]), ddlJobTitle.SelectedValue.ToString());
  31. TotalRows.Value = totalCnt.ToString();
  32. startIndex.Value = "11";
  33. plcPaging.Controls.Clear();
  34. CreatePagingControl();
  35. }
  37. #region " [ListView Events ] "
  38. protected void lvData_Sorting(object sender, ListViewSortEventArgs e)
  39. {
  40. ViewState["SortExpression"] = e.SortExpression;
  41. TotalRows.Value = "0";
  42. startIndex.Value = "0";
  43. getLvData(Convert.ToInt32(startIndex.Value), ref totalCnt, Convert.ToString(ViewState["SortExpression"]), ddlJobTitle.SelectedValue.ToString());
  44. TotalRows.Value = totalCnt.ToString();
  45. startIndex.Value = "11";
  46. plcPaging.Controls.Clear();
  47. CreatePagingControl();
  48. }
  49. #endregion
  51. #region " [ Paging ] "
  52. private void CreatePagingControl()
  53. {
  54. for (int i = 0; i < (Convert.ToInt32(TotalRows.Value) / 10) + 1; i++)
  55. {
  56. LinkButton lnk = new LinkButton();
  57. lnk.Click += new EventHandler(lbl_Click);
  58. lnk.ID = "lnkPage" + (i + 1).ToString();
  59. lnk.Text = (i + 1).ToString();
  60. plcPaging.Controls.Add(lnk);
  61. Label spacer = new Label();
  62. spacer.Text = " ";
  63. plcPaging.Controls.Add(spacer);
  64. lblPage.Text = "Total Pages : " + ((Convert.ToInt32(TotalRows.Value) / 10) + 1).ToString() + ", Selected Page : 1";
  65. }
  66. }
  68. void lbl_Click(object sender, EventArgs e)
  69. {
  70. LinkButton lnk = sender as LinkButton;
  71. int currentPage = int.Parse(lnk.Text);
  72. int take = currentPage * 10;
  73. int skip = currentPage == 1 ? 0 : take - 10;
  74. startIndex.Value = (((currentPage * 10) - 10) + 1).ToString();
  75. getLvData(Convert.ToInt32(startIndex.Value), ref totalCnt, Convert.ToString(ViewState["SortExpression"]), ddlJobTitle.SelectedValue.ToString());
  76. TotalRows.Value = totalCnt.ToString();
  77. lblPage.Text = "Total Pages : " + ((Convert.ToInt32(TotalRows.Value) / 10) + 1).ToString() + ", Selected Page : " + lnk.Text;
  78. }
  79. #endregion
  81. #region " [ Private Function ] "
  82. private void getLvData(int startIndex, ref int totalRows, string sortBy, string jobTitle)
  83. {
  84. DataSet dsData = new DataSet();
  85. SqlConnection sqlCon = null;
  86. SqlCommand sqlCmd = null;
  87. SqlDataAdapter sqlSelectCmd = null;
  88. try
  89. {
  90. using (sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
  91. {
  92. sqlCmd = new SqlCommand("USP_GetGVData", sqlCon);
  93. sqlCmd.CommandType = CommandType.StoredProcedure;
  94. sqlCmd.Parameters.AddWithValue("@startIndex", startIndex);
  95. sqlCmd.Parameters.AddWithValue("@sortBy", sortBy);
  96. sqlCmd.Parameters.AddWithValue("@jobTitle", jobTitle);
  97. sqlCmd.Parameters.AddWithValue("@totalRows", totalRows);
  98. ((SqlParameter)sqlCmd.Parameters["@totalRows"]).Direction = ParameterDirection.Output;
  100. sqlCon.Open();
  101. sqlSelectCmd = new SqlDataAdapter();
  102. sqlSelectCmd.SelectCommand = sqlCmd;
  103. sqlSelectCmd.Fill(dsData);
  105. totalRows = Convert.ToInt32(((SqlParameter)sqlCmd.Parameters["@totalRows"]).Value);
  107. sqlCon.Close();
  108. }
  109. }
  110. catch
  111. {
  112. throw;
  113. }
  115. lvData.DataSource = dsData;
  116. lvData.DataBind();
  118. if (!IsPostBack)
  119. {
  120. CreatePagingControl();
  121. }
  122. }
  123. #endregion
  124. }
  125. }

Compile and run the page and it will look as in the following:



Paging Event



Search with Filter Expression



Search with Sorting Expression (job title selected)



Download the source code for the database script and other explanations.