public List<CommentData> Query(QueryCommentRequest model, out int total) { using (var conn = this.OpenConnection()) { string sql = @" SELECT @Total=COUNT(1) FROM [Comment] Comment WITH(NOLOCK) JOIN [Article] Article WITH(NOLOCK) ON Comment.ArticleID=Article.ID #strWhere#; ;WITH ids AS ( SELECT Comment.ID,Row_Number() OVER(ORDER BY Comment.ID DESC) AS RowID FROM [Comment] Comment WITH(NOLOCK) JOIN Article Article WITH(NOLOCK) ON Comment.ArticleID=Article.ID #strWhere# ) SELECT A.ID, A.ReplyTo, A.Content,A.Email,A.Nickname,A.PostDate,A.PostIP,A.Status,A.NotifyOnReply,B.ID,B.Title FROM [Comment] A WITH(NOLOCK) JOIN Article B WITH(NOLOCK) ON A.ArticleID=B.ID JOIN ids C ON A.ID=C.ID WHERE C.RowID > @Start AND C.RowID <= @End ORDER BY A.ID DESC "; StringBuilder sb = new StringBuilder(" WHERE 1=1"); if (model.Status.HasValue) { sb.Append(" AND Comment.Status=@Status"); } if (!string.IsNullOrWhiteSpace(model.Keywords)) { model.Keywords = "%" + model.Keywords + "%"; sb.Append(" AND (Comment.Content LIKE @Keywords OR Comment.Email LIKE @Keywords OR Comment.Nickname LIKE @Keywords OR Article.Title LIKE @Keywords)"); } sql = sql.Replace("#strWhere#", sb.ToString()); var para = new DynamicParameters(new { Start = (model.PageIndex - 1) * model.PageSize, End = model.PageSize * model.PageSize, Status = model.Status, Keywords = model.Keywords }); para.Add("@Total", 0, System.Data.DbType.Int32, System.Data.ParameterDirection.InputOutput, 4); var query = conn.Query<CommentData, CommentSource, CommentData>(sql, (cd, cs) => { cd.Source = cs; return cd; }, para); total = para.Get<int>("@Total"); return query.ToList(); } }
public IActionResult Query(QueryCommentRequest model) { if (model == null) { return this.BadRequest(); } int total; var list = this.CommentService.Query(model, out total); var result = PagedOperationResult<CommentData>.SuccessResult(list, total); return this.ApiResponse(result); }