Asp.Net真分页技术
最近学校要做课题,闲来没事研究了下asp.net的分页,我使用repeater进行数据的绑定,每次从数据库读取到8条数据填充到repeater中,这样搞可以降低数据库的压力,提高效率.
效果图如下:
数据库设计如下:
附加一下代码:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title></title>
<link rel="stylesheet" href="layui.css" />
<link rel="stylesheet" href="bootstrap.css" />
<style type="text/css">
.pages {
color: #999;
overflow: auto;
}
.pages a, .pages .cpb {
text-decoration: none;
float: left;
padding: 0 5px;
border: 1px solid #ddd;
background: #ffff;
margin: 0 2px;
font-size: 17px;
color: #000;
}
.pages a:hover {
background-color: #347ab6;
color: #fff;
border: 1px solid #347ab6;
text-decoration: none;
}
.pages .cpb {
font-size:large;
font-weight: bold;
color: #fff;
background: #347ab6;
border: 1px solid #347ab6;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="width:500px;top:100px;">
<asp:repeater id="repeater1" runat="server" onitemcommand="repeater1_itemcommand" onitemdatabound="repeater1_itemdatabound">
<headertemplate>
<table class="layui-table">
<thead>
<tr>
<th>产品id</th>
<th>产品</th>
<th>季度</th>
<th>销售量</th>
<th>操作</th>
</tr>
</thead>
<tbody>
</headertemplate>
<itemtemplate>
<asp:panel id="plitem" runat="server">
<tr>
<td>
<asp:label id="label1" runat="server" text='<%# bind("tableid") %>'></asp:label>
</td>
<td>
<asp:label id="label2" runat="server" text='<%# bind("tablename") %>'></asp:label>
</td>
<td>
<asp:label id="label3" runat="server" text='<%# bind("tablejidu") %>'></asp:label>
</td>
<td>
<asp:label id="label4" runat="server" text='<%# bind("tablenumber") %>'></asp:label>
</td>
<td>
<asp:linkbutton id="lbtedit" class="layui-btn layui-btn-xs" commandname="edit" commandargument='<%#eval("tableid") %>' runat="server">编辑</asp:linkbutton>
<asp:linkbutton id="lbtdelete" class="layui-btn layui-btn-danger layui-btn-xs" commandname="delete" commandargument='<%#eval("tableid") %>' runat="server">删除</asp:linkbutton>
</td>
</tr>
</asp:panel>
<asp:panel id="pledit" runat="server">
<tr>
<td>
<asp:label id="label5" runat="server" text='<%# bind("tableid") %>'></asp:label>
</td>
<td>
<asp:textbox id="txttablename" runat="server" text='<%#eval("tablename") %>'></asp:textbox>
</td>
<td>
<asp:textbox id="txttablejidu" runat="server" text='<%#eval("tablejidu") %>'></asp:textbox>
</td>
<td>
<asp:textbox id="txttablenumber" runat="server" text='<%#eval("tablenumber") %>'></asp:textbox>
</td>
<td>
<asp:linkbutton id="lbtcancel" class="layui-btn layui-btn-xs" commandname="cancel" commandargument='<%#eval("tableid") %>' runat="server">取消</asp:linkbutton>
<asp:linkbutton id="lbtupdate" class="layui-btn layui-btn-xs" commandname="update" commandargument='<%#eval("tableid") %>' runat="server">更新</asp:linkbutton>
</td>
</tr>
</asp:panel>
</itemtemplate>
<footertemplate>
</tbody></table>
</footertemplate>
</asp:repeater>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;text-align:center">
<legend>
<webdiyer:aspnetpager id="aspnetpager1" runat="server"
cssclass="pages" currentpagebuttonclass="cpb" pagingbuttonspacing="0" firstpagetext="首页"
lastpagetext="尾页" nextpagetext="后页" prevpagetext="前页" alwaysshow="true"
numericbuttoncount="3" pagesize="5"
onpagechanging="aspnetpager1_pagechanging1">
</webdiyer:aspnetpager>
</legend>
</fieldset>
</div>
</form>
</body>
</html>
后台代码:
string strconn = system.configuration.configurationmanager.connectionstrings["mydb"].tostring();
private int id = 0; //保存指定行操作所在的id号
protected void page_load(object sender, eventargs e)
{
if (!page.ispostback)
{
sqlconnection conn = new sqlconnection(strconn);
conn.open();
sqlcommand cmd = new sqlcommand();
cmd.connection = conn;
cmd.commandtext = "select count(*) from tables";
aspnetpager1.alwaysshow = true;
aspnetpager1.pagesize = 5;
aspnetpager1.recordcount = (int)cmd.executescalar();
conn.close();
this.databindtorepeater(0);//将数据绑定到repeater控件上
}
}
private void databindtorepeater(int pageindex)
{
//使用using语句进行数据库连接
using (sqlconnection sqlcon = new sqlconnection(strconn))
{
sqlcon.open(); //打开数据库连接
sqlcommand sqlcom = new sqlcommand(); //创建数据库命令对象
sqlcom.commandtext = "select * from(select id as tableid,产品 as tablename,季度 as tablejidu,销售量 as tablenumber from tables)as temp order by tableid offset "+pageindex*5+" rows fetch next 5 rows only"; //为命令对象指定执行语句
sqlcom.connection = sqlcon; //为命令对象指定连接对象
this.repeater1.datasource = sqlcom.executereader(); //为repeater对象指定数据源
this.repeater1.databind(); //绑定数据源
}
}
protected void repeater1_itemcommand(object source, repeatercommandeventargs e)
{
//获取命令文本，判断发出的命令为何种类型，根据命令类型调用事件
if (e.commandname == "edit") //编辑命令
{
id = int.parse(e.commandargument.tostring()); //获取命令id号
}
else if (e.commandname == "cancel") //取消更新命令
{
id = -1;
}
else if (e.commandname == "delete") //删除行内容命令
{
id = int.parse(e.commandargument.tostring()); //获取删除行的id号
//删除选定的行，并重新指定绑定操作
this.deleterepeater(id);
}
else if (e.commandname == "update") //更新行内容命令
{
//获取更新行的内容和id号
string strtext = ((textbox)e.item.findcontrol("txttablename")).text.trim();
string jidu = ((textbox)e.item.findcontrol("txttablejidu")).text.trim();
string xiaoshou = ((textbox)e.item.findcontrol("txttablenumber")).text.trim();
int intid = int.parse(((label)e.item.findcontrol("label5")).text);
//更新repeater控件的内容
this.updaterepeater(strtext, intid,jidu,xiaoshou);
}
//重新绑定控件上的内容
this.databindtorepeater(0);
}
private void deleterepeater(int intid)
{
using (sqlconnection sqlcon = new sqlconnection(strconn))
{
sqlcon.open(); //打开数据库连接
sqlcommand sqlcom = new sqlcommand(); //创建数据库命令对象
sqlcom.commandtext = "delete from tables where id=@id"; //为命令对象指定执行语句
sqlcom.connection = sqlcon; //为命令对象指定连接对象
//创建参数集合,并向sqlcom中添加参数集合
sqlparameter sqlparam = new sqlparameter("@id", intid);
sqlcom.parameters.add(sqlparam);
sqlcom.executenonquery(); //指定更新语句
}
}
private void updaterepeater(string txttablename, int intid,string jidu,string xiaoshou)
{
using (sqlconnection sqlcon = new sqlconnection(strconn))
{
sqlcon.open(); //打开数据库连接
sqlcommand sqlcom = new sqlcommand(); //创建数据库命令对象
sqlcom.commandtext = "update tables set 产品=@str,季度=@jidu,销售量=@xiaoshou where id=@id"; //为命令对象指定执行语句
sqlcom.connection = sqlcon; //为命令对象指定连接对象
//创建参数集合,并向sqlcom中添加参数集合
sqlparameter[] sqlparam = {
new sqlparameter("@str", txttablename),
new sqlparameter("@id", intid),
new sqlparameter("@jidu",jidu),
new sqlparameter("@xiaoshou",xiaoshou)
};
sqlcom.parameters.addrange(sqlparam);
sqlcom.executenonquery(); //指定更新语句
}
}
protected void repeater1_itemdatabound(object sender, repeateritemeventargs e)
{
//判断repeater控件中的数据是否是绑定的数据源，如果是的话将会验证是否进行了编辑操作
//listitemtype 枚举表示在一个列表控件可以包括，例如 datagrid、 datalist和 repeater 控件的不同项目。
if (e.item.itemtype == listitemtype.item || e.item.itemtype == listitemtype.alternatingitem)
{
//获取绑定的数据源，这里要注意上面使用sqlreader的方法来绑定数据源，所以下面使用的dbdatarecord方法获取的
//如果绑定数据源是datatable类型的使用下面的语句就会报错.
system.data.common.dbdatarecord record = (system.data.common.dbdatarecord)e.item.dataitem;
//datatable类型的数据源验证方式
//system.data.datarowview record = (datarowview)e.item.dataitem;
//判断数据源的id是否等于现在的id，如果相等的话证明现点击了编辑触发了userrepeat_itemcommand事件
if (id == int.parse(record["tableid"].tostring()))
{
((panel)e.item.findcontrol("plitem")).visible = false;
((panel)e.item.findcontrol("pledit")).visible = true;
}
else
{
((panel)e.item.findcontrol("plitem")).visible = true;
((panel)e.item.findcontrol("pledit")).visible = false;
}
}
}
protected void aspnetpager1_pagechanging1(object src, pagechangingeventargs e)
{
aspnetpager1.currentpageindex = e.newpageindex;
databindtorepeater(e.newpageindex-1);
}
}
}
完成!!!