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=n'select @countrecord = count(*) from '[email protected]
end
else
--否则
begin
set @countselect=n'select @countrecord = count(*) from '[email protected]+' where '[email protected]
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<[email protected] and @pagecount>0 --如果实际总共的页数小于datagrid索引的页数
--or @pagecount=1
begin
--设置为最后一页
set @[email protected]
end
else if @pagecount<[email protected] and @pagecount=0
begin
set @pageindex=0;
end
end
if @pageindex = 0 or @pagecount <= 1 --如果为第一页
begin
if len(@strwhere) =0
begin
set @sqlstr =n'select top '+str( @pagesize )[email protected]+' from '[email protected]+' order by '[email protected][email protected]
end
else
begin
set @sqlstr =n'select top '+str( @pagesize )[email protected]+' from '[email protected]+' where '[email protected]+' order by '[email protected][email protected]
end
end
else if @pageindex = @pagecount - 1 --如果为最后一页
begin
if len(@strwhere) =0
begin
set @sqlstr =n' select '[email protected]+' from '[email protected]+' where '[email protected]+' not in ( select top '+str(/*@recordcount - */@pagesize * @pageindex )[email protected]+' from '[email protected]+'order by '[email protected][email protected]+' ) order by '[email protected][email protected]
end
else
begin
set @sqlstr =n' select '[email protected]+' from '[email protected]+' where '[email protected]+' not in ( select top '+str(/*@recordcount - */ @pagesize * @pageindex )[email protected]+' from '[email protected]+' where '[email protected]+'order by '[email protected][email protected]+' ) and '[email protected]+' order by '[email protected][email protected]
end
end
else --否则执行
begin
if len(@strwhere) =0
begin
set @sqlstr =n' select top '+str( @pagesize )[email protected]+' from '[email protected]+' where '[email protected]+' not in ( select top '+str( /*@recordcount - */@pagesize * @pageindex )[email protected]+' from '[email protected]+' order by '[email protected][email protected]+' ) order by '[email protected][email protected]
end
else
begin
set @sqlstr =n' select top '+str( @pagesize )[email protected]+' from '[email protected]+' where '[email protected]+' not in (select top '+str(/*@recordcount - */ @pagesize * @pageindex )[email protected]+' from '[email protected]+' where '[email protected]+' order by '[email protected][email protected]+' )and '[email protected]+'order by '[email protected][email protected]
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;
}
