三层+存储过程实现分页示例代码
程序员文章站
2024-03-02 08:44:28
前台设计： 复制代码 代码如下: <%@ page language="c#" autoeventwireup="true" codebehind="paging.a...
前台设计：
<%@ page language="c#" autoeventwireup="true" codebehind="paging.aspx.cs" inherits="五二一练习.paging" %>
<!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>
<script src="js/jquery1.7.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
$('#txtpagination').focus(function () {
$(this).val("");
})
})
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:gridview id="gridview1" runat="server" autogeneratecolumns="false"
height="336px" width="685px">
<columns>
<asp:boundfield datafield="id" headertext="编号" />
<asp:boundfield datafield="newstitle" headertext="标题" />
<asp:boundfield datafield="newscontent" headertext="内容" />
<asp:boundfield datafield="createtime"
dataformatstring="{0:yyyy-mm-dd hh:mm:ss}" headertext="发布时间" />
</columns>
</asp:gridview>
<asp:linkbutton id="btnfirst" runat="server" onclick="btnfirst_click">第一页</asp:linkbutton>
<asp:linkbutton
id="btnpre" runat="server" onclick="btnpre_click">上一页</asp:linkbutton>
<asp:linkbutton id="btnnext"
runat="server" onclick="btnnext_click">下一页</asp:linkbutton>
<asp:linkbutton id="btnlast" runat="server" onclick="btnlast_click">最后一页</asp:linkbutton><asp:textbox
id="txtpagination" runat="server"></asp:textbox>
<asp:linkbutton id="btnskip" runat="server" onclick="btnskip_click">go</asp:linkbutton>
</div>
</form>
</body>
</html>
首先在数据库创建存储过程
create proc usp_role_getdatebypageindex
@pagesize int,
@pageindex int
as
begin
select * from
(
select *,row_number() over(order by role_id) as rownumber from role) as tbl
where tbl.rownumber between (@pagesize*(@pageindex-1)+1) and @pageindex*@pagesize
end
exec usp_role_getdatebypageindex 5,3
在项目中添加bll,dal,dataaccess,model层
在dal中写一个方法：
//自己写的方法，分页获取数据列表
public datatable getlistdatatable(int pagesize, int pageindex)
{
sqlparameter[] parameters = {
new sqlparameter("@pagesize", sqldbtype.int),
new sqlparameter("@pageindex", sqldbtype.int)
};
parameters[0].value = pagesize;
parameters[1].value = pageindex;
return dbhelpersql.runproceduredatatable("usp_role_getdatebypageindex", parameters);
}
在bll中调用getlistdatatable：
public datatable getlistdatatable(int pagesize, int pageindex)
{
return dal.getlistdatatable(pagesize, pageindex);
}
在dbhelper中添加runproceduredatatable方法：
public static datatable runproceduredatatable(string stroreprocname, idataparameter[] parameters)
{
using (sqlconnection connection = new sqlconnection(connectionstring))
{
datatable dt = new datatable();
connection.open();
sqldataadapter sqlda = new sqldataadapter();
sqlda.selectcommand = buildquerycommand(connection, stroreprocname, parameters);
sqlda.fill(dt);
connection.close();
return dt;
}
}
然后在后台调用即可：
using system;
using system.collections.generic;
using system.linq;
using system.web;
using system.web.ui;
using system.web.ui.webcontrols;
using system.data;
namespace 练习
{
public partial class paging : system.web.ui.page
{
int pagesize = 10;
int pageindex = 1;
protected void page_load(object sender, eventargs e)
{
if (!ispostback)
{
viewstate["pageindex"] = 1;
ladadata();
getlistpageindex();
}
}
private void getlistpageindex()
{
bll.t_news1 bnews = new bll.t_news1();
int totalcount = bnews.getrecordcount("");
if (totalcount % pagesize == 0)
{
viewstate["lastpageindex"] = totalcount / pagesize;
}
else
{
viewstate["lastpageindex"] = totalcount / pagesize + 1;
}
}
private void ladadata()
{
bll.t_news1 bnews = new bll.t_news1();
datatable dt = bnews.getlistdatatable(pagesize, convert.toint32(viewstate["pageindex"]));
this.gridview1.datasource = dt;
this.gridview1.databind();
}
//第一页
protected void btnfirst_click(object sender, eventargs e)
{
viewstate["pageindex"] = 1;
ladadata();
}
//上一页
protected void btnpre_click(object sender, eventargs e)
{
int pageindex = convert.toint32(viewstate["pageindex"]);
if (pagesize>1)
{
pageindex--;
viewstate["pageindex"] = pageindex;
ladadata();
}
}
//下一页
protected void btnnext_click(object sender, eventargs e)
{
int pageindex = convert.toint32(viewstate["pageindex"]);
if (pageindex<convert.toint32(viewstate["lastpageindex"]))
{
pageindex++;
viewstate["pageindex"] = pageindex;
ladadata();
}
}
//最后一页
protected void btnlast_click(object sender, eventargs e)
{
viewstate["pageindex"] = viewstate["lastpageindex"];
ladadata();
}
//跳转页面
protected void btnskip_click(object sender, eventargs e)
{
int result;
if (int.tryparse(txtpagination.text, out result) == true)
{
viewstate["pageindex"] = txtpagination.text.trim();
ladadata();
}
else
{
txtpagination.text = "请输入合法的数字";
}
}
}
}
复制代码 代码如下:
<%@ page language="c#" autoeventwireup="true" codebehind="paging.aspx.cs" inherits="五二一练习.paging" %>
<!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>
<script src="js/jquery1.7.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
$('#txtpagination').focus(function () {
$(this).val("");
})
})
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:gridview id="gridview1" runat="server" autogeneratecolumns="false"
height="336px" width="685px">
<columns>
<asp:boundfield datafield="id" headertext="编号" />
<asp:boundfield datafield="newstitle" headertext="标题" />
<asp:boundfield datafield="newscontent" headertext="内容" />
<asp:boundfield datafield="createtime"
dataformatstring="{0:yyyy-mm-dd hh:mm:ss}" headertext="发布时间" />
</columns>
</asp:gridview>
<asp:linkbutton id="btnfirst" runat="server" onclick="btnfirst_click">第一页</asp:linkbutton>
<asp:linkbutton
id="btnpre" runat="server" onclick="btnpre_click">上一页</asp:linkbutton>
<asp:linkbutton id="btnnext"
runat="server" onclick="btnnext_click">下一页</asp:linkbutton>
<asp:linkbutton id="btnlast" runat="server" onclick="btnlast_click">最后一页</asp:linkbutton><asp:textbox
id="txtpagination" runat="server"></asp:textbox>
<asp:linkbutton id="btnskip" runat="server" onclick="btnskip_click">go</asp:linkbutton>
</div>
</form>
</body>
</html>
首先在数据库创建存储过程
复制代码 代码如下:
create proc usp_role_getdatebypageindex
@pagesize int,
@pageindex int
as
begin
select * from
(
select *,row_number() over(order by role_id) as rownumber from role) as tbl
where tbl.rownumber between (@pagesize*(@pageindex-1)+1) and @pageindex*@pagesize
end
exec usp_role_getdatebypageindex 5,3
在项目中添加bll,dal,dataaccess,model层
在dal中写一个方法：
复制代码 代码如下:
//自己写的方法，分页获取数据列表
public datatable getlistdatatable(int pagesize, int pageindex)
{
sqlparameter[] parameters = {
new sqlparameter("@pagesize", sqldbtype.int),
new sqlparameter("@pageindex", sqldbtype.int)
};
parameters[0].value = pagesize;
parameters[1].value = pageindex;
return dbhelpersql.runproceduredatatable("usp_role_getdatebypageindex", parameters);
}
在bll中调用getlistdatatable：
public datatable getlistdatatable(int pagesize, int pageindex)
{
return dal.getlistdatatable(pagesize, pageindex);
}
在dbhelper中添加runproceduredatatable方法：
public static datatable runproceduredatatable(string stroreprocname, idataparameter[] parameters)
{
using (sqlconnection connection = new sqlconnection(connectionstring))
{
datatable dt = new datatable();
connection.open();
sqldataadapter sqlda = new sqldataadapter();
sqlda.selectcommand = buildquerycommand(connection, stroreprocname, parameters);
sqlda.fill(dt);
connection.close();
return dt;
}
}
然后在后台调用即可：
复制代码 代码如下:
using system;
using system.collections.generic;
using system.linq;
using system.web;
using system.web.ui;
using system.web.ui.webcontrols;
using system.data;
namespace 练习
{
public partial class paging : system.web.ui.page
{
int pagesize = 10;
int pageindex = 1;
protected void page_load(object sender, eventargs e)
{
if (!ispostback)
{
viewstate["pageindex"] = 1;
ladadata();
getlistpageindex();
}
}
private void getlistpageindex()
{
bll.t_news1 bnews = new bll.t_news1();
int totalcount = bnews.getrecordcount("");
if (totalcount % pagesize == 0)
{
viewstate["lastpageindex"] = totalcount / pagesize;
}
else
{
viewstate["lastpageindex"] = totalcount / pagesize + 1;
}
}
private void ladadata()
{
bll.t_news1 bnews = new bll.t_news1();
datatable dt = bnews.getlistdatatable(pagesize, convert.toint32(viewstate["pageindex"]));
this.gridview1.datasource = dt;
this.gridview1.databind();
}
//第一页
protected void btnfirst_click(object sender, eventargs e)
{
viewstate["pageindex"] = 1;
ladadata();
}
//上一页
protected void btnpre_click(object sender, eventargs e)
{
int pageindex = convert.toint32(viewstate["pageindex"]);
if (pagesize>1)
{
pageindex--;
viewstate["pageindex"] = pageindex;
ladadata();
}
}
//下一页
protected void btnnext_click(object sender, eventargs e)
{
int pageindex = convert.toint32(viewstate["pageindex"]);
if (pageindex<convert.toint32(viewstate["lastpageindex"]))
{
pageindex++;
viewstate["pageindex"] = pageindex;
ladadata();
}
}
//最后一页
protected void btnlast_click(object sender, eventargs e)
{
viewstate["pageindex"] = viewstate["lastpageindex"];
ladadata();
}
//跳转页面
protected void btnskip_click(object sender, eventargs e)
{
int result;
if (int.tryparse(txtpagination.text, out result) == true)
{
viewstate["pageindex"] = txtpagination.text.trim();
ladadata();
}
else
{
txtpagination.text = "请输入合法的数字";
}
}
}
}