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

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

1. CREATE TABLE [dbo].[ArticleCategory](
2. [Id] [int] IDENTITY(1,1) NOT NULL,
3. [Title] [varchar](128) NOT NULL,
4. [ParentId] [int] NOT NULL,
5. [ClassList] [varchar](128) NULL,
6. [ClassLayer] [int] NULL,
7. [Sort] [int] NOT NULL,
8. [ImageUrl] [varchar](128) NULL,
9. [SeoTitle] [varchar](128) NULL,
10. [SeoKeywords] [varchar](256) NULL,
11. [SeoDescription] [varchar](512) NULL,
12. [IsDeleted] [bit] NOT NULL,
13. CONSTRAINT [PK_ARTICLECATEGORY] PRIMARY KEY NONCLUSTERED
14. (
15. [Id] ASC
16. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
17. ) ON [PRIMARY]
18. GO

复制代码

 
（2）.文章类别的视图

（2.1）Create视图

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

复制代码

 
（2.2）Edit视图

1. @{ ViewData["Title"] = "编辑菜单"; }
2. @model ArticleCategory
3. <form action="/ArticleCategory/Edit" method="post">
4. @Html.AntiForgeryToken()
6. <label asp-for="ParentId">父菜单</label>
8. <input type="text" asp-for="ParentId" name="ParentId" />
9. <input type="hidden" asp-for="Id" />
13. <label asp-for="Title">类别名成</label>
15. <input type="text" asp-for="Title" name="Title" placeholder="类别名成">
19. <label asp-for="Sort">排序</label>
21. <input type="text" asp-for="Sort" name="Sort" placeholder="排序">
26. <button type="submit">确定</button>
27. <button type="reset">重置</button>
30. </form>

复制代码

 
（2.3）Index列表视图

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

1. @using Humanizer;
2. @using RjWebCms.Db;
3. @using RjWebCms.Models.Articles;
4. @using RjWebCms.Common;
5. @model PaginatedList<ArticleCategoryView>
6. @{
7. ViewData["Title"] = "文章类别列表";
8. }
9. @section Scripts{
12. }
14. @ViewData["Title"]
15. @Html.AntiForgeryToken()
16. <form asp-action="Index" method="get">
17. <table>
18. <tr><td><a asp-controller="ArticleCategory" asp-action="Create">添加</a></td></tr>
19. <tr>
20. <td>查询关键词:<input type="text" name="SearchString" value="@ViewData["CurrentFilter"]" /></td>
21. <td><input type="submit" value="查询" /></td>
22. <td><a asp-action="Index">Back</a></td>
23. <td><a asp-action="DeleteAll">批量删除</a></td>
24. </tr>
25. </table>
26. </form>
27. <table class="table table-hover">
28. <thead>
29. <tr>
30. <td>✔</td>
31. <td><a asp-action="Index" asp-route-sortOrder="@ViewData["NameSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">类别名称</a></td>
32. <td>父类名称</td>
33. <td><a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">序号</a></td>
34. <td>操作</td>
35. </tr>
36. @foreach (var item in Model)
37. {
38. if (item.ParentId == 0)
39. {
40. <tr>
41. <td><input type="checkbox" class="done-checkbox" name="chk_ids" value="@item.Id"></td>
42. <td>@item.Title</td>
43. <td>\</td>
44. <td>@item.Sort</td>
45. <td>
46. <a asp-action="Create" asp-route-id="@item.Id">Add</a>
47. <a asp-action="Edit" asp-route-id="@item.Id">Edit</a>
48. <a asp-action="Delete" asp-route-id="@item.Id">Delete</a>
49. </td>
50. </tr>
51. }
52. else
53. {
54. <tr>
55. <td><input type="checkbox" class="done-checkbox" name="chk_ids" value="@item.Id"></td>
56. <td>@item.Title</td>
57. @*<td>@Html.Action("GetParentName",new { id=item.ParentId})</td>*@
58. <td>@item.ParentName</td>
59. <td>@item.Sort</td>
60. <td>
61. <a asp-action="Create" asp-route-id="@item.Id">Add</a>
62. <a asp-action="Edit" asp-route-id="@item.Id">Edit</a>
63. <a asp-action="Delete" asp-route-id="@item.Id">Delete</a>
64. </td>
65. </tr>
66. }
67. }
68. </thead>
69. </table>
70. @{
71. var prevDisabled = !Model.HasPreviousPage ? "disabled" : "";
72. var nextDisabled = !Model.HasNextPage ? "disabled" : ""; ;
73. }
74. <a asp-action="Index"
75. asp-route-sortOrder="@ViewData["CurrentSort"]"
76. asp-route-pageNumber="@(Model.PageIndex - 1)"
77. asp-route-currentFilter="@ViewData["CurrentFilter"]"
78. class="btn btn-default @prevDisabled">
79. 上一页
80. </a>
81. <a asp-action="Index"
82. asp-route-sortOrder="@ViewData["CurrentSort"]"
83. asp-route-pageNumber="@(Model.PageIndex + 1)"
84. asp-route-currentFilter="@ViewData["CurrentFilter"]"
85. class="btn btn-default @nextDisabled">
86. 下一页
87. </a>

复制代码

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

（3）.Model对象的编码实现。

（3.1）表的直接映射对象ArticleCategory.cs

1. /// <summary>
3. /// 文章类别
5. /// </summary>
7. public class ArticleCategory
9. {
11. /// <summary>
13. /// 主键
15. /// </summary>
17. [Key]
19. public Int32 Id { get; set; }
21. /// <summary>
23. /// 分类标题
25. /// </summary>
27. [Required]
29. public String Title { get; set; }
31. /// <summary>
33. /// 父分类ID
35. /// </summary>
37. [Required]
39. public Int32 ParentId { get; set; }
41. /// <summary>
43. /// 类别ID列表(逗号分隔开)
45. /// </summary>
47. public String ClassList { get; set; }
49. /// <summary>
51. /// 类别深度
53. /// </summary>
55. public Int32? ClassLayer { get; set; }
57. /// <summary>
59. /// 排序
61. /// </summary>
63. [Required]
65. public Int32 Sort { get; set; }
67. /// <summary>
69. /// 分类图标
71. /// </summary>
73. public String ImageUrl { get; set; }
75. /// <summary>
77. /// 分类SEO标题
79. /// </summary>
81. public String SeoTitle { get; set; }
83. /// <summary>
85. /// 分类SEO关键字
87. /// </summary>
89. public String SeoKeywords { get; set; }
91. /// <summary>
93. /// 分类SEO描述
95. /// </summary>
97. public String SeoDescription { get; set; }
99. /// <summary>
101. /// 是否删除
103. /// </summary>
105. [Required]
107. public Boolean IsDeleted { get; set; }
109. }
111. （3.2）. ArticleCategoryView.cs 也就是文章类别列表页的ViewModel代码
112. public class ArticleCategoryView
113. {
114. public int Id { get; set; }
115. public string Title { get; set; }
116. public int ParentId { get; set; }
117. public string ParentName { get; set; }
118. public int Sort { get; set; }
119. }
121. （4）.Controller的编码实现
122. public class ArticleCategoryController : Controller
123. {
124. private readonly IArticleCategoryService _articleCategoryService;
125. private readonly AppDbContext _appDbContext;
126. public ArticleCategoryController(IArticleCategoryService articleCategoryService, AppDbContext appDbContext)
127. {
128. _appDbContext = appDbContext;
129. _articleCategoryService = articleCategoryService;
130. }
132. public async Task<IActionResult> Index(string sortOrder, string currentFilter, string searchString, int? pageNumber)
133. {
134. ViewData["CurrentSort"] = sortOrder;
135. ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
136. ViewData["DateSortParm"] = sortOrder == "sort" ? "sort_desc" : "sort";
137. if (searchString != null)
138. {
139. pageNumber = 1;
140. }
141. else
142. {
143. searchString = currentFilter;
144. }
146. ViewData["CurrentFilter"] = searchString;
147. var articleCategories = from s in _appDbContext.ArticleCategory.OrderBy(s=>s.Id)
148. join p in _appDbContext.ArticleCategory on s.ParentId equals p.Id into T1
149. from m in T1.DefaultIfEmpty()
150. select new ArticleCategoryView
151. {
152. Id = s.Id,
153. Title = s.Title,
154. ParentId = s.ParentId,
155. ParentName = m.Title,
156. Sort = s.Sort
157. };
158. if (!string.IsNullOrEmpty(searchString))
159. {
160. articleCategories = articleCategories.Where(s => s.Title.Contains(searchString));
161. }
162. switch (sortOrder)
163. {
164. case "name_desc":
165. articleCategories = articleCategories.OrderByDescending(s => s.Title);
166. break;
167. case "sort":
168. articleCategories = articleCategories.OrderBy(s => s.Sort);
169. break;
170. case "sort_desc":
171. articleCategories = articleCategories.OrderByDescending(s => s.Sort);
172. break;
173. default:
174. articleCategories = articleCategories.OrderBy(s => s.Title);
175. break;
176. }
178. int pageSize = 4;
179. return View(await PaginatedList<ArticleCategoryView>.CreateAsync(articleCategories.AsNoTracking(), pageNumber ?? 1, pageSize));
180. }
182. [HttpGet]
183. public async Task<IActionResult> Create(int id)
184. {
185. if (id != 0)
186. {
187. var articleCategory = await _articleCategoryService.FindArticleCategoryAsync(id);
188. if (articleCategory != null)
189. {
190. ViewData["DisplayName"] = articleCategory.Title;
191. ViewData["ParentId"] = articleCategory.Id; //增加子目录时，以id作为子目录的parentid
192. }
193. }
194. else
195. {
196. ViewData["DisplayName"] = "顶级根目录";
197. ViewData["ParentId"] = 0;
198. }
199. return View();
200. }
202. [HttpPost]
203. [ValidateAntiForgeryToken]
204. public async Task<IActionResult> Create(int id, ArticleCategory articleCategory)
205. {
206. //去掉对字段IsSystem的验证,IsSystem在数据库是bool类型,而前端是0和1，ModelState的验证总是报false，所以去掉对其验证
207. //ModelState.Remove("IsSystem");//在View端已经解决了了bool类型，那么此行代码可以不用
208. if (id != 0)
209. articleCategory.ParentId = id;
210. if (ModelState.IsValid)
211. {
212. var successful = await _articleCategoryService.AddArticleCategoryAysnc(articleCategory);
213. if (successful)
214. return RedirectToAction("Index");
215. else
216. return BadRequest("失败");
217. }
218. return View(articleCategory);
219. }<br>
220. [HttpGet]
221. public async Task<IActionResult> Edit(int id)
222. {
223. if (string.IsNullOrEmpty(id.ToString()))
224. return NotFound();
225. var articleCategory = await _articleCategoryService.FindArticleCategoryAsync(id);
226. if (articleCategory == null)
227. return NotFound();
228. return View(articleCategory);
229. }
231. [HttpPost]
232. [ValidateAntiForgeryToken]
233. public async Task<IActionResult> Edit(int id, ArticleCategory articleCategory)
234. {
235. if (id != articleCategory.Id)
236. {
237. return NotFound();
238. }
240. if (ModelState.IsValid)
241. {
242. try
243. {
244. var result = await _articleCategoryService.UpdateArticleCategoryAsync(id, articleCategory);
245. }
246. catch (Exception ex)
247. {
248. return BadRequest("编辑失败");
249. }
250. return RedirectToAction("Index");
251. }
252. return View(articleCategory);
253. }
254. [HttpGet]
256. public async Task<IActionResult> Delete(int id)
257. {
258. var result = await _articleCategoryService.DeleteArticleCategoryAsync(id);
259. if (result)
260. return RedirectToAction("Index");
261. else
262. return BadRequest("删除失败");
263. }
265. [HttpPost]
266. [ValidateAntiForgeryToken]
267. public async Task<IActionResult> DeleteAll(string[] ids)
268. {
269. int countSuccessDel = 0;//记录删除成功的数据条数
270. int countFailedDel = 0;//记录删除成功的数据条数
271. foreach (string id in ids)
272. {
273. if (await _articleCategoryService.DeleteArticleCategoryAsync(int.Parse(id)))
274. countSuccessDel++;
275. else
276. countFailedDel++;
277. }
279. if (countSuccessDel > 0)
280. return RedirectToAction("Index");
281. else
282. return BadRequest("删除失败");
283. }
285. public async Task<IActionResult> Details(int id)
286. {
287. if (string.IsNullOrEmpty(id.ToString()))
288. return NotFound();
289. var articleCategory = await _articleCategoryService.FindArticleCategoryAsync(id);
290. if (articleCategory == null)
291. return NotFound();
292. return View(articleCategory);
293. }
295. [HttpGet]
296. public async Task<IActionResult> GetCategory()
297. {
298. var items = await _articleCategoryService.GetArticleCategory();
299. return View(items);
300. }
301. public IActionResult GetParentName(int id)
302. {
303. var category = _appDbContext.ArticleCategory.Where(x => x.Id == id).FirstOrDefault();
304. if (category != null)
305. return Content(category.Title);
306. else
307. return Content("");
308. }
309. }

复制代码

 
注意，在Index的Action中的我用了个简单的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的名称作为一个新列放在查询表中。

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

（5）.服务层代码实现
（5.1）接口代码

1. public interface IArticleCategoryService
2. {
3. Task<ArticleCategory[]> GetArticleCategory();
4. Task<ArticleCategory[]> GetArticleCategoryByParentId(int pId);
5. Task<ArticleCategory> FindArticleCategoryAsync(int Id);
6. Task<bool> AddArticleCategoryAysnc(ArticleCategory menu);
7. Task<bool> UpdateArticleCategoryAsync(int id, ArticleCategory menu);
8. Task<bool> DeleteArticleCategoryAsync(int Id);
9. }

复制代码

 
（5.2）实现代码

1. public class ArticleCategoryService : IArticleCategoryService
2. {
3. private readonly AppDbContext _appDbContext;
4. public ArticleCategoryService(AppDbContext appDbContext)
5. {
6. _appDbContext = appDbContext;
7. }
8. public async Task<bool> AddArticleCategoryAysnc(ArticleCategory articleCategory)
9. {
10. articleCategory.IsDeleted = false;
11. if (articleCategory.ParentId == 0)
12. articleCategory.ClassLayer = 1;
13. else
14. articleCategory.ClassLayer = 2;
15. await _appDbContext.ArticleCategory.AddAsync(articleCategory);
16. var result = await _appDbContext.SaveChangesAsync();
17. return result == 1;
18. }
20. public async Task<bool> DeleteArticleCategoryAsync(int Id)
21. {
22. var articleCategory = await _appDbContext.ArticleCategory.FirstOrDefaultAsync(x => x.Id == Id);
23. if (articleCategory != null)
24. {
25. _appDbContext.ArticleCategory.Remove(articleCategory);
26. }
27. var result = await _appDbContext.SaveChangesAsync();
28. return result == 1; //注意(result==1 如果等式成立，则返回true，说明删除成功)
29. }
31. public async Task<ArticleCategory> FindArticleCategoryAsync(int Id)
32. {
33. var item = await _appDbContext.ArticleCategory.Where(x => x.Id == Id).FirstOrDefaultAsync();
34. return item;
35. }
37. public async Task<ArticleCategory[]> GetArticleCategory()
38. {
39. var items = await _appDbContext.ArticleCategory.Where(x => x.IsDeleted==false).OrderByDescending(x => x.Sort).ToArrayAsync();
40. return items;
41. }
43. public async Task<ArticleCategory[]> GetArticleCategoryByParentId(int pId)
44. {
45. var items = await _appDbContext.ArticleCategory.Where(x => x.ParentId == pId).OrderByDescending(x => x.Sort).ToArrayAsync();
46. return items;
47. }
49. public async Task<bool> UpdateArticleCategoryAsync(int id, ArticleCategory articleCategory)
50. {
51. var oldArticleCategory = await FindArticleCategoryAsync(id); //找出旧对象
52. //将新值赋到旧对象上
53. oldArticleCategory.Title = articleCategory.Title;
54. oldArticleCategory.ParentId = articleCategory.ParentId;
55. oldArticleCategory.Sort = articleCategory.Sort;
56. //对旧对象执行更新
57. _appDbContext.Entry(oldArticleCategory).State = EntityState.Modified;
58. var result = await _appDbContext.SaveChangesAsync();
59. return result == 1;
60. }
61. }

复制代码

 

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！