IDC资讯中心
sqlserver中的存储过程完整代码
/*****************************************************************
* 存储过程名: getcustomersdatapage
* 过程描述: 通用大数据集分页
* 传入参数：
* 传出参数：
* 修改记录
* 姓名 日期 修改类型
* nicklee 2005－1－17 新建
*
*
*
*
*
******************************************************************/
— 获取指定页的数据
create procedure [getcustomersdatapage]
@pageindex int, –页面索引,从datagrid中获取
@pagesize int, –页面显示数量，从datagrid中获取
@recordcount int out, –返回记录总数
@pagecount int out, –返回分页后页数
@strgetfields nvarchar(1000), — 需要查询的列
@tablename nvarchar(500) , –表名称
@id nvarchar(100), –主键,（为表的主键）
@strwhere nvarchar(1000) =, — 查询条件 (注意: 不要加 where)
@sortname nvarchar(50) = asc , –排序方式
@ordername nvarchar(100) –父级查询排序方式
as
declare @countselect nvarchar(2000)
–设置统计查询语句
if len(@strwhere) =0
–如果没有查询条件
begin
set @countselect=nselect @countrecord = count(*) from +@tablename
end
else
–否则
begin
set @countselect=nselect @countrecord = count(*) from +@tablename+ where +@strwhere
end
–执行并返回总数
exec sp_executesql @countselect,n@countrecord int output,@recordcount output
set @pagecount = ceiling(@recordcount * 1.0 / @pagesize)
set nocount on
declare @sqlstr nvarchar(3000)
–实际总共的页码小于当前页码 或者 最大页码
if @pagecount>=0
–如果分页后页数大于0
begin
if @pagecount<=@pageindex and @pagecount>0 –如果实际总共的页数小于datagrid索引的页数
–or @pagecount=1
begin
–设置为最后一页
set @pageindex=@pagecount-1
end
else if @pagecount<=@pageindex and @pagecount=0
begin
set @pageindex=0;
end
end
if @pageindex = 0 or @pagecount <= 1 –如果为第一页
begin
if len(@strwhere) =0
begin
set @sqlstr =nselect top +str( @pagesize )+@strgetfields+ from +@tablename+ order by +@ordername+@sortname
end
else
begin
set @sqlstr =nselect top +str( @pagesize )+@strgetfields+ from +@tablename+ where +@strwhere+ order by +@ordername+@sortname
end
end
else if @pageindex = @pagecount – 1 –如果为最后一页
begin
if len(@strwhere) =0
begin
set @sqlstr =n select +@strgetfields+ from +@tablename+ where +@id+ not in ( select top +str(/*@recordcount – */@pagesize * @pageindex )+@id+ from +@tablename+order by +@ordername+@sortname+ ) order by +@ordername+@sortname
end
else
begin
set @sqlstr =n select +@strgetfields+ from +@tablename+ where +@id+ not in ( select top +str(/*@recordcount – */ @pagesize * @pageindex )+@id+ from +@tablename+ where +@strwhere+order by +@ordername+@sortname+ ) and +@strwhere+ order by +@ordername+@sortname
end
end
else –否则执行
begin
if len(@strwhere) =0
begin
set @sqlstr =n select top +str( @pagesize )+@strgetfields+ from +@tablename+ where +@id+ not in ( select top +str( /*@recordcount – */@pagesize * @pageindex )+@id+ from +@tablename+ order by +@ordername+@sortname+ ) order by +@ordername+@sortname
end
else
begin
set @sqlstr =n select top +str( @pagesize )+@strgetfields+ from +@tablename+ where +@id+ not in (select top +str(/*@recordcount – */ @pagesize * @pageindex )+@id+ from +@tablename+ where +@strwhere+ order by +@ordername+@sortname+ )and +@strwhere+order by +@ordername+@sortname
end
end
exec (@sqlstr)
set nocount off
go
在asp.net中调用方法
#region 调用函数
//绑定数据
private void datagriddatabind()
{
dataset ds = getcustomersdata(pageindex,pagesize,ref recordcount,ref pagecount);
datagrid1.virtualitemcount = recordcount;
datagrid1.datasource = ds;
datagrid1.databind();
// gridexpand(this.datagrid1,2);
setpagingstate();
}
private dataset getcustomersdata(int pageindex,int pagesize,ref int recordcount,ref int pagecount)
{
datafill.constring=system.configuration.configurationsettings.appsettings["sqlconnectionstring"];
datafill.sqlclientdataset("getcustomersdatapage");
system.data.sqlclient.sqldataadapter comm=datafill.mysqladapter;
comm.selectcommand.parameters.add(new sqlparameter("@pageindex",sqldbtype.int));
comm.selectcommand.parameters[0].value = pageindex;
comm.selectcommand.parameters.add(new sqlparameter("@pagesize",sqldbtype.int));
comm.selectcommand.parameters[1].value = pagesize;
comm.selectcommand.parameters.add(new sqlparameter("@recordcount",sqldbtype.int));
comm.selectcommand.parameters[2].direction = parameterdirection.output;
comm.selectcommand.parameters.add(new sqlparameter("@pagecount",sqldbtype.int));
comm.selectcommand.parameters[3].direction = parameterdirection.output;
comm.selectcommand.parameters.add(new sqlparameter("@strgetfields",sqldbtype.nvarchar));
comm.selectcommand.parameters[4].value ="torder.ordertime as 下订单时间,torder.facname as 工厂,torder.facordernum as 工厂订单号,torder.quantity as 定单数,torder.realquantity as 实际出货数,torder.reqtime as 要求出货时间,torder.reptime as 出货时间,tmaterial.matname as 材料,tmaterial.colname as 颜色,torder.leavequantity as 未出货数,torder.orderstatic as 全部出货,torder.orderdetail as 备注 ";
/*torder.comname as 公司,torder.comordernum as 公司订单号,*/
comm.selectcommand.parameters.add(new sqlparameter("@tablename",sqldbtype.nvarchar));
comm.selectcommand.parameters[5].value =" torder left join tstock on torder.stoid=tstock.stoid left join tmaterial on tstock.matid=tmaterial.matid ";
comm.selectcommand.parameters.add(new sqlparameter("@id",sqldbtype.nvarchar));
comm.selectcommand.parameters[6].value =" torder.orderid ";
comm.selectcommand.parameters.add(new sqlparameter("@ordername",sqldbtype.nvarchar));
comm.selectcommand.parameters[7].value =" tmaterial.matname ";
comm.selectcommand.parameters.add(new sqlparameter("@strwhere",sqldbtype.nvarchar));
comm.selectcommand.parameters[8].value =" facname="+en1.decyrpt(this.request.querystring["facname"].tostring())+" and facordernum="+en1.decyrpt(this.request.querystring["facnum"].tostring())+" ";
// comm.parameters.add(new sqlparameter("@sortname",sqldbtype.nvarchar));
// comm.parameters[8].value =" desc ";
comm.fill(datafill.mydateset);
recordcount = (int)comm.selectcommand.parameters[2].value;
pagecount = (int)comm.selectcommand.parameters[3].value;
if(pageindex>=pagecount&&pagecount>0)
{
pageindex=pagecount-1;
}
else if(pageindex>=pagecount&&pagecount==0)
{
pageindex=0;
}
//
return datafill.mydateset;
}
/// <summary>
/// 控制导航按钮或数字的状态
/// </summary>
public void setpagingstate()
{
if( pagecount <= 1 )//( recordcount <= pagesize )//小于等于一页
{
this.menu1.items[0].enabled = false;
this.menu1.items[1].enabled = false;
this.menu1.items[2].enabled = false;
this.menu1.items[3].enabled = false;
}
else //有多页
{
if( pageindex == 0 )//当前为第一页
{
this.menu1.items[0].enabled = false;
this.menu1.items[1].enabled = false;
this.menu1.items[2].enabled = true;
this.menu1.items[3].enabled = true;
}
else if( pageindex == pagecount – 1 )//当前为最后页
{
this.menu1.items[0].enabled = true;
this.menu1.items[1].enabled = true;
this.menu1.items[2].enabled = false;
this.menu1.items[3].enabled = false;
}
else //中间页
{
this.menu1.items[0].enabled = true;
this.menu1.items[1].enabled = true;
this.menu1.items[2].enabled = true;
this.menu1.items[3].enabled = true;
}
}
if(recordcount == 0)
{
lab_pagecount.text="第0页 共0页 每页"+pagesize.tostring()+"条 共"+recordcount.tostring()+"条";
}
else
{
lab_pagecount.text="第"+(pageindex + 1).tostring()+"页 共"+pagecount.tostring()+"页 每页"+pagesize.tostring()+"条 共"+recordcount.tostring()+"条";
}
}
#endregion
重点在数据对datagrid绑定前进行判定
if(pageindex>=pagecount&&pagecount>0)
{
pageindex=pagecount-1;
}
else if(pageindex>=pagecount&&pagecount==0)
{
pageindex=0;
}
