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);
}