篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

  • 篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)已关闭评论
  • 8 次浏览
  • A+
所属分类:.NET技术
摘要

篇(17)-Asp.Net Core入门实战-文章管理之文章类别的管理如果要做一个CMS系统,那么文章管理算是入门,文章管理附带一个类别管理,用来对文章进行类别区分。所以,本章简单讲一些类别管理,这也是一个数据操作。

篇(17)-Asp.Net Core入门实战-文章管理之文章类别的管理

如果要做一个CMS系统,那么文章管理算是入门,文章管理附带一个类别管理,用来对文章进行类别区分。所以,本章简单讲一些类别管理,这也是一个数据操作。

(1).文章类别Sql表的建立

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

CREATE TABLE [dbo].[ArticleCategory]( [Id] [int] IDENTITY(1,1) NOT NULL, [Title] [varchar](128) NOT NULL, [ParentId] [int] NOT NULL, [ClassList] [varchar](128) NULL, [ClassLayer] [int] NULL, [Sort] [int] NOT NULL, [ImageUrl] [varchar](128) NULL, [SeoTitle] [varchar](128) NULL, [SeoKeywords] [varchar](256) NULL, [SeoDescription] [varchar](512) NULL, [IsDeleted] [bit] NOT NULL, CONSTRAINT [PK_ARTICLECATEGORY] PRIMARY KEY NONCLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

 

(2).文章类别的视图

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

(2.1)Create视图

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

@{ ViewData["Title"] = "新建文章分类"; } @model ArticleCategory <form action="/ArticleCategory/Create" method="post"> @Html.AntiForgeryToken() <div> <label asp-for="ParentId">父类别</label> <div> <label name="Parent_DisplayName" id="Parent_DisplayName">@ViewData["DisplayName"]</label> <input type="hidden" asp-for="ParentId" id="ParentId" name="ParentId" value="@ViewData["ParentId"]" /> </div> </div> <div> <label asp-for="Title">类别名称</label> <div> <input type="text" asp-for="Title" name="Title" placeholder="请输入类别名"> </div> </div> <div> <label asp-for="Sort">排序</label> <div> <input type="text" asp-for="Sort" name="Sort" placeholder="排序"> </div> </div> <div> <div> <button type="submit">确定</button> <button type="reset">重置</button> </div> </div> </form>

 

(2.2)Edit视图

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

@{ ViewData["Title"] = "编辑菜单"; } @model ArticleCategory <form action="/ArticleCategory/Edit" method="post"> @Html.AntiForgeryToken() <div> <label asp-for="ParentId">父菜单</label> <div> <input type="text" asp-for="ParentId" name="ParentId" /> <input type="hidden" asp-for="Id" /> </div> </div> <div> <label asp-for="Title">类别名成</label> <div> <input type="text" asp-for="Title" name="Title" placeholder="类别名成"> </div> </div> <div> <label asp-for="Sort">排序</label> <div> <input type="text" asp-for="Sort" name="Sort" placeholder="排序"> </div> </div> <div> <div> <button type="submit">确定</button> <button type="reset">重置</button> </div> </div> </form>

 

(2.3)Index列表视图

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

单纯的列表在现实父类名称时,显示的还是Id值,看第一步的表结构可以发现,这个字段是ParentId,我们得显示成名称才行。

@using Humanizer; @using RjWebCms.Db; @using RjWebCms.Models.Articles; @using RjWebCms.Common; @model PaginatedList<ArticleCategoryView> @{ ViewData["Title"] = "文章类别列表"; } @section Scripts{ <script src="~/js/jquery-2.1.0.min.js"></script> <script type="text/javascript"> function DelAll() { var ids = document.getElementsByName("#chk_ids"); var arrIds = ""; var n = 0; for (var i = 0; i < ids.length; i++) { if (ids[i].checked == true) { arrIds += ids[i].value + ","; n++; } } if (n == 0) { alert("请选择要删除的信息"); return; } arrIds = arrids.substr(0, arrIds.length - 1); // if (confirm("确定要全部删除选择信息吗")) { $.ajax({ type: "post", url: "/ArticleCategory/DeleteAll", data: { ids: arrIds }, success: function (data, state) { alert('删除成功!'); window.location.href = ""; }, error: function (data, state) { alert('删除失败'); } }); } } </script> } <div class="panel panel-default todo-panel"> <div class="panel-heading">@ViewData["Title"]</div> @Html.AntiForgeryToken() <form asp-action="Index" method="get"> <table> <tr><td><a asp-controller="ArticleCategory" asp-action="Create">添加</a></td></tr> <tr> <td>查询关键词:<input type="text" name="SearchString" value="@ViewData["CurrentFilter"]" /></td> <td><input type="submit" value="查询" /></td> <td><a asp-action="Index">Back</a></td> <td><a asp-action="DeleteAll">批量删除</a></td> </tr> </table> </form> <table class="table table-hover"> <thead> <tr> <td>&#x2714;</td> <td><a asp-action="Index" asp-route-sortOrder="@ViewData["NameSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">类别名称</a></td> <td>父类名称</td> <td><a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">序号</a></td> <td>操作</td> </tr> @foreach (var item in Model) { if (item.ParentId == 0) { <tr> <td><input type="checkbox" class="done-checkbox" name="chk_ids" value="@item.Id"></td> <td>@item.Title</td> <td></td> <td>@item.Sort</td> <td> <a asp-action="Create" asp-route-id="@item.Id">Add</a> <a asp-action="Edit" asp-route-id="@item.Id">Edit</a> <a asp-action="Delete" asp-route-id="@item.Id">Delete</a> </td> </tr> } else { <tr> <td><input type="checkbox" class="done-checkbox" name="chk_ids" value="@item.Id"></td> <td>@item.Title</td> @*<td>@Html.Action("GetParentName",new { id=item.ParentId})</td>*@ <td>@item.ParentName</td> <td>@item.Sort</td> <td> <a asp-action="Create" asp-route-id="@item.Id">Add</a> <a asp-action="Edit" asp-route-id="@item.Id">Edit</a> <a asp-action="Delete" asp-route-id="@item.Id">Delete</a> </td> </tr> } } </thead> </table> @{ var prevDisabled = !Model.HasPreviousPage ? "disabled" : ""; var nextDisabled = !Model.HasNextPage ? "disabled" : ""; ; } <a asp-action="Index" asp-route-sortOrder="@ViewData["CurrentSort"]" asp-route-pageNumber="@(Model.PageIndex - 1)" asp-route-currentFilter="@ViewData["CurrentFilter"]" class="btn btn-default @prevDisabled"> 上一页 </a> <a asp-action="Index" asp-route-sortOrder="@ViewData["CurrentSort"]" asp-route-pageNumber="@(Model.PageIndex + 1)" asp-route-currentFilter="@ViewData["CurrentFilter"]" class="btn btn-default @nextDisabled"> 下一页 </a> <div class="panel-footer add-item-form"> <!-- TODO: Add item form --> </div> </div>

 

所以,需要编写ViewModel是实现,这在前面章节中详细讲过。

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

(3).Model对象的编码实现。

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

(3.1)表的直接映射对象ArticleCategory.cs

/// <summary>  /// 文章类别  /// </summary>  public class ArticleCategory  {  /// <summary>  /// 主键  /// </summary>  [Key]  public Int32 Id { get; set; }  /// <summary>  /// 分类标题  /// </summary>  [Required]  public String Title { get; set; }  /// <summary>  /// 父分类ID  /// </summary>  [Required]  public Int32 ParentId { get; set; }  /// <summary>  /// 类别ID列表(逗号分隔开)  /// </summary>  public String ClassList { get; set; }  /// <summary>  /// 类别深度  /// </summary>  public Int32? ClassLayer { get; set; }  /// <summary>  /// 排序  /// </summary>  [Required]  public Int32 Sort { get; set; }  /// <summary>  /// 分类图标  /// </summary>  public String ImageUrl { get; set; }  /// <summary>  /// 分类SEO标题  /// </summary>  public String SeoTitle { get; set; }  /// <summary>  /// 分类SEO关键字  /// </summary>  public String SeoKeywords { get; set; }  /// <summary>  /// 分类SEO描述  /// </summary>  public String SeoDescription { get; set; }  /// <summary>  /// 是否删除  /// </summary>  [Required]  public Boolean IsDeleted { get; set; }  }  (3.2). ArticleCategoryView.cs 也就是文章类别列表页的ViewModel代码 public class ArticleCategoryView { public int Id { get; set; } public string Title { get; set; } public int ParentId { get; set; } public string ParentName { get; set; } public int Sort { get; set; } }  (4).Controller的编码实现 public class ArticleCategoryController : Controller { private readonly IArticleCategoryService _articleCategoryService; private readonly AppDbContext _appDbContext; public ArticleCategoryController(IArticleCategoryService articleCategoryService, AppDbContext appDbContext) { _appDbContext = appDbContext; _articleCategoryService = articleCategoryService; }  public async Task<IActionResult> Index(string sortOrder, string currentFilter, string searchString, int? pageNumber) { ViewData["CurrentSort"] = sortOrder; ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : ""; ViewData["DateSortParm"] = sortOrder == "sort" ? "sort_desc" : "sort"; if (searchString != null) { pageNumber = 1; } else { searchString = currentFilter; }  ViewData["CurrentFilter"] = searchString; var articleCategories = from s in _appDbContext.ArticleCategory.OrderBy(s=>s.Id) join p in _appDbContext.ArticleCategory on s.ParentId equals p.Id into T1 from m in T1.DefaultIfEmpty() select new ArticleCategoryView { Id = s.Id, Title = s.Title, ParentId = s.ParentId, ParentName = m.Title, Sort = s.Sort }; if (!string.IsNullOrEmpty(searchString)) { articleCategories = articleCategories.Where(s => s.Title.Contains(searchString)); } switch (sortOrder) { case "name_desc": articleCategories = articleCategories.OrderByDescending(s => s.Title); break; case "sort": articleCategories = articleCategories.OrderBy(s => s.Sort); break; case "sort_desc": articleCategories = articleCategories.OrderByDescending(s => s.Sort); break; default: articleCategories = articleCategories.OrderBy(s => s.Title); break; }  int pageSize = 4; return View(await PaginatedList<ArticleCategoryView>.CreateAsync(articleCategories.AsNoTracking(), pageNumber ?? 1, pageSize)); }  [HttpGet] public async Task<IActionResult> Create(int id) { if (id != 0) { var articleCategory = await _articleCategoryService.FindArticleCategoryAsync(id); if (articleCategory != null) { ViewData["DisplayName"] = articleCategory.Title; ViewData["ParentId"] = articleCategory.Id; //增加子目录时,以id作为子目录的parentid } } else { ViewData["DisplayName"] = "顶级根目录"; ViewData["ParentId"] = 0; } return View(); }  [HttpPost] [ValidateAntiForgeryToken] public async Task<IActionResult> Create(int id, ArticleCategory articleCategory) { //去掉对字段IsSystem的验证,IsSystem在数据库是bool类型,而前端是0和1,ModelState的验证总是报false,所以去掉对其验证 //ModelState.Remove("IsSystem");//在View端已经解决了了bool类型,那么此行代码可以不用 if (id != 0) articleCategory.ParentId = id; if (ModelState.IsValid) { var successful = await _articleCategoryService.AddArticleCategoryAysnc(articleCategory); if (successful) return RedirectToAction("Index"); else return BadRequest("失败"); } return View(articleCategory); }
[HttpGet]
public async Task<IActionResult> Edit(int id) { if (string.IsNullOrEmpty(id.ToString())) return NotFound(); var articleCategory = await _articleCategoryService.FindArticleCategoryAsync(id); if (articleCategory == null) return NotFound(); return View(articleCategory); } [HttpPost] [ValidateAntiForgeryToken] public async Task<IActionResult> Edit(int id, ArticleCategory articleCategory) { if (id != articleCategory.Id) { return NotFound(); } if (ModelState.IsValid) { try { var result = await _articleCategoryService.UpdateArticleCategoryAsync(id, articleCategory); } catch (Exception ex) { return BadRequest("编辑失败"); } return RedirectToAction("Index"); } return View(articleCategory); } [HttpGet] public async Task<IActionResult> Delete(int id) { var result = await _articleCategoryService.DeleteArticleCategoryAsync(id); if (result) return RedirectToAction("Index"); else return BadRequest("删除失败"); } [HttpPost] [ValidateAntiForgeryToken] public async Task<IActionResult> DeleteAll(string[] ids) { int countSuccessDel = 0;//记录删除成功的数据条数 int countFailedDel = 0;//记录删除成功的数据条数 foreach (string id in ids) { if (await _articleCategoryService.DeleteArticleCategoryAsync(int.Parse(id))) countSuccessDel++; else countFailedDel++; } if (countSuccessDel > 0) return RedirectToAction("Index"); else return BadRequest("删除失败"); } public async Task<IActionResult> Details(int id) { if (string.IsNullOrEmpty(id.ToString())) return NotFound(); var articleCategory = await _articleCategoryService.FindArticleCategoryAsync(id); if (articleCategory == null) return NotFound(); return View(articleCategory); } [HttpGet] public async Task<IActionResult> GetCategory() { var items = await _articleCategoryService.GetArticleCategory(); return View(items); } public IActionResult GetParentName(int id) { var category = _appDbContext.ArticleCategory.Where(x => x.Id == id).FirstOrDefault(); if (category != null) return Content(category.Title); else return Content(""); } }

 

注意,在Index的Action中的我用了个简单的Linq关联查询

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

因为,父Id与Id都是存在一张表中,关联子查询在使用时要用到:from m in T1.DefaultIfEmpty() 这一句,可以将空也计算在内,否则,会排除掉最前面2行(下图),我的本意sql语句是:

Select a.Id,a.Title,a.parentId,b.Title as ParentName,a.sort from ArticleCategory a

Left join ArticleCategory b on a.ParentId = b.Id ,这样可以将父Id的名称作为一个新列放在查询表中。

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

要注意在关键地方的改变,其他内容与将菜单功能时类似。

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

(5).服务层代码实现

(5.1)接口代码

public interface IArticleCategoryService { Task<ArticleCategory[]> GetArticleCategory(); Task<ArticleCategory[]> GetArticleCategoryByParentId(int pId); Task<ArticleCategory> FindArticleCategoryAsync(int Id); Task<bool> AddArticleCategoryAysnc(ArticleCategory menu); Task<bool> UpdateArticleCategoryAsync(int id, ArticleCategory menu); Task<bool> DeleteArticleCategoryAsync(int Id); }

 

(5.2)实现代码

public class ArticleCategoryService : IArticleCategoryService { private readonly AppDbContext _appDbContext; public ArticleCategoryService(AppDbContext appDbContext) { _appDbContext = appDbContext; } public async Task<bool> AddArticleCategoryAysnc(ArticleCategory articleCategory) { articleCategory.IsDeleted = false; if (articleCategory.ParentId == 0) articleCategory.ClassLayer = 1; else articleCategory.ClassLayer = 2; await _appDbContext.ArticleCategory.AddAsync(articleCategory); var result = await _appDbContext.SaveChangesAsync(); return result == 1; }  public async Task<bool> DeleteArticleCategoryAsync(int Id) { var articleCategory = await _appDbContext.ArticleCategory.FirstOrDefaultAsync(x => x.Id == Id); if (articleCategory != null) { _appDbContext.ArticleCategory.Remove(articleCategory); } var result = await _appDbContext.SaveChangesAsync(); return result == 1; //注意(result==1 如果等式成立,则返回true,说明删除成功) }  public async Task<ArticleCategory> FindArticleCategoryAsync(int Id) { var item = await _appDbContext.ArticleCategory.Where(x => x.Id == Id).FirstOrDefaultAsync(); return item; }  public async Task<ArticleCategory[]> GetArticleCategory() { var items = await _appDbContext.ArticleCategory.Where(x => x.IsDeleted==false).OrderByDescending(x => x.Sort).ToArrayAsync(); return items; }  public async Task<ArticleCategory[]> GetArticleCategoryByParentId(int pId) { var items = await _appDbContext.ArticleCategory.Where(x => x.ParentId == pId).OrderByDescending(x => x.Sort).ToArrayAsync(); return items; }  public async Task<bool> UpdateArticleCategoryAsync(int id, ArticleCategory articleCategory) { var oldArticleCategory = await FindArticleCategoryAsync(id); //找出旧对象 //将新值赋到旧对象上 oldArticleCategory.Title = articleCategory.Title; oldArticleCategory.ParentId = articleCategory.ParentId; oldArticleCategory.Sort = articleCategory.Sort; //对旧对象执行更新 _appDbContext.Entry(oldArticleCategory).State = EntityState.Modified; var result = await _appDbContext.SaveChangesAsync(); return result == 1; } }

 

篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)

至此,文章管理功能编写完成。通篇只有2个主要知识点:linq子查询和ViewModel显示