public List <ArticlesModels> findListByPage(ArticlesQueryModels articlesQueryModels)
{
List <ArticlesModels> articlesModelsList = new List <ArticlesModels>();
String sql = "select top {0} t.id,t.title,t.create_date,t.article_types_id,t.ctr from Articles t where t.article_types_id=@ArticleTypesId and t.Id not in (select top {1} t1.Id from Articles t1 where t1.article_types_id=@ArticleTypesId order by t1.create_date desc) order by t.create_date desc";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ArticleTypesId", articlesQueryModels.ArticleTypesId)
};
if (articlesQueryModels.ArticleTypesId == 0)
{
sql = "select top {0} t.id,t.title,t.create_date,t.article_types_id,t.ctr from Articles t where t.Id not in (select top {1} t1.Id from Articles t1 order by t1.create_date desc) order by t.create_date desc";
}
sql = String.Format(sql, articlesQueryModels.PageSize, ((articlesQueryModels.CurrentPage - 1) * articlesQueryModels.PageSize));
SqlDataReader objReader = SqlHelper.getReader(sql, param);
while (objReader.Read())
{
ArticlesModels articles = new ArticlesModels();
articles.Id = Convert.ToInt32(objReader["id"]);
articles.Title = Convert.ToString(objReader["title"]);
articles.CreateDate = Convert.ToString(objReader["create_date"]);
articles.Ctr = Convert.ToInt32(objReader["ctr"]);
articlesModelsList.Add(articles);
}
return(articlesModelsList);
}
/*
* public ActionResult Blog()
* {
*
* int currentPage = Convert.ToInt32(Request.QueryString["currentPage"]);
* UserManager userManager = new UserManager();
* //UserModels queryUserModels = new UserModels();
* //queryUserModels.Id = "028636db-f6b6-4640-9001-341931a799a2";
* //queryUserModels.UserName = "******";
* //ViewBag.UserModelsList = userManager.findUserList(queryUserModels);
* ViewBag.UserModelsList = userManager.findAllUserList();
* ViewBag.Message = "在这里写下我的想法";
* return View();
*
*
* ArticlesModels articlesModels = new ArticlesModels();
* articlesModels.CurrentPage = 0;
* articlesModels.PageSize = 15;
* ArticlesManager articlesManager = new ArticlesManager();
* PageResultModels<ArticlesModels> pageResultModels = articlesManager.findListByPage(articlesModels);
* ViewBag.PageResultModels = pageResultModels;
* ViewBag.Message = "在这里写下我的想法";
* return View();
* }*/
public ActionResult Blog(int?currentPage, int?articleTypesId)
{
ArticlesQueryModels articlesQueryModels = new ArticlesQueryModels();
articlesQueryModels.CurrentPage = currentPage == null ? 1 : Convert.ToInt32(currentPage);
articlesQueryModels.ArticleTypesId = articleTypesId == null ? 0 : Convert.ToInt32(articleTypesId);
articlesQueryModels.PageSize = 15;
ArticlesManager articlesManager = new ArticlesManager();
PageResultModels <ArticlesModels> pageResultModels = articlesManager.findListByPage(articlesQueryModels);
ViewBag.PageResultModels = pageResultModels;
if (articlesQueryModels.ArticleTypesId == 0)
{
ActicleTypeModels acticleTypeModels = new ActicleTypeModels();
acticleTypeModels.Name = "全部";
acticleTypeModels.Id = 0;
ViewBag.ActicleTypeModels = acticleTypeModels;
}
else
{
ActicleTypeManager acticleTypeManager = new ActicleTypeManager();
ViewBag.ActicleTypeModels = acticleTypeManager.findById(articlesQueryModels.ArticleTypesId);
}
ViewBag.Message = "在这里写下我的想法";
return(View());
}
public String uri(int page, ArticlesQueryModels articlesQueryModels)
{
if (articlesQueryModels.ArticleTypesId == 0)
{
return(page.ToString());
}
else
{
return(page.ToString() + "/" + articlesQueryModels.ArticleTypesId);
}
}
public void setPageInfo(PageResultModels <ArticlesModels> pageResultModels, ArticlesQueryModels articlesQueryModels)
{
//用户当前请求页码
int currentPage = pageResultModels.CurrentPage;
//数据库数据总页数
int totalPage = pageResultModels.TotalPage;
//总分页数量
const int totalPageSize = 11;
int center = (totalPageSize / 2) + 1;
int size = totalPageSize;
int i = 0;
if (currentPage > center)
{
int deviation = currentPage - center;
i = deviation;
size = totalPageSize + deviation;
}
if (totalPage < totalPageSize)
{
size = totalPage;
}
else if ((i + totalPageSize) > totalPage)
{
i = totalPage - totalPageSize;
size = i + totalPageSize;
}
List <PageModels> numberList = new List <PageModels>();
for (; i < size; i++)
{
PageModels pageModels = new PageModels();
int page = i + 1;
pageModels.Number = page;
pageModels.Uri = uri(page, articlesQueryModels);
numberList.Add(pageModels);
}
pageResultModels.NumberList = numberList;
pageResultModels.Previous = uri(currentPage == 1 ? 1 : currentPage - 1, articlesQueryModels);
pageResultModels.Next = uri(currentPage == totalPage ? totalPage : currentPage + 1, articlesQueryModels);
pageResultModels.First = uri(1, articlesQueryModels);
pageResultModels.Last = uri(totalPage, articlesQueryModels);
}
public int findListByPageCount(ArticlesQueryModels articlesQueryModels)
{
String sql = "select count(1) from Articles t where t.article_types_id=@ArticleTypesId";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ArticleTypesId", articlesQueryModels.ArticleTypesId)
};
if (articlesQueryModels.ArticleTypesId == 0)
{
sql = "select count(1) from Articles";
}
object obj = SqlHelper.getSingleResult(sql, param);
if (obj == null)
{
return(0);
}
return(Convert.ToInt32(obj));
}
public PageResultModels <ArticlesModels> findListByPage(ArticlesQueryModels articlesQueryModels)
{
List <ArticlesModels> list = articlesService.findListByPage(articlesQueryModels);
int totalNumber = articlesService.findListByPageCount(articlesQueryModels);
int pageSize = articlesQueryModels.PageSize;
int totalPage = totalNumber / pageSize;
if (totalNumber % pageSize != 0)
{
totalPage++;
}
PageResultModels <ArticlesModels> pageResultModels = new PageResultModels <ArticlesModels>();
pageResultModels.PageSize = pageSize;
pageResultModels.CurrentPage = articlesQueryModels.CurrentPage;
pageResultModels.TotalNumber = totalNumber;
pageResultModels.TotalPage = totalPage;
pageResultModels.List = list;
setPageInfo(pageResultModels, articlesQueryModels);
return(pageResultModels);
}
