sqlserver中的存儲過程完整代碼
/*****************************************************************
* 存儲過程名: getcustomersdatapage
* 過程描述: 通用大數(shù)據(jù)集分頁
* 傳入?yún)?shù)：
* 傳出參數(shù)：
* 修改記錄
* 姓名 日期 修改類型
* nicklee 2005－1－17 新建
*
*
*
*
*
******************************************************************/
-- 獲取指定頁的數(shù)據(jù)
create procedure [getcustomersdatapage]
@pageindex int, --頁面索引,從datagrid中獲取
@pagesize int, --頁面顯示數(shù)量，從datagrid中獲取
@recordcount int out, --返回記錄總數(shù)
@pagecount int out, --返回分頁后頁數(shù)
@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)
--設(shè)置統(tǒng)計查詢語句
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
--執(zhí)行并返回總數(shù)
exec sp_executesql @countselect,n'@countrecord int output',@recordcount output
set @pagecount = ceiling(@recordcount * 1.0 / @pagesize)
set nocount on
declare @sqlstr nvarchar(3000)
--實際總共的頁碼小于當(dāng)前頁碼 或者 最大頁碼
if @pagecount>=0
--如果分頁后頁數(shù)大于0
begin
if @pagecount<[email protected] and @pagecount>0 --如果實際總共的頁數(shù)小于datagrid索引的頁數(shù)
--or @pagecount=1
begin
--設(shè)置為最后一頁
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 --否則執(zhí)行
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中調(diào)用方法
#region 調(diào)用函數(shù)
//綁定數(shù)據(jù)
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 '定單數(shù)',torder.realquantity as '實際出貨數(shù)',torder.reqtime as '要求出貨時間',torder.reptime as '出貨時間',tmaterial.matname as '材料',tmaterial.colname as '顏色',torder.leavequantity as '未出貨數(shù)',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>
/// 控制導(dǎo)航按鈕或數(shù)字的狀態(tài)
/// </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 )//當(dāng)前為第一頁
{
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 )//當(dāng)前為最后頁
{
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
重點在數(shù)據(jù)對datagrid綁定前進(jìn)行判定
if(pageindex>=pagecount&&pagecount>0)
{
pageindex=pagecount-1;
}
else if(pageindex>=pagecount&&pagecount==0)
{
pageindex=0;
}
