/*--用存儲過程實(shí)現(xiàn)的分頁程序
顯示指定表、視圖、查詢結(jié)果的第X頁
對于表中主鍵或標(biāo)識列的情況,直接從原表取數(shù)查詢，其它情況使用臨時表的方法
如果視圖或查詢結(jié)果中有主鍵,不推薦此方法
--鄒建 2003.09--*/
/*--調(diào)用示例
exec p_show ‘地區(qū)資料‘
exec p_show ‘地區(qū)資料‘,5,3,‘地區(qū)編號,地區(qū)名稱,助記碼‘,‘地區(qū)編號‘
--*/
/*
因?yàn)橐櫦巴ㄓ眯?所以對帶排序的查詢語句有一定要求.如果先排序,再出結(jié)果.就是:
exec p_show ‘select top 100 percent * from 地區(qū)資料 order by 地區(qū)名稱‘,5,3,‘地區(qū)編號,地區(qū)名稱,助記碼‘,‘地區(qū)名稱‘
--查詢語句加上:top 100 percent //top時
*/
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[p_show]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)
drop procedure [dbo].[p_show]
GO
CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、視圖名、查詢語句
@PageSize int=10, --每頁的大小(行數(shù))
@PageCurrent int=1, --要顯示的頁
@FdShow nvarchar (4000)=‘‘, --要顯示的字段列表,如果查詢結(jié)果有標(biāo)識字段,需要指定此值,且不包含標(biāo)識字段
@FdOrder nvarchar (1000)=‘‘ --排序字段列表
as
declare @FdName nvarchar(250) --表中的主鍵或表、臨時表中的標(biāo)識列名
,@Id1 varchar(20),@Id2 varchar(20) --開始和結(jié)束的記錄號
,@Obj_ID int --對象ID
--表中有復(fù)合主鍵的處理
declare @strfd nvarchar(2000) --復(fù)合主鍵列表
,@strjoin nvarchar(4000) --連接字段
,@strwhere nvarchar(2000) --查詢條件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,‘‘) when ‘‘ then ‘ *‘ else ‘ ‘+@FdShow end
,@FdOrder=case isnull(@FdOrder,‘‘) when ‘‘ then ‘‘ else ‘ order by ‘+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ‘ ‘+@QueryStr else ‘ (‘+@QueryStr+‘) a‘ end
--如果顯示第一頁，可以直接用top來完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec(‘select top ‘+@Id1+@FdShow+‘ from ‘+@QueryStr+@FdOrder)
return
end
--如果是表,則檢查表中是否有標(biāo)識更或主鍵
if @Obj_ID is not null and objectproperty(@Obj_ID,‘IsTable‘)=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中無標(biāo)識列,則檢查表中是否有主鍵
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype=‘PK‘)
goto lbusetemp --如果表中無主鍵,則用臨時表處理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype=‘PK‘ and parent_obj=@Obj_ID
)))
if @@rowcount>1 --檢查表中的主鍵是否為復(fù)合主鍵
begin
select @strfd=‘‘,@strjoin=‘‘,@strwhere=‘‘
select @strfd=@strfd+‘,[‘+name+‘]‘
,@strjoin=@strjoin+‘ and a.[‘+name+‘]=b.[‘+name+‘]‘
,@strwhere=@strwhere+‘ and b.[‘+name+‘] is null‘
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype=‘PK‘ and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用標(biāo)識列或主鍵為單一字段的處理方法--*/
lbuseidentity:
exec(‘select top ‘+@Id1+@FdShow+‘ from ‘+@QueryStr
+‘ where ‘+@FdName+‘ not in(select top ‘
+@Id2+‘ ‘+@FdName+‘ from ‘+@QueryStr+@FdOrder
+‘)‘+@FdOrder
)
return
/*--表中有復(fù)合主鍵的處理方法--*/
lbusepk:
exec(‘select ‘+@FdShow+‘ from(select top ‘+@Id1+‘ a.* from
(select top 100 percent * from ‘+@QueryStr+@FdOrder+‘) a
left join (select top ‘+@Id2+‘ ‘+@strfd+‘
from ‘+@QueryStr+@FdOrder+‘) b on ‘+@strjoin+‘
where ‘+@strwhere+‘) a‘
)
return
/*--用臨時表處理的方法--*/
lbusetemp:
select @FdName=‘[ID_‘+cast(newid() as varchar(40))+‘]‘
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec(‘select ‘+@FdName+‘=identity(int,0,1),‘+@FdShow+‘
into #tb from‘+@QueryStr+@FdOrder+‘
select ‘+@FdShow+‘ from #tb where ‘+@FdName+‘ between ‘
+@Id1+‘ and ‘+@Id2
)
Sqlserver存儲過程和C#分頁類簡化你的代碼!
在最近的項(xiàng)目中，由于要用到自定義分頁的功能，本人就在網(wǎng)上找了個存儲過程。結(jié)合C#寫了個分頁類。由于本人第一次寫文章。寫得不好，大家不要扔雞蛋。。
下面是存儲過程（sqlserver2000下通過)
--最通用的分頁存儲過程
-- 獲取指定頁的數(shù)據(jù)
CREATE PROCEDURE Pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = ‘*‘, -- 需要返回的列
@fldName varchar(255)=‘‘, -- 排序的字段名
@PageSize int = 10, -- 頁尺寸
@PageIndex int = 1, -- 頁碼
@doCount bit = 0, -- 返回記錄總數(shù), 非 0 值則返回
@OrderType bit = 0, -- 設(shè)置排序類型, 非 0 值則降序
@strWhere varchar(1500) = ‘‘ -- 查詢條件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主語句
declare @strTmp varchar(110) -- 臨時變量
declare @strOrder varchar(400) -- 排序類型
if @doCount != 0
begin
if @strWhere !=‘‘
set @strSQL = ‘select count(*) as Total from [‘+ @tblName +‘] where ‘+ @strWhere
else
set @strSQL = ‘select count(*) as Total from [‘+ @tblName +‘]‘
end
--以上代碼的意思是如果@doCount傳遞過來的不是0，就執(zhí)行總數(shù)統(tǒng)計(jì)。以下的所有代碼都
--是@doCount為0的情況
else
begin
if @OrderType != 0
begin
set @strTmp = ‘<(select min‘
set @strOrder = ‘ order by [‘+ @fldName +‘] desc‘
--如果@OrderType不是0，就執(zhí)行降序，這句很重要！
end
else
begin
set @strTmp = ‘>(select max‘
set @strOrder = ‘ order by [‘+ @fldName +‘] asc‘
end
if @PageIndex = 1
begin
if @strWhere != ‘‘
set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘+ @tblName +‘] where ‘ + @strWhere + ‘ ‘ + @strOrder
else
set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘+ @tblName +‘] ‘+ @strOrder
--如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
end
else
begin
--以下代碼賦予了@strSQL以真正執(zhí)行的SQL代碼
set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘ + @tblName +‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘+ @fldName + ‘])
from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘+ @fldName + ‘] from [‘+ @tblName +‘]‘ + @strOrder + ‘) as tblTmp)‘+ @strOrder
if @strWhere != ‘‘
set @strSQL = ‘select top ‘ + str(@PageSize) +‘ ‘+@strGetFields+ ‘ from [‘+ @tblName +‘] where [‘ + @fldName + ‘]‘ + @strTmp + ‘([‘+ @fldName + ‘]) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [‘+ @fldName + ‘]
from [‘+ @tblName +‘] where ‘ + @strWhere + ‘ ‘ + @strOrder + ‘) as tblTmp) and ‘ + @strWhere + ‘ ‘ + @strOrder
end
end
exec ( @strSQL)
GO
下面是C#的代碼
using System.Data ;
using System.Data.SqlClient ;
using Microsoft.ApplicationBlocks.Data ;
using System.Web ;
using System.Web.UI ;
namespace RssLayer.PageHelper
{
/**//// <summary>
/// 分頁類PagerHelper 的摘要說明。
/// </summary>
public class PagerHelper
{
private string connectionString;
public PagerHelper(string tblname,string sortname,bool docount,string connectionString)
{
this.tblName = tblname;
this.fldName = sortname ;
this.connectionString = connectionString ;
this.docount = docount;
}
public PagerHelper(string tblname,bool docount,
string strGetFields, string fldName,int pagesize,
int pageindex,bool ordertype,string strwhere,string connectionString
)
{
this.tblName = tblname ;
this.docount = docount ;
this.strGetFields = strGetFields ;
this.fldName = fldName;
this.pagesize = pagesize ;
this.pageindex = pageindex;
this.ordertype = ordertype ;
this.strwhere = strwhere ;
this.connectionString = connectionString ;
}
/**//// <summary>
/// 得到記錄集的構(gòu)造函數(shù)
/// </summary>
/// <param name="tblname"></param>
/// <param name="strwhere"></param>
/// <param name="connectionString"></param>
public PagerHelper(string tblname,string strwhere,string connectionString)
{
this.tblName = tblname;
this.strwhere = strwhere ;
this.docount = true;
this.connectionString = connectionString ;
}
private string tblName;
public string TblName
{
get{return tblName;}
set{tblName =value;}
}
private string strGetFields="*";
public string StrGetFields
{
get{return strGetFields ;}
set{strGetFields =value;}
}
private string fldName=string.Empty;
public string FldName
{
get{return fldName ;}
set{fldName =value;}
}
private int pagesize =10;
public int PageSize
{
get{return pagesize ;}
set{pagesize =value;}
}
private int pageindex =1;
public int PageIndex
{
get{return pageindex ;}
set{pageindex =value;}
}
private bool docount=false;
public bool DoCount
{
get{return docount ;}
set{docount =value;}
}
private bool ordertype=false;
public bool OrderType
{
get{return ordertype ;}
set{ordertype =value;}
}
private string strwhere=string.Empty ;
public string StrWhere
{
get{return strwhere ;}
set{strwhere =value;}
}
public IDataReader GetDataReader()
{
if(this.docount)
{
throw new ArgumentException("要返回記錄集，DoCount屬性一定為false");
}
// System.Web.HttpContext.Current.Response.Write(pageindex);
return SqlHelper.ExecuteReader(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}
public DataSet GetDataSet()
{
if(this.docount)
{
throw new ArgumentException("要返回記錄集，DoCount屬性一定為false");
}
return SqlHelper.ExecuteDataset(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}
public int GetCount()
{
if(!this.docount)
{
throw new ArgumentException("要返回總數(shù)統(tǒng)計(jì)，DoCount屬性一定為true");
}
return (int)SqlHelper.ExecuteScalar(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}
}
}
如何調(diào)用？？？
假如我已經(jīng)建立了2個類。一個是FavList數(shù)據(jù)庫實(shí)體類，一個FavListCollection集合類。FavListCollection存儲了FavList實(shí)體類的集合。
我可以這樣寫一個方法。
/**//// <summary>
/// 返回FavList集合,使用存儲過程自定義分頁。
/// </summary>
/// <param name="userid">數(shù)據(jù)庫FavList的字段，用戶ID</param>
/// <param name="strwhere">查找的條件</param>
/// <param name="ordertype">排序，true表示Desc,false表示asc</param>
/// <param name="fldname">排序的字段，只能是一個字段</param>
/// <param name="pagesize">每頁的記錄數(shù)</param>
/// <param name="pageindex">到第幾頁的參數(shù)，由1開始。1表示第一頁，以此類推。</param>
/// <param name="recordcount">總記錄數(shù)。</param>
/// <returns></returns>
public override FavListCollection GetFavListsByUser(int userid, string strwhere,
bool ordertype, string fldname, int pagesize,
int pageindex,out int recordcount
)
{
recordcount = 0;
PagerHelper helper = new PagerHelper("Vfavlist",strwhere,ConnectionString); //VFavList是View
recordcount = helper.GetCount();
PagerHelper helper2 = new PagerHelper("Vfavlist",false," * ",fldname,
pagesize,pageindex,ordertype,strwhere,ConnectionString);
IDataReader dr = helper2.GetDataReader();
FavListCollection list = new FavListCollection();
while(dr.Read())
{
list.Add(PopulateFavList(dr));
}
dr.Close();
return list;
}
DataGrid調(diào)用就不用說了吧。。
關(guān)于該分頁的Bug和局限性
Bug：當(dāng)排序那個字段內(nèi)容相同的時候（例如：按時間來排序，而時間是一樣的話。后面的記錄會顯示不出來。本人測試過)
局限性：排序只能一個字段，不能超過一個
出處：BLOG永不言拜
