public PageSearchResult <CommentViewInfo> CommentSearchResult(CommentSearchModel search) { // string sql = string.Format(@"select cm.JokeId,cm.ID as CommentId,cm.Content as Comment,cm.UserID,us.UserName,cm.Floor from T_Comment cm // inner join T_Joke jk on cm.JokeId = jk.ID // inner join T_User us on us.id=cm.UserID where cm.JokeId={0}",search.JokeID); string sql = string.Format(@"declare @@pagenum int ={1}; declare @@pagesize int = {2}; with tmp as( select ROW_NUMBER() OVER(ORDER by cm.ID desc) as Num, cm.JokeId,cm.ID as CommentId,cm.Content as Comment,cm.UserID,us.UserName,cm.Floor from T_Comment cm inner join T_Joke jk on cm.JokeId = jk.ID inner join T_User us on us.id=cm.UserID where cm.JokeId={0}) select JokeId,CommentId,Comment,UserID,UserName,UserName,Floor from tmp where Num>(@@pagenum-1)*@@pagesize and Num<=@@pagenum*@@pagesize; select count(1) from T_Comment with(nolock) where JokeId={0}", search.JokeID, search.Page, search.PageSize); var items = this.jokeDatabase.FetchMultiple <CommentViewInfo, int>(sql, search.Page, search.PageSize); var pageResult = new PageSearchResult <CommentViewInfo>() { Items = items.Item1, Page = search.Page, PageSize = search.PageSize, TotalCount = items.Item2[0] }; return(pageResult); }
public PageSearchResult <T_Comment> CommentManageSearch(CommentManageSearch search) { Sql where = Sql.Builder.Where("1=1").OrderBy("ID Desc"); var page = this.jokeDatabase.Page <T_Comment>(search.Page, search.PageSize, where); var pageResult = new PageSearchResult <T_Comment>() { Items = page.Items, Page = search.Page, PageSize = search.PageSize, TotalCount = (int)page.TotalItems }; return(pageResult); }
public PageSearchResult <T_UserLog> UserLogSearch(UserLogSearchModel search) { Sql where = Sql.Builder.Where("1=1").OrderBy("ID DESC"); var pageResult = this.jokeDatabase.Page <T_UserLog>(search.Page, search.PageSize, where); PageSearchResult <T_UserLog> pageViewResult = new PageSearchResult <T_UserLog>() { Items = pageResult.Items, Page = (int)pageResult.CurrentPage, PageSize = (int)pageResult.ItemsPerPage, TotalCount = (int)pageResult.TotalItems }; return(pageViewResult); }
public PageSearchResult <T_UserLog> UserLogSearch(UserLogSearchModel search) { var data = db.UserLogs.OrderBy(x => x.ID).Where(x => 1 == 1); var pageResult = data.ToList(); PageSearchResult <T_UserLog> pageViewResult = new PageSearchResult <T_UserLog>() { Items = pageResult, Page = search.Page, PageSize = search.PageSize, TotalCount = pageResult.Count }; return(pageViewResult); }
public PageSearchResult <JokePostInfo> UserJokesSearch(UserJokesSearchModel search) { StringBuilder where = new StringBuilder(); if (search.UserId != null) { where.AppendFormat(" and j.postid={0}", search.UserId); } if (search.JokeState != null) { where.AppendFormat(" and j.State={0}", search.JokeState); } if (search.JokeType != null) { where.AppendFormat(" and j.Type={0}", search.JokeType); } string sql = string.Format(@"declare @@pagenum int=@0; declare @@pagesize int = @1; with tmp as ( SELECT ROW_NUMBER() over(order by j.ID DESC) as Num, j.ID as JokeId,j.Title,j.Content,j.LikeCount,j.HateCount,u.NikeName,u.ID as UserId,j.Type as JokeType,j.AddDate as PostDate,u.UserName,j.State as JokeState,c.Name as Category,c.Pinyin as CategoryPinyin from T_Joke j with(NOLOCK) inner join T_User u on u.ID = j.PostID inner join T_Category c on c.ID = j.Category where 1=1 {0} ) SELECT JokeId,Title,Content,LikeCount,HateCount,NikeName,UserId,JokeType,PostDate,UserName,JokeState,CategoryPinyin,Category from tmp where Num>(@@pagenum-1)*@@pagesize and Num<=@@pagenum*@@pagesize; select COUNT(1) from T_Joke j where 1=1 {0}", where.ToString()); var items = this.jokeDatabase.FetchMultiple <JokePostInfo, int>(sql, search.Page, search.PageSize); PageSearchResult <JokePostInfo> pageResult = new PageSearchResult <JokePostInfo>() { Items = items.Item1, Page = search.Page, PageSize = search.PageSize, TotalCount = items.Item2[0] }; return(pageResult); }