How to implement efficient paging code for ListView

When ListView is selected for automatic paging, A DataPager paging control is added. There is a nested relationship between the two. As mentioned in Repeater and ListView, such paging is not efficient because the data source still returns all data instead of the current page data

Optimization solution and steps:

1. Set the EnablePaging attribute of the data source to true (pagination allowed]

Set MaximumRowsParameterName = "rowIndex" [MSDN explanation: the value of the number of rows retrieved by this parameter can be understood as the subscript of the last row of the previous page]

Set StartRowIndexParameterName = "pageSize" [MSDN explanation: this parameter accepts the value of the first index to be retrieved. It can be understood as pageSize, that is, the number of entries displayed on each page]

SelectCountMethod = "GetTotalRowsCount" [The method to be executed when the total number of rows is required is to tell the page control how to display the total number of data records]

2. In this case, the original method getAllClasses called by the data source no longer meets the requirements. You need to add a new method with the MaximumRowsParameterName and StartRowIndexParameterName parameter names in the business layer and GetTotalRowsCount.

The BLL layer is added as follows:

Copy codeThe Code is as follows: View Code

Public List <MODEL. Classes> getPageListByPage (int pageSize, int rowIndex) {return dal. getPageListByPage (pageSize, rowIndex, false );
}

Public int GetTotalRowsCount (){
Return dal. GetTotalRowsCount ();
}

The DAL layer is added as follows:

Copy codeThe Code is as follows: View Code

Public List <MODEL. Classes> getPageListByPage (int rowIndex, int pageSize, bool isDel) {int rowCount = 0;
Int pageCount = 0;
DataTable dt = SqlHelper. getPageListByPage (rowIndex, pageSize, out rowCount, out pageCount, isDel );
If (dt. Rows. Count> 0 ){
List <MODEL. Classes> list = new List <MODEL. Classes> ();
Foreach (DataRow dr in dt. Rows ){
MODEL. Classes model = new MODEL. Classes ();
LoadEntityData (model, dr );
List. Add (model );
}
Return list;
}
Return null;
}

Public int GetTotalRowsCount (){
String sqlstr = "select * from classes where cisdel = 0 ";
Return SqlHelper. ExecuteScalar (sqlstr );
}

SqlHelper is added as follows:

Copy codeThe Code is as follows: View Code

Public static DataTable getPageListByPage (int rowIndex, int pageSize, out int rowCount, out int pageCount, bool isDel) {DataTable dtcalss = new DataTable ();
RowCount = 0;
PageCount = 0;
Using (SqlConnection sqlcon = new SqlConnection (Connstr )){
SqlDataAdapter sda = new SqlDataAdapter ("up_GetPageData2", sqlcon );
SqlParameter [] pars = {
New SqlParameter ("@ LastRowIndex", rowIndex ),
New SqlParameter ("@ pgSize", pageSize ),
New SqlParameter ("@ rowCount", rowCount ),
New SqlParameter ("@ pgCount", pageCount ),
New SqlParameter ("@ isDel", isDel ),
};
// Specify the output direction of the two output parameters
Pars [2]. Direction = ParameterDirection. Output;
Pars [3]. Direction = ParameterDirection. Output;
// Add the parameter set to the query command object
Sda. SelectCommand. Parameters. AddRange (pars );
// Set the query command type to Stored Procedure
Sda. SelectCommand. CommandType = CommandType. StoredProcedure;
// Execute the Stored Procedure
Sda. Fill (dtcalss );
// After execution, the two output parameter values obtained by the stored procedure are assigned to the two output parameters of this method.
RowCount = Convert. ToInt32 (pars [2]. Value );
PageCount = Convert. ToInt32 (pars [3]. Value );
}
Return dtcalss;
}

The stored procedure up_GetPageData2 code is as follows:

Copy codeThe Code is as follows: View Code

Create proc up_GetPageData2
@ LastRowIndex int, --- subscript of the last row of the previous page
@ PgSize float, -- page size
@ RowCount int output, --- total number of output rows
@ PgCount int output, --- total number of output pages
@ IsDel bit -- whether to delete the data
As
Begin
Select @ rowCount = count (*) from classes where cisdel = @ isDel -- check the total number of rows
Set @ pgCount = ceiling (@ rowCount/@ pgSize) -- calculate the total number of pages
Select * from (
Select Row_Number () over (order by cid) as RNum, * from classes where cisdel = @ isDel
) As temp
Where RNum> @ LastRowIndex and RNum <= @ LastRowIndex + @ pgSize
End

The ListView. aspx code is as follows:

Copy codeThe Code is as follows: View Code

<% @ Page Language = "C #" AutoEventWireup = "true" CodeBehind = "ListView. aspx. cs" Inherits = "WebForm. ListView" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>

<Asp: ObjectDataSource ID = "objectperformance1" runat = "server"
SelectMethod = "getPageListByPage" TypeName = "BLL. Classes"
DataObjectTypeName = "MODEL. Classes" DeleteMethod = "SoftDel" InsertMethod = "Add"
UpdateMethod = "Modify" EnablePaging = "True"
MaximumRowsParameterName = "rowIndex" SelectCountMethod = "GetTotalRowsCount"
StartRowIndexParameterName = "pageSize">
</Asp: ObjectDataSource>
<Asp: ListView ID = "ListView1" runat = "server" performanceid = "objectperformance1"
InsertItemPosition = "LastItem">
<AlternatingItemTemplate>
<Tr style = "">
<Td>
<Asp: Button ID = "DeleteButton" runat = "server" CommandName = "Delete" Text = "Delete"/>
<Asp: Button ID = "EditButton" runat = "server" CommandName = "Edit" Text = "Edit"/>
</Td>
<Td>
<Asp: Label ID = "CIDLabel" runat = "server" Text = '<% # Eval ("CID") %>'/>
</Td>
<Td>
<Asp: Label ID = "CNameLabel" runat = "server" Text = '<% # Eval ("CName") %>'/>
</Td>
<Td>
<Asp: Label ID = "CCountLabel" runat = "server" Text = '<% # Eval ("CCount") %>'/>
</Td>
<Td>
<Asp: Label ID = "CImgLabel" runat = "server" Text = '<% # Eval ("CImg") %>'/>
</Td>
<Td>
<Asp: CheckBox ID = "CIsDelCheckBox" runat = "server"
Checked = '<% # Eval ("CIsDel") %>' Enabled = "false"/>
</Td>
<Td>
<Asp: Label ID = "CAddTimeLabel" runat = "server" Text = '<% # Eval ("CAddTime") %>'/>
</Td>
</Tr>
</AlternatingItemTemplate>

<EditItemTemplate>
<Tr style = "">
<Td>
<Asp: Button ID = "UpdateButton" runat = "server" CommandName = "Update" Text = "Update"/>
<Asp: Button ID = "CancelButton" runat = "server" CommandName = "Cancel" Text = "Cancel"/>
</Td>
<Td>
<Asp: TextBox ID = "CIDTextBox" runat = "server" Text = '<% # Bind ("CID") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CNameTextBox" runat = "server" Text = '<% # Bind ("CName") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CCountTextBox" runat = "server" Text = '<% # Bind ("CCount") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CImgTextBox" runat = "server" Text = '<% # Bind ("CImg") %>'/>
</Td>
<Td>
<Asp: CheckBox ID = "CIsDelCheckBox" runat = "server"
Checked = '<% # Bind ("CIsDel") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CAddTimeTextBox" runat = "server"
Text = '<% # Bind ("CAddTime") %>'/>
</Td>
</Tr>
</EditItemTemplate>
<EmptyDataTemplate>
<Table runat = "server"

Style = "">
<Tr>
<Td>
No data is returned. </Td>
</Tr>
</Table>
</EmptyDataTemplate>
<InsertItemTemplate>
<Tr style = "">
<Td>
<Asp: Button ID = "InsertButton" runat = "server" CommandName = "Insert" Text = "Insert"/>
<Asp: Button ID = "CancelButton" runat = "server" CommandName = "Cancel" Text = "clear"/>
</Td>
<Td>
<Asp: TextBox ID = "CIDTextBox" runat = "server" Text = '<% # Bind ("CID") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CNameTextBox" runat = "server" Text = '<% # Bind ("CName") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CCountTextBox" runat = "server" Text = '<% # Bind ("CCount") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CImgTextBox" runat = "server" Text = '<% # Bind ("CImg") %>'/>
</Td>
<Td>
<Asp: CheckBox ID = "CIsDelCheckBox" runat = "server"
Checked = '<% # Bind ("CIsDel") %>'/>
</Td>
<Td>
<Asp: TextBox ID = "CAddTimeTextBox" runat = "server"
Text = '<% # Bind ("CAddTime") %>'/>
</Td>
</Tr>
</InsertItemTemplate>
<ItemTemplate>
<Tr style = "">
<Td>
<Asp: Button ID = "DeleteButton" runat = "server" CommandName = "Delete" Text = "Delete"/>
<Asp: Button ID = "EditButton" runat = "server" CommandName = "Edit" Text = "Edit"/>
</Td>
<Td>
<Asp: Label ID = "CIDLabel" runat = "server" Text = '<% # Eval ("CID") %>'/>
</Td>
<Td>
<Asp: Label ID = "CNameLabel" runat = "server" Text = '<% # Eval ("CName") %>'/>
</Td>
<Td>
<Asp: Label ID = "CCountLabel" runat = "server" Text = '<% # Eval ("CCount") %>'/>
</Td>
<Td>
<Asp: Label ID = "CImgLabel" runat = "server" Text = '<% # Eval ("CImg") %>'/>
</Td>
<Td>
<Asp: CheckBox ID = "CIsDelCheckBox" runat = "server"
Checked = '<% # Eval ("CIsDel") %>' Enabled = "false"/>
</Td>
<Td>
<Asp: Label ID = "CAddTimeLabel" runat = "server" Text = '<% # Eval ("CAddTime") %>'/>
</Td>
</Tr>
</ItemTemplate>
<LayoutTemplate>
<Table runat = "server">
<Tr runat = "server">
<Td runat = "server">
<Table ID = "itemPlaceholderContainer" runat = "server" border = "0"

Style = "">
<Tr runat = "server" style = "">
<Th runat = "server">
</Th>
<Th runat = "server">
CID </th>
<Th runat = "server">
CName </th>
<Th runat = "server">
CCount </th>
<Th runat = "server">
CImg </th>
<Th runat = "server">
CIsDel </th>
<Th runat = "server">
CAddTime </th>
</Tr>
<Tr ID = "itemPlaceholder" runat = "server">
</Tr>
</Table>
</Td>
</Tr>
<Tr runat = "server">
<Td runat = "server"

Style = "">
</Td>
</Tr>
</Table>
</LayoutTemplate>
<SelectedItemTemplate>
<Tr style = "">
<Td>
<Asp: Button ID = "DeleteButton" runat = "server" CommandName = "Delete" Text = "Delete"/>
<Asp: Button ID = "EditButton" runat = "server" CommandName = "Edit" Text = "Edit"/>
</Td>
<Td>
<Asp: Label ID = "CIDLabel" runat = "server" Text = '<% # Eval ("CID") %>'/>
</Td>
<Td>
<Asp: Label ID = "CNameLabel" runat = "server" Text = '<% # Eval ("CName") %>'/>
</Td>
<Td>
<Asp: Label ID = "CCountLabel" runat = "server" Text = '<% # Eval ("CCount") %>'/>
</Td>
<Td>
<Asp: Label ID = "CImgLabel" runat = "server" Text = '<% # Eval ("CImg") %>'/>
</Td>
<Td>
<Asp: CheckBox ID = "CIsDelCheckBox" runat = "server"
Checked = '<% # Eval ("CIsDel") %>' Enabled = "false"/>
</Td>
<Td>
<Asp: Label ID = "CAddTimeLabel" runat = "server" Text = '<% # Eval ("CAddTime") %>'/>
</Td>
</Tr>
</SelectedItemTemplate>
</Asp: ListView>

</Div>
<Asp: DataPager ID = "DataPager1" runat = "server" PagedControlID = "ListView1"
PageSize = "5">
<Fields>
<Asp: NextPreviousPagerField ButtonType = "Button" ShowFirstPageButton = "True"
ShowLastPageButton = "True"/>
</Fields>
</Asp: DataPager>
</Form>
</Body>
</Html>

3. Delete "enable pagination" in ListView1 and drag the pagination control DataPage to the page by page. Set PagedControlID = "ListView1" to associate it with ListView1.

4. Modify the data source call method to getPageListByPage. The running result is as follows:

Supplement:

If the running error 'objectdatasource "objectperformance1" is reported, the non-generic method "getPageListByPage" with parameters cannot be found: pageSize, pageIndex. '

You only need to delete

<SelectParameters>

<Asp: Parameter DefaultValue = "5" Name = "pageSize" Type = "Int32"/>

<Asp: Parameter Name = "rowIndex" Type = "Int32"/>

</SelectParameters>