--分页存储过程
if (object_id('pro_page', 'P') is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2, 2;
1.此种方法是先查询出所需要的数据表格再添加序列号No(分页搜索时推荐使用这种)
select T.* from(
select ROW_NUMBER() over(order by XM desc) as No,A.* from ( //将A表加上序列号
select * from RZSH WHERE 1=1 sqlwhere) AS A
) AS T
WHERE T.No>startIndex and T.No<endIndex
2.此种方法是将表格添加序列号再进行查询(此种弊端是sqlwhere搜索后的数据不是从1开始(因为已经在搜索前编过序号了),若查询出的数据是12-15行的,而T.No>1 and T.No<10,则不能显示或显示不全)
select * from (
select * from (
select ROW_NUMBER() over(order by SBRQ DESC) as No,* from RZSH
) as a WHERE 1=1 and XM like '%飞%'
) as T
WHERE T.No>0 and T.No<11
前台页面
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" FirstPageText="首页" LastPageText="尾页"
ShowCustomInfoSection="Left" CustomInfoClass="fengyebox_left" PageSize="10" AlwaysShow="true"
OnPageChanged="AspNetPager1_PageChanged" CustomInfoHTML="数据共%RecordCount%条,当前%CurrentPageIndex%/%PageCount%页"
NextPageText="下一页" PageIndexBoxType="DropDownList" PrevPageText="上一页" ShowPageIndexBox="Always"
SubmitButtonText="Go" TextAfterPageIndexBox="页" TextBeforePageIndexBox="转到" CurrentPageButtonClass="fengye_btn1"
FirstLastButtonsClass="fengye_sxyy" MoreButtonsClass="fengye_btn3" PagingButtonsClass="fengye_btn2"
PrevNextButtonsClass="fengye_sxyy">
</webdiyer:AspNetPager>
后台
//当定数据和分页
public void BindData()
{
string sqlwhere = "";
if (TxtName.Text.Trim() != "")
{
sqlwhere += " and XM like '%" + TxtName.Text.Trim() + "%'";
}
if (TxtAddress.Text.Trim() != "")
{
sqlwhere += " and FYDZ like '%" + TxtAddress.Text.Trim() + "%' or FJH like '%" + TxtAddress.Text.Trim() + "%'";
}
if (TxtDate.Text.Trim() != "")
{
sqlwhere += " and SBRQ>'" + TxtDate.Text.Trim() + "'";
}
//int index = Convert.ToInt32(ViewState["index"].ToString());
int index = AspNetPager1.CurrentPageIndex;
DataTable dt = sbsh.GetView(sqlwhere).Tables[0];
DataSet ds = sbsh.GetViewByPage(sqlwhere, "SBRQ DESC", (index - 1) * 10, (index - 1) * 10 + 11);
AspNetPager1.RecordCount = dt.Rows.Count;
AspNetPager1.PageSize = 10;
Repeater1.DataSource = ds.Tables[0];
Repeater1.DataBind();
for (int i = 0; i < this.Repeater1.Items.Count; i++)
{
Button btn = (Button)this.Repeater1.Items[i].FindControl("BtnSH");
HiddenField hfd = (HiddenField)this.Repeater1.Items[i].FindControl("hfd");
if (hfd.Value == "0230000001")
{
btn.Enabled = false;
}
}
}
//当前页索引改变事件
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
//ViewState["index"] = AspNetPager1.CurrentPageIndex;
BindData();
}
//绑定审核状态
public string BindSHZT(string shzt)
{
DM_DMBManager dmb = new DM_DMBManager();
DM_DMB dmbmodel= dmb.GetModel(shzt);
if (dmbmodel != null)
{
return dmbmodel.DMMC;
}
else
{
return "";
}
}
protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "edit")
{
string date = DateTime.Now.ToShortDateString();
string time = DateTime.Now.ToShortTimeString ();
string datetime = date + " " + time;//审核时间
string sbbh = e.CommandArgument.ToString();
sbsh.UpdateSHZT(sbbh, datetime);
BindData();
}
}
//搜索
protected void BtnSearch_Click(object sender, EventArgs e)
{
AspNetPager1.CurrentPageIndex = 1;
BindData();
}