sqlserver中翻頁存儲過程:
CreatePROCblog_GetPagedPosts
(
@PageIndexint,
@PageSizeint,
@BlogIDint=0,
@PostTypeint=-1,
@CategoryIDint=-1,
@Hidingbit=0,
@Countintoutput
)
as
DECLARE@PageLowerBoundint
DECLARE@PageUpperBoundint
SET@PageLowerBound=@PageSize*@PageIndex-@PageSize
SET@PageUpperBound=@PageLowerBound @PageSize 1
CreateTable#IDs
(
TempIDintIDENTITY(1,1)NOTNULL,
EntryIDintnotnull
)
Insertinto#IDs(EntryID)selectDISTINCT[ID]fromvIEw_ContentwhereCategoryID=@CategoryIDandblogID=@BlogIDorderby[ID]desc
SELECTvc.*
FROMVIEw_Contentvc
INNERJOIN#IDStmpON(vc.[ID]=tmp.EntryID)
WHEREtmp.TempID>@PageLowerBound
ANDtmp.TempID<@PageUpperBoundandvc.Hiding=0
ORDERBYtmp.TempID
SELECT@Count=COUNT(*)FROM#IDS
SELECT@Count=COUNT(*)FROM#IDS
DROPTABLE#IDS
return@Count
GO
在Access中由於不支持存儲過程,不能建立臨時表只能在程序中實現
Access中實現如下,這也是我在myblogAccess版中使用的:
publicList<DayBook>GetPagedPost(PagedPostp,outintTotalRecords)
{
List<DayBook>list=newList<DayBook>();
using(OleDbConnectionconn=GetOleDbConnection())
{
StringBuildersql=newStringBuilder();
sql.AppendFormat("select[ID]fromblog_ContentASP");//構造查詢條件
if(p.CategoryID>0)
{
sql.AppendFormat(",blog_CategorIEsASc,blog_LinksASlWHEREc.CategoryID=l.CategoryIDand(p.ID=l.PostID)andc.CategoryID={1}andp.BlogID={0}",p.BlogID,p.CategoryID);
}
else
{
sql.AppendFormat("wherep.blogID={0}",p.BlogID);
}
if(p.PostType!=PostType.Undeclared)
{
sql.AppendFormat("andp.PostType={0}",(int)p.PostType);
}
sql.Append("orderbyp.[DateUpdated]desc");
//NetDiskContext.Current.Context.Response.Write(sql.ToString());
//NetDiskContext.Current.Context.Response.End();
OleDbCommandMyComm=newOleDbCommand(sql.ToString(),conn);
List<int>IDs=newList<int>();//獲取主題ID列表
conn.Open();
using(OleDbDataReaderdr=MyComm.ExecuteReader())
{
while(dr.Read())
{
IDs.Add((int)dr[0]);
}
}
TotalRecords=IDs.Count;//返回記錄總數
if(TotalRecords<1)
returnlist;
intpageLowerBound=p.PageSize*p.PageIndex-p.PageSize;//記錄索引
intpageUpperBound=pageLowerBound p.PageSize;
StringBuildersb=newStringBuilder();
if(TotalRecords>=pageLowerBound)
for(inti=pageLowerBound;i<TotalRecords&&i<pageUpperBound;i )
{
sb.AppendFormat("{0},",IDs[i]);//構造IDin()條件,取其中一頁
}
elsereturnlist;//如沒有記錄返回空表
if(sb.Length>1)
sb.Remove(sb.Length-1,1);//刪除最後一個逗號
MyComm.CommandText=string.Format("SELECTb.*,c.AccountasAccountFROMblog_Contentb,Blog_Configcwhereb.BlogID=c.BlogIDandb.[ID]in({0})orderbyb.dateaddeddesc",sb.ToString());
using(OleDbDataReaderdr=MyComm.ExecuteReader())
{
while(dr.Read())
{
list.Add(DataHelp.LoadDayBook(dr));
}
}
returnlist;
}
}
