Click here to Skip to main content
Click here to Skip to main content

Effective paging with List View control in ASP.NET

, 9 Nov 2012
Rate this:
Please Sign up or sign in to vote.
To develop scalable applications, effective paging is essential. Devoid of smart and effective paging and sorting to handle thousands of thousand data takes extra time and system resources.

Introduction

Paging is an important thing for thousands of data if we want to develop a scalable application. Sometimes, it may need customized user interface and also with paging feature. ASP.NET ListView is a control that can use to provide user interface or layout with paging supports.

Background

I will develop an ASP.NET web application with List View control to show how to build scalable application using paging and sorting effectively.

Let’s Get Started

Before we begin, you may read my first article of paging Effective Paging with Grid View control in ASP.NET.

Open Microsoft SQL Server Management Studio 2005. Design a table as the following structure or you may have a different one.

PagingWithListView/TableStructure.JPG

Figure 1

Some records from Profile table.

PagingWithListView/ProfileData.JPG

Figure 2

Effective Paging with Listview Control

To make paging and sorting effective, the first step is to create stored procedures in SQL.

  1. Open SQL Server Management Studio
  2. Take a New Query

Create a stored procedure with some technique in your database to retrieve records form Profile table. Like the below SQL:

CREATE PROCEDURE [dbo].[Profile_GET]
    @PageSize int = null,
    @CurrentPage int = null,
    @SortExpression    nvarchar(max) = null
AS

BEGIN
    SET NOCOUNT ON

    DECLARE @SqlString nvarchar(max)
    Declare @UpperBand int
    Declare @LowerBand int        
    
    SET @LowerBand  = (@CurrentPage - 1) * @PageSize
    SET @UpperBand  = (@CurrentPage * @PageSize) + 1

    BEGIN

    WITH tempProfile AS
        (                    
        SELECT 
            [ProfileId],
            [Name],
            [Address],
            [Email],
            [Mobile], 
            [IsActive] = CASE [IsActive] WHEN 1 THEN 'Active' _
                    WHEN 0 THEN 'DeActive' END,
            ROW_NUMBER() OVER (ORDER BY ProfileId ASC ) AS RowNumber
            FROM [dbo].[Profile]        
        )     
    
    SELECT 
        [ProfileId],
        [Name],
        [Address],
        [Email],
        [Mobile], 
        [IsActive]
        FROM 
            tempProfile 
        WHERE 
            RowNumber > @LowerBand AND RowNumber < @UpperBand
            ORDER BY  
                CASE WHEN @SortExpression ='ProfileId' _
                    THEN [ProfileId] END,
                CASE WHEN @SortExpression ='ProfileId DESC' _
                    THEN [ProfileId] END DESC,
                CASE WHEN @SortExpression ='Name' THEN [Name] END,
                CASE WHEN @SortExpression ='Name DESC' _
                    THEN [Name] END DESC,
                CASE WHEN @SortExpression ='Address' _
                    THEN [Address] END,
                CASE WHEN @SortExpression ='Address DESC' _
                    THEN [Address] END DESC,
                CASE WHEN @SortExpression ='Email' THEN [Email] END,
                CASE WHEN @SortExpression ='Email DESC' _
                    THEN [Email] END DESC,
                CASE WHEN @SortExpression ='Mobile' THEN [Mobile] END,
                CASE WHEN @SortExpression ='Mobile DESC' _
                    THEN [Mobile] END DESC,
                CASE WHEN @SortExpression ='Status' _
                    THEN [IsActive] END,
                CASE WHEN @SortExpression ='Status DESC' _
                    THEN [IsActive] END DESC
    END
END

Profile_GET stored procedure takes PageSize, CurrentPage and SortExpression as input parameter. PageSize - is the number of records that will display in ListView as a page at a time. CurrentPage – which page you are in currently in ListView.

SortExpression – sort records in a page by which field. All of these above parameters will be passed by ListView control and DataPage form web page when the application is running.

SET @LowerBand  = (@CurrentPage - 1) * @PageSize
SET @UpperBand  = (@CurrentPage * @PageSize) + 1

The above two equations will calculate upper bound and lower bound for a page. Lower band means the starting row position for a page and upper band means top row position for a page. Say, you have current page 5 and your page size is 5. So the result for the above two equations are:

LowerBand = (5 -1) * 5 = 20 
UpperBand = (5 * 5) + 1 = 26

So records that hold position number 21-25 (because I used greater than and less than in where condition later in this procedure) will return by those equations.

WITH tempProfile AS
(                    
    SELECT 
        [ProfileId],
        [Name],
        [Address],
        [Email],
        [Mobile], 
        [IsActive] = CASE [IsActive] WHEN 1 THEN 'Active' _
            WHEN 0 THEN 'DeActive' END,
        ROW_NUMBER() OVER (ORDER BY  
            CASE @SortExpression WHEN 'ProfileId' _
                THEN [ProfileId] END,
            CASE @SortExpression WHEN 'Name' THEN [Name] END,
            CASE @SortExpression WHEN 'Address' _
                THEN [Address] END,
            CASE @SortExpression WHEN 'Email' THEN [Email] END,
            CASE @SortExpression WHEN 'Mobile' THEN [Mobile] END,
            CASE @SortExpression WHEN 'Status' _
                THEN [IsActive] END
        ) AS RowNumber         
        FROM [dbo].[Profile]        
)

The above SQL statement followed by a table name will produce a temporary table from SELECT SQL statement from its body.

ROW_NUMBER() OVER (ORDER BY  
    CASE @SortExpression WHEN 'ProfileId' THEN [ProfileId] END,
    CASE @SortExpression WHEN 'Name' THEN [Name] END,
    CASE @SortExpression WHEN 'Address' THEN [Address] END,
    CASE @SortExpression WHEN 'Email' THEN [Email] END,
    CASE @SortExpression WHEN 'Mobile' THEN [Mobile] END,
    CASE @SortExpression WHEN 'Status' THEN [IsActive] END
    ) AS RowNumber

The above SQL statement will add an additional column RowNumber in a temporary table tempProfile and assign a sequential number for each record in temporary table after sort in ascending order by sorting expression parameter. This RowNumber column will be used for paging later.

PagingWithListView/RowNumber.JPG

Figure 3

Some records from temporary tempProfile table where records have been sorted by Profile Id. Now filter records as per request that have come by PageSize, CurrentPage and SortExpresssion parameters.

SELECT 
    [ProfileId],
    [Name],
    [Address],
    [Email],
    [Mobile], 
    [IsActive]
    FROM 
        tempProfile 
    WHERE 
        RowNumber > @LowerBand AND RowNumber < @UpperBand
        ORDER BY  
            CASE WHEN @SortExpression ='ProfileId' THEN [ProfileId] END,
            CASE WHEN @SortExpression ='ProfileId DESC' _
                THEN [ProfileId] END DESC,
            CASE WHEN @SortExpression ='Name' THEN [Name] END,
            CASE WHEN @SortExpression ='Name DESC' THEN [Name] END DESC,
            CASE WHEN @SortExpression ='Address' THEN [Address] END,
            CASE WHEN @SortExpression ='Address DESC' _
                THEN [Address] END DESC,
            CASE WHEN @SortExpression ='Email' THEN [Email] END,
            CASE WHEN @SortExpression ='Email DESC' THEN [Email] END DESC,
            CASE WHEN @SortExpression ='Mobile' THEN [Mobile] END,
            CASE WHEN @SortExpression ='Mobile DESC' _
                THEN [Mobile] END DESC,
            CASE WHEN @SortExpression ='Status' THEN [IsActive] END,
            CASE WHEN @SortExpression ='Status DESC' _
                THEN [IsActive] END DESC

Now, you need to create another stored procedure.

CREATE PROCEDURE [dbo].[Profile_Total]
AS
BEGIN

    SET NOCOUNT ON
    SELECT COUNT(*) FROM Profile
END

The above Profile_Total will return total number of records.

Creating ASP.NET Website in Visual Studio 2010

  1. Create a website project and name it to PagingWithListView
  2. Add a web form and named it to PagingWithListView.aspx

Add ListView on the web from. Rename it to lstProfileView. Define layout template.

<LayoutTemplate>
    <table cellpadding="0" cellspacing="0" border="0" width="70%">
      <tr>
         <td>
             <h3 class="title">Effective Paging With List View Control in ASP.NET</h3>
         </td>
      </tr>
      <tr>
        <td> </td>
      </tr>
      <tr>
         <td colspan="6" style="background-color: #9BAAC1; 
        font-family: Arial; font-weight: bold;text-align: removed; 
        padding: 4px 2px">PROFILES
          </td>
      </tr>
       <tr>
           <td colspan="6" style="background-color: #384B69">
               <table id="tlbProfile"  runat="server" 
               cellspacing="0" 
            cellpadding="0" border="0" width="100%">
                  <tr>
                      <th align="removed" 
                      style="width: 10%" class="column_head">
                          <asp:LinkButton ID="lnkProfileId" 
                          runat="server" 
            Text="ProfileId" Style="color: #FFFFFF" 
            CommandName="Sort" CommandArgument="ProfileId" />
                          <asp:Image runat="server" ID="SortImage1" 
            ImageUrl="~/Images/asc.png" Visible="false" 
            Width="15" Height="15" class="sort_image"/>
                       </th>
                       <th align="removed" style="width: 15%;" 
                       class="column_head">
                           <asp:LinkButton ID="lnkName" runat="server" 
            Text="Name" Style="color: #FFFFFF" 
            CommandName="Sort" CommandArgument="Name" />
                           <asp:Image runat="server" ID="SortImage2" 
                ImageUrl="~/Images/asc.png" Visible="false" 
                Width="15" Height="15" class="sort_image"/>
                       </th>
                       <th align="removed" style="width: 20%" 
                       class="column_head">
                           <asp:LinkButton ID="lnkAddress" runat="server" 
                Text="Address" Style="color: #FFFFFF" 
                CommandName="Sort" CommandArgument="Address" />
                           <asp:Image runat="server" ID="SortImage3" 
                ImageUrl="~/Images/asc.png" Visible="false"  
                Width="15" Height="15" class="sort_image"/> 
                       </th>
                       <th align="removed" style="width: 15%" 
                       class="column_head">
                           <asp:LinkButton ID="lnkEmail" runat="server" 
                Text="Email" Style="color: #FFFFFF" 
                CommandName="Sort" CommandArgument="Email" />
                           <asp:Image runat="server" ID="SortImage4" 
                ImageUrl="~/Images/asc.png" Visible="false"  
                Width="15" Height="15" class="sort_image"/>
                       </th>
                       <th align="removed" style="width:12%" 
                       class="column_head">
                           <asp:LinkButton ID="lnkMobile" runat="server" 
                Text="Mobile" Style="color: #FFFFFF" 
                CommandName="Sort" CommandArgument="Mobile" />
                            <asp:Image runat="server" ID="SortImage5" 
                ImageUrl="~/Images/asc.png" Visible="false"  
                Width="15" Height="15" class="sort_image"/>
                       </th>
                       <th align="removed" style="width: 15%; 
                       padding-left: 2px;">
                           <asp:LinkButton ID="lnkStatus" 
                           runat="server" Text="Status" 
                           Style="color: #FFFFFF" CommandName="Sort" 
                           CommandArgument="Status" />
                            <asp:Image runat="server" ID="SortImage6" 
                            ImageUrl="~/Images/asc.png" Visible="false"  
                            Width="15" Height="15" class="sort_image"/>
                       </th>
                    </tr>
                    <tr id="itmPlaceholder"  runat="server">
                    </tr>
                 </table>
               </td>
             </tr>
             <tr style="height: 10px;">
                 <td colspan="6">
                 </td>
             </tr>
             <tr>
                 <td>
                     <asp:DataPager ID="dataPagerNumeric" 
                     runat="server" PageSize="5">
                       <Fields>
                            <asp:NumericPagerField ButtonCount="5" 
                            NumericButtonCssClass="numeric_button" 
                            CurrentPageLabelCssClass="current_page"
                                            NextPreviousButtonCssClass="next_button" />
                       </Fields>
                    </asp:DataPager>
                  <td colspan="4" class="number_of_record">
                      <asp:DataPager ID="dataPageDisplayNumberOfPages" 
                      runat="server" PageSize="5">
                      <Fields>
                           <asp:TemplatePagerField>
                                <PagerTemplate>
                                     <span style="color: Black;">Records:
                                               <%# Container.StartRowIndex >= 0 ? 
                                               (Container.StartRowIndex + 1) : 0 %> -
                                               <%# (Container.StartRowIndex + 
                        Container.PageSize) 
                                               > Container.TotalRowCount ? 
                        Container.TotalRowCount : 
                                               (Container.StartRowIndex + 
                        Container.PageSize)%> of
                                               <%# Container.TotalRowCount %>
                                    </span>
                                </PagerTemplate>
                            </asp:TemplatePagerField>
                        </Fields>
                    </asp:DataPager>
                </td>
            </td>
        </tr>
    </table>
</LayoutTemplate>

I have used HTML Table for providing layout template.

<tr>
   <td>
      <h3 class="title">
          Effective Paging With List View Control in ASP.NET</h3>
    </td>
</tr>

The above markup is for title.

  <tr>
     <td colspan="6" style="background-color: #9BAAC1; font-family: Arial; 
        font-weight: bold; text-align: removed; padding: 4px 2px">
        PROFILES
     </td>
</tr>

The above markup is for list title:

<tr>
  <td colspan="6" style="background-color: #384B69">
      <table id="tlbProfile"  runat="server" 
      cellspacing="0" cellpadding="0" border="0" width="100%">
           <tr>
             <th align="removed" style="width: 10%" 
             class="column_head">
                 <asp:LinkButton ID="lnkProfileId" 
                 runat="server" Text="ProfileId" 
                 Style="color: #FFFFFF" CommandName="Sort" 
                 CommandArgument="ProfileId" />
                 <asp:Image runat="server" ID="SortImage1" 
                 ImageUrl="~/Images/asc.png" Visible="false" 
                 Width="15" Height="15" class="sort_image"/>
             </th>
             <th align="removed" style="width: 15%;" 
             class="column_head">
                 <asp:LinkButton ID="lnkName" runat="server" 
                 Text="Name" Style="color: #FFFFFF" 
                 CommandName="Sort" CommandArgument="Name" />
                 <asp:Image runat="server" ID="SortImage2" 
                 ImageUrl="~/Images/asc.png" Visible="false"  
                 Width="15" Height="15" class="sort_image"/>
             </th>
             <th align="removed" style="width: 20%" 
             class="column_head">
                 <asp:LinkButton ID="lnkAddress" runat="server" 
                 Text="Address" Style="color: #FFFFFF" 
                 CommandName="Sort" CommandArgument="Address" />
                 <asp:Image runat="server" ID="SortImage3" 
                 ImageUrl="~/Images/asc.png" Visible="false"  
                 Width="15" Height="15" class="sort_image"/>
             </th>
             <th align="removed" style="width: 15%" class="column_head">
                 <asp:LinkButton ID="lnkEmail" runat="server" 
                 Text="Email" Style="color: #FFFFFF" 
                 CommandName="Sort" CommandArgument="Email" />
                 <asp:Image runat="server" ID="SortImage4" 
                 ImageUrl="~/Images/asc.png" Visible="false"  
                 Width="15" Height="15" class="sort_image"/>
             </th>
                 <th align="removed" style="width:12%" 
                 class="column_head">
                     <asp:LinkButton ID="lnkMobile" runat="server" 
                     Text="Mobile" Style="color: #FFFFFF" 
                     CommandName="Sort" CommandArgument="Mobile" />
                    <asp:Image runat="server" ID="SortImage5" 
                    ImageUrl="~/Images/asc.png" Visible="false"  
                    Width="15" Height="15" class="sort_image"/>
                 </th>
                 <th align="removed" style="width: 15%; padding-left: 2px;">
                     <asp:LinkButton ID="lnkStatus" runat="server" 
                     Text="Status" Style="color: #FFFFFF" 
                     CommandName="Sort" CommandArgument="Status" />
                     <asp:Image runat="server" ID="SortImage6" 
                     ImageUrl="~/Images/asc.png" Visible="false"  
                     Width="15" Height="15" class="sort_image"/>
                 </th>
              </tr>
              <tr id="itmPlaceholder"  runat="server">
              </tr>
           </table>
        </td>
      </tr>

The above markup is for column head. You must define place holder - it is needed by List View.

<tr id="itmPlaceholder"  runat="server">
</tr>

Add two data pagers in the ASPX page.

<tr>
    <td>
       <asp:DataPager ID="dataPagerNumeric" 
           runat="server" PageSize="5">
           <Fields>
                <asp:NumericPagerField ButtonCount="5" 
            NumericButtonCssClass="numeric_button" 
            CurrentPageLabelCssClass="current_page"
                           NextPreviousButtonCssClass="next_button" />
           </Fields>
        </asp:DataPager>
    <td colspan="4" class="number_of_record">
       <asp:DataPager ID="dataPageDisplayNumberOfPages" 
       runat="server" PageSize="5">
           <Fields>
               <asp:TemplatePagerField>
                   <PagerTemplate>
                         <span style="color: Black;">Records:
                              <%# Container.StartRowIndex >= 0 ? 
                (Container.StartRowIndex + 1) : 0 %> -
                              <%# (Container.StartRowIndex + Container.PageSize) > 
                Container.TotalRowCount ? Container.TotalRowCount : 
                (Container.StartRowIndex + Container.PageSize)%> of
                              <%# Container.TotalRowCount %>
                         </span>
                   </PagerTemplate>
               </asp:TemplatePagerField>
           </Fields>
        </asp:DataPager>
     </td>
   </td>
</tr>
<asp:DataPager ID="dataPagerNumeric" 
runat="server" PageSize="5">
   <Fields>
       <asp:NumericPagerField ButtonCount="5" 
       NumericButtonCssClass="numeric_button" 
            CurrentPageLabelCssClass="current_page"
                NextPreviousButtonCssClass="next_button" />
   </Fields>
</asp:DataPager>

The above markup is for numeric buttons.

PagingWithListView/numericbuttons.JPG
Figure 4

I have added some CSS classes to design it.

.numeric_button
{
    background-color:#384B69;
    color:#FFFFFF;    
    font-family:Arial;
    font-weight:bold;    
    padding:2px;  
    border:none;  
}
.current_page
{
    background-color:#09151F;
    color:#FFFFFF;    
    font-family:Arial;
    font-weight:bold;    
    padding:2px;    
}
.next_button
{
    background-color:#1F3548;
    color:#FFFFFF;    
    font-family:Arial;
    font-weight:bold;    
    padding:2px;    
}
<td colspan="4" class="number_of_record">
    <asp:DataPager ID="dataPageDisplayNumberOfPages" runat="server" PageSize="5">
  <Fields>
    <asp:TemplatePagerField>
        <PagerTemplate>
           <span style="color: Black;">Records:
               <%# Container.StartRowIndex >= 0 ? (Container.StartRowIndex + 1) : 0 %> -
               <%# (Container.StartRowIndex + Container.PageSize) > 
        Container.TotalRowCount ? Container.TotalRowCount : 
        (Container.StartRowIndex + Container.PageSize)%> of 
              <%# Container.TotalRowCount %>
          </span>
       </PagerTemplate>
    </asp:TemplatePagerField>
  </Fields>
 </asp:DataPager>
</td>

The above markup is for number of records.

PagingWithListView/numberofrecords.JPG

Figure 5

Now add EmptyDataTemplate. It will show if data is not available.

<EmptyDataTemplate>
 <table class="empty_data"  runat="server">
   <tr>
     <td>No data found.</td>
   </tr>
 </table>
</EmptyDataTemplate>

Add ItemTemplate:

<ItemTemplate>
    <tr>
        <td align="removed" style="width: 7%;" 
        class='<%# Container.DataItemIndex % 2 == 0 ? 
        "alternate_row" : "row" %>'>
            <asp:Label ID="lblProfileId" 
            runat="server" Width="65" />
        </td>
        <td align="removed" style="width: 10%;" 
        class='<%# Container.DataItemIndex % 2 == 0 ? 
        "alternate_row" : "row" %>'>
            <asp:Label ID="lblName" runat="server" />
        </td>
        <td align="removed" style="
        width: 25%;" class='<%# Container.DataItemIndex % 2 == 0 ? 
        "alternate_row" : "row" %>'>
            <asp:Label ID="lblAddress" runat="server" />
        </td>
        <td align="removed" style="width: 25%;" 
        class='<%# Container.DataItemIndex % 2 == 0 ? "
        alternate_row" : "row" %>'>
            <asp:Label ID="lblEmail" runat="server" />
        </td>
        <td align="removed" style="width: 15%;" 
        class='<%# Container.DataItemIndex % 2 == 0 ? 
        "alternate_row" : "row" %>'>
            <asp:Label ID="lblMobile" runat="server" />
        </td>
        <td align="removed" style="width: 8%;
        " class='<%# Container.DataItemIndex % 2 == 0 ? 
        "alternate_row" : "row" %>'>
            <asp:Label ID="lblStatus" runat="server" />
        </td>
    </tr>
</ItemTemplate>

The above markup actually shows records.

class='<%# Container.DataItemIndex % 2 == 0 ? "alternate_row" : "row" %>'

The above code is for every alternate rows color by CSS class. If row position is divided by 2, then it is alternate row and assign alternate_row CSS class, otherwise it is row and assign row.

.alternate_row
{
    background-color:#D9DEE7;
    border-right:1px solid #FFFFFF;
    padding-left:2px;
}
.row
{
    background-color:#FFFFFF;
    border-right:1px solid #FFFFFF;
    padding-left:2px;
}

Rows and alternate rows.

PagingWithListView/alternetrow.JPG

Figure 6

All together:

<asp:ListView ID="lstProfileView" runat="server" 
         DataSourceID="odsPforile" DataKeyNames="ProfileId"
         ItemPlaceholderID="itmPlaceholder" 
         OnItemDataBound="Profile_ItemDataBound" 
         OnSorting="Profile_Sorting">
    <LayoutTemplate>
        <table cellpadding="0" cellspacing="0" 
        border="0" width="70%">
            <tr>
                <td>
                    <h3 class="title">
                        Effective Paging With List View Control in ASP.NET</h3>
                </td>
            </tr>
            <tr>
                <td>
                     
                </td>
            </tr>
            <tr>
                <td colspan="6" style="
                background-color: #9BAAC1; font-family: Arial; font-weight: bold;
                    text-align: left; padding: 4px 2px">
                    PROFILES
                </td>
            </tr>
            <tr>
                <td colspan="6" style="background-color: #384B69">
                    <table id="tlbProfile"  
                    runat="server" cellspacing="0" 
                    cellpadding="0" border="0"
                        width="100%">
                        <tr>
                            <th align="removed" 
                            style="width: 10%" class="column_head">
                                <asp:LinkButton ID="lnkProfileId" 
                                runat="server" Text="ProfileId" 
                                Style="color: #FFFFFF"
                                    CommandName="Sort" 
                                    CommandArgument="ProfileId" />
                                <asp:Image runat="server" 
                                ID="SortImage1" 
                                ImageUrl="~/Images/asc.png" 
                                Visible="false" Width="15" 
                                Height="15" class="sort_image"/>
                            </th>
                            <th align="removed" style="width: 15%;" 
                            class="column_head">
                                <asp:LinkButton ID="lnkName" 
                                runat="server" Text="Name" 
                                Style="color: #FFFFFF" CommandName="Sort"
                                    CommandArgument="Name" />
                                <asp:Image runat="server" 
                                ID="SortImage2" ImageUrl="~/Images/asc.png" 
                                Visible="false"  Width="15" 
                                Height="15" class="sort_image"/>
                            </th>
                            <th align="removed" style="width: 20%" 
                            class="column_head">
                                <asp:LinkButton ID="lnkAddress" 
                                runat="server" Text="Address" 
                                Style="color: #FFFFFF"
                                    CommandName="Sort" 
                                    CommandArgument="Address" />
                                <asp:Image runat="server" 
                                ID="SortImage3" ImageUrl="~/Images/asc.png" 
                                Visible="false"  Width="15" 
                                Height="15" class="sort_image"/>
                            </th>
                            <th align="removed" style="width: 15%" 
                            class="column_head">
                                <asp:LinkButton ID="lnkEmail" 
                                runat="server" Text="Email" 
                                Style="color: #FFFFFF"
                                    CommandName="Sort" 
                                    CommandArgument="Email" />
                                    <asp:Image runat="server" 
                                    ID="SortImage4" 
                                    ImageUrl="~/Images/asc.png" 
                                    Visible="false"  Width="15" 
                                    Height="15" class="sort_image"/>
                            </th>
                            <th align="removed" style="width:12%" 
                            class="column_head">
                                <asp:LinkButton ID="lnkMobile" 
                                runat="server" Text="Mobile" 
                                Style="color: #FFFFFF"
                                    CommandName="Sort" 
                                    CommandArgument="Mobile" />
                                    <asp:Image runat="server" 
                                    ID="SortImage5" 
                                    ImageUrl="~/Images/asc.png" 
                                    Visible="false"  Width="15" 
                                    Height="15" class="sort_image"/>
                            </th>
                            <th align="removed" style="width: 15%; 
                            padding-left: 2px;">
                                <asp:LinkButton ID="lnkStatus" 
                                runat="server" Text="Status" 
                                Style="color: #FFFFFF"
                                    CommandName="Sort" 
                                    CommandArgument="Status" />
                                    <asp:Image runat="server" 
                                    ID="SortImage6" 
                                    ImageUrl="~/Images/asc.png" 
                                    Visible="false"  Width="15" 
                                    Height="15" class="sort_image"/>
                            </th>
                        </tr>
                        <tr id="itmPlaceholder"  runat="server">
                        </tr>
                    </table>
                </td>
            </tr>
            <tr style="height: 10px;">
                <td colspan="6">
                </td>
            </tr>
            <tr>
                <td>
                    <asp:DataPager ID="dataPagerNumeric" 
                    runat="server" PageSize="5">
            <Fields>
                <asp:NumericPagerField ButtonCount="5" 
                NumericButtonCssClass="numeric_button" 
                CurrentPageLabelCssClass="current_page"
                    NextPreviousButtonCssClass="next_button" />
            </Fields>
        </asp:DataPager>
        <td colspan="4" class="number_of_record">
            <asp:DataPager ID="dataPageDisplayNumberOfPages" 
            runat="server" PageSize="5">
                <Fields>
                    <asp:TemplatePagerField>
                        <PagerTemplate>
                            <span style="color: Black;">Records:
                                <%# Container.StartRowIndex >= 0 ? 
                                (Container.StartRowIndex + 1) : 0 %>
                                -
                                <%# (Container.StartRowIndex + 
                                Container.PageSize) > Container.TotalRowCount ? 
                                Container.TotalRowCount : (Container.StartRowIndex + 
                                Container.PageSize)%>
                                of
                                <%# Container.TotalRowCount %>
                                        </span>
                                    </PagerTemplate>
                                </asp:TemplatePagerField>
                            </Fields>
                        </asp:DataPager>
                    </td>
                </td>
            </tr>
        </table>
    </LayoutTemplate>
    <EmptyDataTemplate>
        <table class="empty_data"  runat="server">
            <tr>
                <td>
                    No data found.
                </td>
            </tr>
        </table>
    </EmptyDataTemplate>
    <ItemTemplate>
        <tr>
            <td align="removed" style="width: 7%;" 
            class='<%# Container.DataItemIndex % 2 == 0 ? 
            "alternate_row" : "row" %>'>
                <asp:Label ID="lblProfileId" 
                runat="server" Width="65" />
            </td>
            <td align="removed" 
            style="width: 10%;" class='<%# 
            Container.DataItemIndex % 2 == 0 ? 
            "alternate_row" : "row" %>'>
                <asp:Label ID="lblName" runat="server" />
            </td>
            <td align="removed" style="
            width: 25%;" class='<%# Container.DataItemIndex % 2 == 0 ? 
            "alternate_row" : "row" %>'>
                <asp:Label ID="lblAddress" runat="server" />
            </td>
            <td align="removed" style="width: 25%;" 
            class='<%# Container.DataItemIndex % 2 == 0 ? 
            "alternate_row" : "row" %>'>
                <asp:Label ID="lblEmail" runat="server" />
            </td>
            <td align="removed" style="
            width: 15%;" class='<%# Container.DataItemIndex % 2 == 0 ? 
            "alternate_row" : "row" %>'>
                <asp:Label ID="lblMobile" runat="server" />
            </td>
            <td align="removed" style="width: 8%;" 
            class='<%# Container.DataItemIndex % 2 == 0 ? 
            "alternate_row" : "row" %>'>
                <asp:Label ID="lblStatus" runat="server" />
            </td>
        </tr>
    </ItemTemplate>
</asp:ListView>

Now come to Visual Studio. Add a class I have created ProfileDataSource.cs. This type will be used by object data source. So we need to make it data source of object data source.

[DataObject(true)]
public class ProfileDataSource
{
    public ProfileDataSource()
    {
    }

    [DataObjectMethod(DataObjectMethodType.Select, false)]
    public Int32 TotalRowCount(Int32 startRowIndex, 
        Int32 pageSize, String sortExpression)
    {
        Int32 intTotalProfile = 0;

        using (SqlConnection conn = new SqlConnection
        (ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
        {
            SqlCommand cmdSelect = new SqlCommand();

            conn.Open();
            cmdSelect.CommandText = "Profile_Total";
            cmdSelect.CommandType = CommandType.StoredProcedure;
            cmdSelect.Connection = conn;

            SqlDataReader dataReader = cmdSelect.ExecuteReader();

            dataReader.Read();
            intTotalProfile = Convert.ToInt32(dataReader[0]);
        }
        return intTotalProfile;
    }

    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public static DataTable GetProfileData
    (Int32 startRowIndex, Int32 pageSize, String sortExpression)
    {
        DataTable profileDataTable = new DataTable();

        using (SqlConnection conn = new SqlConnection
        (ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString()))
        {
            SqlCommand cmdSelect = new SqlCommand();

            conn.Open();
            cmdSelect.CommandText = "Profile_GET";
            cmdSelect.CommandType = CommandType.StoredProcedure;
            cmdSelect.Connection = conn;                

            startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;
            
            if (String.IsNullOrEmpty(sortExpression))
                sortExpression = "ProfileId";

            cmdSelect.Parameters.AddWithValue("@CurrentPage", startRowIndex);
            cmdSelect.Parameters.AddWithValue("@PageSize", pageSize);
            cmdSelect.Parameters.AddWithValue("@SortExpression", sortExpression);

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = cmdSelect;

            dataAdapter.Fill(profileDataTable);                
        }
        return profileDataTable;
    }
}

Add the following namespaces:

using System.Configuration;
using System.Data.SqlClient;
using System.ComponentModel;

Now tag the type ProfileDataSource as data object by adding DataObject attribute.

[DataObject(true)]
 public class ProfileDataSource
 {
 }

Add two methods in this type. TotalRowCount is to get total number of records from underling table. GetProfileData to get paged records from underling table.

Now make methods for Data Object Select method.

[DataObjectMethod(DataObjectMethodType.Select, false)]
public Int32 TotalRowCount(Int32 startRowIndex, Int32 pageSize, String sortExpression)
{
}

[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable GetProfileData(Int32 startRowIndex, 
    Int32 pageSize, String sortExpression)
{
}

Write body for TotalRowCount method. Parameters of this method will pass by object data source control later. This will execute Ptofile_Total stored procedure that we have already created and will return total number of records to object data source control.

[DataObjectMethod(DataObjectMethodType.Select, false)]
 public Int32 TotalRowCount(Int32 startRowIndex, 
        Int32 pageSize, String sortExpression)
 {
    Int32 intTotalProfile = 0;

    using (SqlConnection conn = new SqlConnection
    (ConfigurationManager.ConnectionStrings
        ["ApplicationServices"].ToString()))
    {
         SqlCommand cmdSelect = new SqlCommand();

         conn.Open();
         cmdSelect.CommandText = "Profile_Total";
         cmdSelect.CommandType = CommandType.StoredProcedure;
         cmdSelect.Connection = conn;

         SqlDataReader dataReader = cmdSelect.ExecuteReader();

         dataReader.Read();
         intTotalProfile = Convert.ToInt32(dataReader[0]);
    }
    return intTotalProfile;
}

Write body for GetProfileData method. Parameters of this method will be passed by object data source control. This will execute Profile_GET stored procedure that we have already created with parameter to get paged records for each request. Finally, this method will return a data table to object data source control.

[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable GetProfileData(Int32 startRowIndex, 
    Int32 pageSize, String sortExpression)
{
DataTable profileDataTable = new DataTable();

using (SqlConnection conn = new SqlConnection
    (ConfigurationManager.ConnectionStrings
        ["ApplicationServices"].ToString()))
{
SqlCommand cmdSelect = new SqlCommand();

conn.Open();
cmdSelect.CommandText = "Profile_GET";
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.Connection = conn;                

              startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;

              if (String.IsNullOrEmpty(sortExpression))
                 sortExpression = "ProfileId";

              cmdSelect.Parameters.AddWithValue("@CurrentPage", startRowIndex);
              cmdSelect.Parameters.AddWithValue("@PageSize", pageSize);
              cmdSelect.Parameters.AddWithValue("@SortExpression", sortExpression);

              SqlDataAdapter dataAdapter = new SqlDataAdapter();
              dataAdapter.SelectCommand = cmdSelect;

         dataAdapter.Fill(profileDataTable);                
            }
            return profileDataTable;
        }


   startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;

The line of code calculates start row index form startRowIndex and pageSize parameters.

Object data source control passes sortExpression parameter to null when page is first time loaded. In this case, sort expression will be ProfileId that is primary key for the underling table.

if (String.IsNullOrEmpty(sortExpression))
sortExpression = "ProfileId";

Now add Object Data Source in the ASPX page.

<asp:ObjectDataSource ID="odsPforile" 
runat="server" SelectMethod="GetProfileData"
        SelectCountMethod="TotalRowCount" EnablePaging="true" 
        MaximumRowsParameterName="pageSize"
        StartRowIndexParameterName="startRowIndex" 
        TypeName="VTS.Web.UI.ProfileDataSource"
        SortParameterName="sortExpression">
        <SelectParameters>
            <asp:Parameter Name="startRowIndex" Type="Int32" />
            <asp:Parameter Name="pageSize" Type="Int32" />
            <asp:Parameter Name="sortExpression" Type="String" />
        </SelectParameters>
    </asp:ObjectDataSource>

Set some properties EnablePaging to true for paging, MaximumRowsParameterName to pageSize for current page size, StartRowIndexParameterName to startRowIndex for start row number position, TypeName to VTS.Web.UI.ProfileDataSource for Data object with namespace, SelectMethod to GetProfileData to get paged records, SelectCountMethod to TotalRowCount to get total records from underlining table will be used for paging, SortParameterName to sortExpression for sorting.

Select parameters for object data source:

<SelectParameters>
<asp:Parameter Name="startRowIndex" Type="Int32" />
<asp:Parameter Name="pageSize" Type="Int32"/>
<asp:Parameter Name="sortExpression" Type="String" />            
</SelectParameters>

Now configure listProfileView like below:

  <asp:ListView ID="lstProfileView" runat="server" 
    DataSourceID="odsPforile" DataKeyNames="ProfileId"
            ItemPlaceholderID="itmPlaceholder" OnItemDataBound="Profile_ItemDataBound" 
    OnSorting="Profile_Sorting">

Now you need to write two events for data binding and sorting.

protected void Profile_ItemDataBound(object sender, ListViewItemEventArgs e)
{
    if (e.Item.ItemType == ListViewItemType.DataItem)
    {
        System.Data.DataRowView rowView = 
    e.Item.DataItem as System.Data.DataRowView;

        Label lblProfileId = (Label)e.Item.FindControl("lblProfileId");
        Label lblName = (Label)e.Item.FindControl("lblName");
        Label lblAddress = (Label)e.Item.FindControl("lblAddress");
        Label lblEmail = (Label)e.Item.FindControl("lblEmail");
        Label lblMobile = (Label)e.Item.FindControl("lblMobile");
        Label lblStatus = (Label)e.Item.FindControl("lblStatus");

        lblProfileId.Text = rowView["ProfileId"].ToString();
        lblName.Text = rowView["Name"].ToString();
        lblAddress.Text = rowView["Address"].ToString();
        lblEmail.Text = rowView["Email"].ToString();
        lblMobile.Text = rowView["Mobile"].ToString();
        lblStatus.Text = rowView["IsActive"].ToString();                
    }
}

The above event will set text to item template controls.

protected void Profile_Sorting(object sender, ListViewSortEventArgs e)
{            
    String imgUrl;
    if (e.SortDirection == SortDirection.Ascending)
        imgUrl = "~/images/asc.png";
    else
        imgUrl = "~/images/desc.png";
    
    Image sortImage1 = (Image)lstProfileView.FindControl("SortImage1");
    Image sortImage2 = (Image)lstProfileView.FindControl("SortImage2");
    Image sortImage3 = (Image)lstProfileView.FindControl("SortImage3");
    Image sortImage4 = (Image)lstProfileView.FindControl("SortImage4");
    Image sortImage5 = (Image)lstProfileView.FindControl("SortImage5");
    Image sortImage6 = (Image)lstProfileView.FindControl("SortImage6");

    sortImage1.Visible = false;
    sortImage2.Visible = false;
    sortImage3.Visible = false;
    sortImage4.Visible = false;
    sortImage5.Visible = false;
    sortImage6.Visible = false;

    switch (e.SortExpression)
    {
        case "ProfileId":
            sortImage1.Visible = true;
            sortImage1.ImageUrl = imgUrl;
            break;
        case "Name":
            sortImage2.Visible = true;
            sortImage2.ImageUrl = imgUrl;
            break;
        case "Address":
            sortImage3.Visible = true;
            sortImage3.ImageUrl = imgUrl;
            break;
        case "Email":
            sortImage4.Visible = true;
            sortImage4.ImageUrl = imgUrl;
            break;
        case "Mobile":
            sortImage5.Visible = true;
            sortImage5.ImageUrl = imgUrl;
            break;
        case "Status":
            sortImage6.Visible = true;
            sortImage6.ImageUrl = imgUrl;
            break;
    }
}

The above event will set ascending and decending image when sorting.

PagingWithListView/descending.JPG
Figure 7

Now run the application. You will get output like below:

PagingWithListView/finaloutput.JPG

Figure 8

How It Works

Open SQL Profile from SQL Management Studio under Tools menu to diagnose how things work behind effective paging.

  1. Open New Trace from File menu
  2. Login using your credential
  3. Select Run
  4. Click Clear Trace Window under Edit menu to clear existing trace
  5. Minimize SQL Server Profiler
  6. Run the Application from Visual Studio to browse PagingWithListView.aspx page

After you run the application for the first time, you will get the trace like below:

PagingWithListView/Tace1.JPG
Figure 9

By clicking next numeric button:

PagingWithListView/nextbuttonoutput.JPG

Figure 10

In Profiler:

PagingWithListView/Trace2.JPG

Figure 11

By clicking column head to sort:

PagingWithListView/descending.JPG

Figure 12

In profiler:

PagingWithListView/Trace3.JPG

Figure 13

Continue these steps for all of the pages that are available in the bottom of the ProfileView also for sorting by clicking header of each column. You will get 5 records for each request. So there is no change to load all the records from the underling table. Only the number of records load that you have set to pageSize property in Listview. Records load faster than earlier normal paging. It will take less system resource. It is very effective for thousands of thousand data.

Conclusion

Effective paging is important for applications that handle large number of records to build scalable applications. This demonstration will help you to create scalable applications.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Abdul Quader Mamun
Team Leader
Bangladesh Bangladesh
I have been developing software/web applications since 2002 mainly on Microsoft technologies.
 
I'm also experienced with other technologies.
 
I'm fan of well organized and effective coding.
 
A wide range of technologies, tools, languages, frameworks, components and design patterns I’m experienced with.
 
I’m a MCP, MCAD.NET, MCSD.NET.
Follow on   Google+

Comments and Discussions

 
GeneralGreat Article Pinmemberarvind mepani10-Jul-14 0:14 
QuestionHow to achieve this without using ObjectDataSource PinmemberMember 246525816-Aug-13 6:16 
GeneralMy vote of 5 Pinmembertanweer akhtar23-Dec-12 20:47 
QuestionOne More Solution of DataPager, May be its Easy PinmemberMustafaIqbal11-Nov-12 18:54 
GeneralMy vote of 5 PinmemberАslam Iqbal11-Nov-12 17:37 
GeneralMy vote of 5 Pinmembercsharpbd9-Nov-12 20:20 
QuestionSorting & Paging PinmemberMustafaIqbal5-Nov-12 18:17 
AnswerRe: Sorting & Paging PinmemberAbdul Quader Mamun6-Nov-12 4:39 
Please save the table in ViewState or session that you bind in Gridview. After that, create the bellow event. and code like bellow(example code it may help you):
 
protected void gvUser_PageIndexChanging(Object sender, GridViewPageEventArgs e)
{
gvUser.PageIndex = e.NewPageIndex;
 
DataTable dtPrefineUser = (DataTable)ViewStateHelper.Get(ViewStateConstant.PREDEFINE_USER_TABLE);// get the table from viewstate you may have different.
 
gvUser.DataSource = dtPrefineUser;
gvUser.DataBind();
 
if (dtPrefineUser.Rows.Count > 0)
{
lblTotalRecord.Visible = true;
}
 
if (e.NewPageIndex + 1 == gvUser.PageCount)
{
lblTotalRecord.Text = String.Format("Record(s) {0} of {1}", dtPrefineUser.Rows.Count, dtPrefineUser.Rows.Count);
}
else if (e.NewPageIndex + 1 < gvUser.PageCount)
{
lblTotalRecord.Text = String.Format("Record(s) {0} of {1}", ((e.NewPageIndex + 1) * gvUser.PageSize), dtPrefineUser.Rows.Count);
}
 
}
GeneralRe: Sorting & Paging PinmemberMustafaIqbal11-Nov-12 18:49 
Questionbroken link in article ... and a question PinmemberBillWoodruff16-Aug-12 14:26 
AnswerRe: broken link in article ... and a question [modified] PinmemberAbdul Quader Mamun16-Aug-12 15:34 
QuestionNice example but... Pinmembersimpa13-Aug-12 2:37 
AnswerRe: Nice example but... PinmemberAbdul Quader Mamun13-Aug-12 2:47 
GeneralMy vote of 5 PinmemberAjith_joseph7-Aug-12 20:03 
QuestionOnly 5* can be possible Pinmemberricardolourenco6-Aug-12 5:01 
GeneralMy vote of 5 Pinmembermehrtash29-Jul-12 8:09 
QuestionError correction: ROWNUMBER() Pinmemberjohnchen8827-Mar-12 5:58 
GeneralMy vote of 5 PinmemberHaBiX16-Nov-11 21:11 
GeneralMy vote of 5 PinmemberMai Tho11-Feb-11 16:19 
GeneralMy vote of 5 PinmemberBryian Tan27-Jan-11 11:01 
GeneralMy vote of 5 Pinmember[raju.m][makhaai]10-Jan-11 12:47 
GeneralMy vote of 5 PinmvpMd. Marufuzzaman6-Jan-11 5:49 
GeneralMy vote of 5 PinmemberSunasara Imdadhusen6-Jan-11 0:52 
Generalvote of 5 PingroupTHE SK5-Jan-11 19:46 
GeneralMy vote of 5 PinmemberRaviRanjankr5-Jan-11 7:29 
GeneralMy vote of 5 Pinmemberprasad024-Jan-11 4:50 
GeneralRe: My vote of 5 PinmemberAbdul Quader Mamun4-Jan-11 5:06 
Generalcool - have 5 PinmemberPranay Rana3-Jan-11 21:16 
GeneralRe: cool - have 5 PinmemberAbdul Quader Mamun4-Jan-11 5:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 9 Nov 2012
Article Copyright 2011 by Abdul Quader Mamun
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid