Effective paging with List View control in ASP.NET

Download source - 8.67 KB

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.

Open SQL Server Management Studio
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

Create a website project and name it to PagingWithListView
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.

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.

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.

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.

Open New Trace from File menu
Login using your credential
Select Run
Click Clear Trace Window under Edit menu to clear existing trace
Minimize SQL Server Profiler
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:

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.

Hint: For improved responsiveness ensure Javascript is enabled and choose 'Normal' from the Layout dropdown and hit 'Update'.
You must Sign In to use this message board.

Search
	Per page