国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁 > 數據庫 > Access > 正文

在Access中模擬sql server存儲過程翻頁

2024-09-07 19:04:49
字體:
來源:轉載
供稿:網友

sql server中翻頁存儲過程:
Create PROC blog_GetPagedPosts
(
@PageIndex int,
@PageSize int,
@BlogID int=0,
@PostType int=-1,
@CategoryID int=-1,
@Hiding bit =0,
@Count int output

)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound @PageSize 1

Create Table #IDs
(
TempID int IDENTITY (1, 1) NOT NULL,
EntryID int not null
)
Insert into #IDs(EntryID) select DISTINCT [ID] from view_Content where CategoryID=@CategoryID and blogID=@BlogID order by [ID] desc
SELECT vc.*
FROM View_Content vc
INNER JOIN #IDS tmp ON (vc .[ID] = tmp.EntryID)
WHERE tmp.TempID > @PageLowerBound
AND tmp.TempID < @PageUpperBound and vc.Hiding=0
ORDER BY tmp.TempID
SELECT @Count=COUNT(*) FROM #IDS
SELECT @Count=COUNT(*) FROM #IDS
DROP TABLE #IDS
return @Count
GO

在Access中由于不支持存儲過程,不能建立臨時表只能在程序中實現
Access中實現如下,這也是我在myblog Access版中使用的:
public List<DayBook> GetPagedPost(PagedPost p, out int TotalRecords)
{
List<DayBook> list = new List<DayBook>();

using (OleDbConnection conn = GetOleDbConnection())
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("select [ID] from blog_Content as p ");//構造查詢條件
if (p.CategoryID > 0)
{
sql.AppendFormat(",blog_Categories AS c, blog_Links AS l WHERE c.CategoryID=l.CategoryID and (p.ID=l.PostID ) and c.CategoryID={1} and p.BlogID={0} ",p.BlogID, p.CategoryID);
}
else
{
sql.AppendFormat(" where p.blogID={0} ", p.BlogID);
}
if (p.PostType != PostType.Undeclared)
{
sql.AppendFormat(" and p.PostType={0} ", (int)p.PostType);
}
sql.Append(" order by p.[DateUpdated] desc");
// NetDiskContext.Current.Context.Response.Write(sql.ToString());
//NetDiskContext.Current.Context.Response.End();
OleDbCommand MyComm = new OleDbCommand(sql.ToString(), conn);
List<int> IDs = new List<int>(); //獲取主題ID列表
conn.Open();
using (OleDbDataReader dr = MyComm.ExecuteReader())
{
while (dr.Read())
{
IDs.Add((int)dr[0]);

}
}

TotalRecords=IDs.Count;//返回記錄總數
if (TotalRecords < 1)
return list;
int pageLowerBound = p.PageSize * p.PageIndex - p.PageSize;//記錄索引
int pageUpperBound = pageLowerBound p.PageSize ;
StringBuilder sb = new StringBuilder();
if (TotalRecords >= pageLowerBound)
for (int i = pageLowerBound; i < TotalRecords && i < pageUpperBound; i )
{
sb.AppendFormat("{0},", IDs[i]);//構造ID in() 條件,取其中一頁
}
else return list; //如沒有記錄返回空表
if(sb.Length>1)
sb.Remove(sb.Length - 1, 1);//刪除最后一個逗號
MyComm.CommandText = string.Format("SELECT b.* , c.Account as Account FROM blog_Content b, Blog_Config c where b.BlogID=c.BlogID and b.[ID] in ({0}) order by b.dateadded desc", sb.ToString());
using (OleDbDataReader dr = MyComm.ExecuteReader())
{
while (dr.Read())
{
list.Add(DataHelp.LoadDayBook(dr));
}
}
return list;
}
}

轉帖請注明出處..深Q

發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
主站蜘蛛池模板: 肇源县| 安化县| 建瓯市| 建水县| 蓬莱市| 偃师市| 纳雍县| 赤壁市| 会宁县| 焉耆| 大宁县| 九寨沟县| 淳安县| 诸城市| 安义县| 原阳县| 曲阜市| 景东| 夏河县| 疏附县| 张家界市| 大宁县| 安乡县| 海晏县| 灌云县| 车致| 利津县| 祁阳县| 梧州市| 灵武市| 白山市| 开封市| 黄浦区| 东至县| 云霄县| 称多县| 阿克| 博野县| 筠连县| 武陟县| 怀柔区|