篇(17)-Asp.Net Core入门实战-文章管理之文章类别管理(Linq子查询)
篇(17)-Asp.Net Core入门实战-文章管理之文章类别的管理如果要做一个CMS系统，那么文章管理算是入门，文章管理附带一个类别管理，用来对文章进行类别区分。所以，本章简单讲一些类别管理，这也是一个数据操作。
（1）.文章类别Sql表的建立
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131909119-813712955.jpg
CREATE TABLE .(
IDENTITY(1,1) NOT NULL,
(128) NOT NULL,
NOT NULL,
(128) NULL,
NULL,
NOT NULL,
(128) NULL,
(128) NULL,
(256) NULL,
(512) NULL,
NOT NULL,
CONSTRAINT PRIMARY KEY NONCLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
) ON
GO
（2）.文章类别的视图
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131907874-1730003467.jpg
（2.1）Create视图
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131908301-965338654.jpg
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131907900-986132650.jpg
@{ ViewData["Title"] = "新建文章分类"; }
@model ArticleCategory
<form action="/ArticleCategory/Create" method="post">
@Html.AntiForgeryToken()
<label asp-for="ParentId">父类别</label>
<label name="Parent_DisplayName" id="Parent_DisplayName">@ViewData["DisplayName"]</label>
<input type="hidden" asp-for="ParentId" id="ParentId" name="ParentId" value="@ViewData["ParentId"]" />
<label asp-for="Title">类别名称</label>
<input type="text" asp-for="Title" name="Title" placeholder="请输入类别名">
<label asp-for="Sort">排序</label>
<input type="text" asp-for="Sort" name="Sort" placeholder="排序">
<button type="submit">确定</button>
<button type="reset">重置</button>
</form>
（2.2）Edit视图
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131907904-1643942161.jpg
@{ ViewData["Title"] = "编辑菜单"; }
@model ArticleCategory
<form action="/ArticleCategory/Edit" method="post">
@Html.AntiForgeryToken()
<label asp-for="ParentId">父菜单</label>
<input type="text" asp-for="ParentId" name="ParentId" />
<input type="hidden" asp-for="Id" />
<label asp-for="Title">类别名成</label>
<input type="text" asp-for="Title" name="Title" placeholder="类别名成">
<label asp-for="Sort">排序</label>
<input type="text" asp-for="Sort" name="Sort" placeholder="排序">
<button type="submit">确定</button>
<button type="reset">重置</button>
</form>
（2.3）Index列表视图
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131908299-800403678.jpg
单纯的列表在现实父类名称时，显示的还是Id值，看第一步的表结构可以发现，这个字段是ParentId，我们得显示成名称才行。
@using Humanizer;
@using RjWebCms.Db;
@using RjWebCms.Models.Articles;
@using RjWebCms.Common;
@model PaginatedList<ArticleCategoryView>
@{
ViewData["Title"] = "文章类别列表";
}
@section Scripts{
}
@ViewData["Title"]
@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>✔</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>
所以，需要编写ViewModel是实现，这在前面章节中详细讲过。
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131908815-625271410.jpg
（3）.Model对象的编码实现。
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131908401-1599832898.jpg
（3.1）表的直接映射对象ArticleCategory.cs
/// <summary>
/// 文章类别
/// </summary>
public class ArticleCategory
{
/// <summary>
/// 主键
/// </summary>
public Int32 Id { get; set; }
/// <summary>
/// 分类标题
/// </summary>
public String Title { get; set; }
/// <summary>
/// 父分类ID
/// </summary>
public Int32 ParentId { get; set; }
/// <summary>
/// 类别ID列表(逗号分隔开)
/// </summary>
public String ClassList { get; set; }
/// <summary>
/// 类别深度
/// </summary>
public Int32? ClassLayer { get; set; }
/// <summary>
/// 排序
/// </summary>
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>
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));
}
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();
}
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);
}<br>
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);
}
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);
}
public async Task<IActionResult> Delete(int id)
{
var result = await _articleCategoryService.DeleteArticleCategoryAsync(id);
if (result)
return RedirectToAction("Index");
else
return BadRequest("删除失败");
}
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);
}
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关联查询
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131909114-729300236.jpg
因为，父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的名称作为一个新列放在查询表中。
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131908980-1756967141.jpg
要注意在关键地方的改变，其他内容与将菜单功能时类似。
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131908048-1002315061.jpg
（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;
}
}
https://img2022.cnblogs.com/blog/39139/202211/39139-20221121131908075-1349551125.jpg
至此，文章管理功能编写完成。通篇只有2个主要知识点：linq子查询和ViewModel显示
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]