Optimized Data Binding, Paging and Sorting in ListView Control

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)       
  10.   
  11. AS  
  12. BEGIN  
  13.     DECLARE @sqlStatement NVARCHAR(MAX),   
  14.             @upperBound INT,   
  15.             @pageSize AS INT = 9;  
  16.       
  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  
  21.           
  22.     IF @pageSize < 1 SET @pageSize = 1  
  23.         SET @upperBound = @startIndex + @pageSize  
  24.           
  25. -- calculate total rows       
  26.     SELECT @totalRows = Count(*)   
  27.     FROM Employee  
  28.     WHERE JobTitle = CASE @jobTitle WHEN '-1' THEN JobTitle ELSE @jobTitle END   
  29.       
  30. ---- select data  
  31.   
  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 )  
  43.       
  44.     SELECT * FROM T   
  45.     WHERE ROWNUM BETWEEN @startIndex AND @upperBound  
  46.       
  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" %>  
  2.   
  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;  
  6.   
  7. namespace TestApplication  
  8. {  
  9.     public partial class Default : System.Web.UI.Page  
  10.     {  
  11.         int totalCnt = 0;  
  12.   
  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.         }  
  23.   
  24.   
  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.         }  
  36.  
  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  
  50.  
  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.         }  
  67.   
  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  
  80.  
  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;  
  99.   
  100.                     sqlCon.Open();  
  101.                     sqlSelectCmd = new SqlDataAdapter();  
  102.                     sqlSelectCmd.SelectCommand = sqlCmd;  
  103.                     sqlSelectCmd.Fill(dsData);  
  104.   
  105.                     totalRows = Convert.ToInt32(((SqlParameter)sqlCmd.Parameters["@totalRows"]).Value);  
  106.   
  107.                     sqlCon.Close();  
  108.                 }  
  109.             }  
  110.             catch  
  111.             {  
  112.                 throw;  
  113.             }  
  114.   
  115.             lvData.DataSource = dsData;  
  116.             lvData.DataBind();  
  117.   
  118.             if (!IsPostBack)  
  119.             {  
  120.                 CreatePagingControl();  
  121.             }  
  122.         }  
  123.         #endregion  
  124.     }  

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.