首先,写一个实现高效分页的存储过程:
create procedure [dbo].[p_selectPagedStudents]
@startRowIndex int,
@maximumRows int
as
create table #PageIndex
(indexID int identity(1,1) not null,
recordID int)
insert into #PageIndex
select stuNumber from student
select s.* from student as s
inner join #PageIndex as n
on s.stuNumber =n.recordID
where n.indexID >@startRowIndex and n.indexID<@startRowIndex+@maximumRows+1
order by n.indexID
其次,写一个操作数据的实体类:
public class Student
{
public int StuNumber { get; set; }
public string StuName { get; set; }
public DateTime StuBirthday { get; set; }
public bool StuGender { get; set; }
public string StuClass { get; set; }
public decimal StuTuition { get; set; }
public string StuPhoto { get; set; }
public List<Student> GetStudents()
{
List<Student> list = new List<Student>();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString);
SqlCommand comm = new SqlCommand("select * from student", conn);
using (conn)
{
conn.Open();
using (SqlDataReader sdr = comm.ExecuteReader())
{
while (sdr.Read())
{
Student s = new Student();
s.StuNumber = sdr.GetInt32(0);
s.StuName = sdr.GetString(1);
s.StuBirthday = sdr.GetDateTime(2);
s.StuGender = sdr.GetBoolean(3);
s.StuClass = sdr.GetString(4);
s.StuTuition = sdr.GetDecimal(5);
s.StuPhoto = sdr.GetString(6);
list.Add(s);
}
}
}
return list;
}
public List<Student> GetStudents(int start, int count)
{
List<Student> list = new List<Student>();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString);
SqlCommand comm = new SqlCommand("p_selectPagedStudents", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@startRowIndex", start);
comm.Parameters.AddWithValue("@maximumRows", count);
using (conn)
{
conn.Open();
using (SqlDataReader sdr = comm.ExecuteReader())
{
while (sdr.Read())
{
Student s = new Student();
s.StuNumber = sdr.GetInt32(0);
s.StuName = sdr.GetString(1);
s.StuBirthday = sdr.GetDateTime(2);
s.StuGender = sdr.GetBoolean(3);
s.StuClass = sdr.GetString(4);
s.StuTuition = sdr.GetDecimal(5);
s.StuPhoto = sdr.GetString(6);
list.Add(s);
}
}
}
return list;
}
//public SqlDataReader GetStudents(int start, int count)
//{
// //List<Student> list = new List<Student>();
// SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString);
// SqlCommand comm = new SqlCommand("p_selectPagedStudents", conn);
// comm.CommandType = CommandType.StoredProcedure;
// comm.Parameters.AddWithValue("@startRowIndex", start);
// comm.Parameters.AddWithValue("@maximumRows", count);
// using (conn)
// {
// conn.Open();
// return comm.ExecuteReader();
// }
//}
public int GetStudentCount()
{
HttpContext context = HttpContext.Current;
if (context.Cache["StuCount"] == null)
{
context.Cache["StuCount"] = GetStuCountFromDB();
}
return (int)context.Cache["StuCount"];
}
private int GetStuCountFromDB()
{
int i = 0;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["testDBConnectionString"].ConnectionString);
SqlCommand comm = new SqlCommand("select Count(*) from student", conn);
using (conn)
{
conn.Open();
i = (int)comm.ExecuteScalar();
}
return i;
}
}
注意该实体类中,用于读取分页数据的参数分别是:start(起始记录)和count(每次显示的记录数量)。
最后,设计前台页面:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListView GroupItemCount="2" ID="ListView1" runat="server" DataSourceID="ObjectDataSource1" ---------------1
DataKeyNames="stuNumber">
<LayoutTemplate>
<table runat="server">
<tr runat="server" id="groupPlaceholder">---------------------------------------------------2
</tr>
</table>
</LayoutTemplate>
<GroupTemplate>
<tr runat ="server">
<td id="itemPlaceholder" runat ="server" ></td>------------------------------------------------3
</tr>
</GroupTemplate>
<ItemTemplate>
<td runat ="server" >---------------------------------------------------------------------------------4
<table>
<tr style="">
<td>
<asp:Label ID="StuNumberLabel" runat="server" Text='<%# Eval("StuNumber") %>' />
</td>
<td>
<asp:Label ID="StuNameLabel" runat="server" Text='<%# Eval("StuName") %>' />
</td>
<td>
<asp:Label ID="StuBirthdayLabel" runat="server"
Text='<%# Eval("StuBirthday") %>' />
</td>
<td>
<asp:CheckBox ID="StuGenderCheckBox" runat="server"
Checked='<%# Eval("StuGender") %>' Enabled="false" />
</td>
<td>
<asp:Label ID="StuClassLabel" runat="server" Text='<%# Eval("StuClass") %>' />
</td>
<td>
<asp:Label ID="StuTuitionLabel" runat="server"
Text='<%# Eval("StuTuition") %>' />
</td>
<td>
<asp:Label ID="StuPhotoLabel" runat="server" Text='<%# Eval("StuPhoto") %>' />
</td>
</tr>
</table>
</td>
</ItemTemplate>
</asp:ListView>
<asp:DataPager PagedControlID="ListView1" ID="DataPager1" runat="server" PageSize="4">--------------5
<Fields>
<asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True"
ShowLastPageButton="True" />
</Fields>
</asp:DataPager>
<br />
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True"
SelectMethod="GetStudents" TypeName="ObjectDataSource控件.Student"
SelectCountMethod="GetStudentCount" MaximumRowsParameterName="count"
StartRowIndexParameterName="start">--------------------------------------------6
</asp:ObjectDataSource>
<br />
</div>
</form>
</body>
</html>
这样即可实现像DataList那样的自定义呈现,并轻松实现高效分页。(当然你可以把ListView中ItemTemplate中的table设置的更美观些)。
注意,红色标注:
1、指定了数据源控件,同时GroupItemCount类似于DataList中的RepeatColumns,用于指定水平显示(一组)几条记录。此处为一行显示2条记录。
2、tr的id属性此处为"groupPlaceholder",这个id可以修改,但一定要修改的和ListView属性框中的groupPlaceholderID一致。
3、在groupTemplate中,td的属性此处为"itemPlaceholder",同2,也可以修改,但要与ListView属性框中的itemPlaceholderID一致。
4、在ItemTemplate中的td容器,一定要有,而且加runat=”server"。
5、DataPager是asp.net4新增的,目前该 控件只能和ListView一起用,可以写在ListView的LayouTemplate中,也可以的单独写在外面。写在外部时,要设置PageControlID属性,同时此处指定了ListView每页显示的记录数(4),由于ListView做了分组,所以4条记录就回显示在2行中,每行2个。
6、ObjectDataSource,要指定EnablePaging,SelectMethod,SelectCountMethod属性,另外特别注意:如果SelectMethod属性指定的方法的参数不是“startRowIndex"和"maximumRows",本例中分别是start和count,那么必须修改MaximumRowsParameterName和StartRowIndexParameterName属性的值和方法的参数名一致。否则报错。