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.
Figure 1
Some records from Profile table.
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.
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.
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:
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:
Figure 10
In Profiler:
Figure 11
By clicking column head to sort:
Figure 12
In profiler:
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
Smile | 