.Net Core Web Api_笔记25_api结合EFCore资料库操作part3_产品分类资料的编辑与删除(EF的更新写法怎麽这麽多种!如何观察EF产生的SQL)
我们在上一篇的Show.html
已经完成了资料查询呈现
这里要多出操作(比方像是编辑、删除...)
在Show.html
加上对应client端 jQuery存取呼叫程序
以及扩充操作的相关连结
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<link href="../css/bootstrap/css/bootstrap.min.css" rel="stylesheet" />
<script src="../js/jquery/jquery.min.js"></script>
</head>
<body style="margin:20px;">
<table id="tbProductCategory" class="table table-bordered">
<thead>
<tr>
<td nowrap>产品类别ID</td>
<td nowrap>产品类别名称</td>
<td nowrap>操作</td>
</tr>
</thead>
<tbody>
</tbody>
</table>
<script type="text/javascript">
$(function () {
var tbody = $('#tbProductCategory tbody');
$.ajax({
type: 'get',
url: '/api/pcategory/show',
dataType: 'json',
success: function (result) {
$.each(result, function (n, value) {
var tr_val = "";
tr_val += "<tr><td>" + value.cId + "</td>" +
"<td>" + value.cTitle + "</td>" +
"<td nowrap>" +
"<a href='Edit.html?id=" + value.cId + "'>编辑</a> " +
"<a href='javascript:Del(\"" + value.cId + "\")'>删除</a>" + //传 GUID 当参数 要用双引号包覆 跳脱字元(\")
"</td></tr>";
tbody += tr_val;
});
$('#tbProductCategory').append(tbody);
}
});
});
function Del(id) {
$.ajax({
type: "delete",
url: "/api/pcategory/delete?id=" + id,
dataType: "json",
success: function (result) {
if (result != "0") {
location.href = "Show.html";
}
}
});
}
</script>
</body>
</html>
编辑部分传入对应产品类别ID并进行页面跳转与後续资料回填
删除则针对特定产品类别ID直接呼叫HTTP Delete即可
扩充编辑用的 Action Method
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Net5EFCoreWebApiApp.Data;
using Net5EFCoreWebApiApp.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace Net5EFCoreWebApiApp.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class PCategoryController : ControllerBase
{
//Startup.cs中注册EF服务後，就可在特定控制器藉由.net core预设DI，透过建构子去实践存取ProductDbContext。
private readonly ProductDbContext _dbContext;
public PCategoryController(ProductDbContext dbContext)
{
_dbContext = dbContext;
}
[HttpPost("Add")]
public async Task<ActionResult<int>> AddProductCategory(PCategory pCategory)
{
if (pCategory == null)
return NotFound();
if (string.IsNullOrEmpty(pCategory.CTitle))
return NotFound();
pCategory.CId = Guid.NewGuid();
_dbContext.PCategories.Add(pCategory);
int RowCount = await _dbContext.SaveChangesAsync();
return CreatedAtAction(nameof(AddProductCategory),RowCount);
}
[HttpGet("Show")]
public async Task<ActionResult<List<PCategory>>> ShowProductCategory()
{
var categories = await _dbContext.PCategories.ToListAsync();
return categories;
}
[HttpGet("GetById")]
public async Task<ActionResult<PCategory>> GetSpecificCategoryById(string id)
{
if (string.IsNullOrWhiteSpace(id))
return NotFound();
var category = await _dbContext.PCategories.AsNoTracking()
.FirstOrDefaultAsync(item=>item.CId==new Guid(id));
return category;
}
[HttpPut("Update")]
public async Task<ActionResult<int>> UpdateCategory(PCategory pCategory)
{
if (pCategory == null)
return NotFound();
if (string.IsNullOrEmpty(pCategory.CTitle))
return NotFound();
_dbContext.Entry(pCategory).State = EntityState.Modified;
var count = await _dbContext.SaveChangesAsync();
return count;
}
}
}
Edite.html对应client端 jQuery存取呼叫程序
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<link href="../css/bootstrap/css/bootstrap.min.css" rel="stylesheet" />
<script src="../js/jquery/jquery.min.js"></script>
</head>
<body>
<div style="padding:20px;border:1px solid #0094ff;width:600px;margin:30px;">
<h3>产品分类编辑</h3>
<hr />
<div class="form-horizontal">
<div class="form-group col-4">
<label>产品类别</label>
<input type="text" class="form-control" id="CTitle">
</div>
<div class="form-group">
<div class="col-md-2 col-md-10">
<button type="submit" id="btnSubmit" class="btn btn-primary">Submit</button>
</div>
<div>
<span id="msg" class="bg-danger"></span>
</div>
</div>
</div>
</div>
<script type="text/javascript">
//var query = window.location.search;
//console.log("window.location.search:" + query);
//window.location.search:?id=ffd2823a-f739-4678-98b6-a4d7dfc482fa
//var query0 = window.location.search.substring(0);
//console.log("window.location.search.substring(0):" + query0);
//window.location.search.substring(0):?id=ffd2823a-f739-4678-98b6-a4d7dfc482fa
//var query1 = window.location.search.substring(1);
//console.log("window.location.search.substring(1):" + query1);
//window.location.search.substring(1):id=ffd2823a-f739-4678-98b6-a4d7dfc482fa
function getQueryGetParams(variable) {
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var idxVar = 0; idxVar < vars.length; idxVar++) {
var pair = vars[idxVar].split("=");
if (pair[0] == variable)
return pair[1];
}
return "";
}
$(function () {
var id = getQueryGetParams("id");
$.ajax({
type: "get",
url: "/api/PCategory/getbyid?id=" + id,
dataType: "json",
success: function (result) {
$("#CTitle").val(result.cTitle);
}
});
});
$('#btnSubmit').click(function () {
var id = getQueryGetParams("id");
$.ajax({
type: 'put',
url: "/api/PCategory/update",
dataType: "text",
data: JSON.stringify({
CId: id,
CTitle: $("#CTitle").val()
}),
contentType: 'application/json',
success: function (result) {
if (result == "1") {
$('#msg').text('成功更新');
}
}
});
});
</script>
</body>
</html>
分别为
GET类型的
GetSpecificCategoryById
用来帮我们把资料查询用来後续回填至画面上
和PUT类型的
UpdateCategory
用来呼叫後端进行编辑的更新
运行效果
画面By CategoryID 回填
更改之前
执行编辑更新後
扩充删除用的 Action Method
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Net5EFCoreWebApiApp.Data;
using Net5EFCoreWebApiApp.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace Net5EFCoreWebApiApp.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class PCategoryController : ControllerBase
{
//Startup.cs中注册EF服务後，就可在特定控制器藉由.net core预设DI，透过建构子去实践存取ProductDbContext。
private readonly ProductDbContext _dbContext;
public PCategoryController(ProductDbContext dbContext)
{
_dbContext = dbContext;
}
[HttpPost("Add")]
public async Task<ActionResult<int>> AddProductCategory(PCategory pCategory)
{
if (pCategory == null)
return NotFound();
if (string.IsNullOrEmpty(pCategory.CTitle))
return NotFound();
pCategory.CId = Guid.NewGuid();
_dbContext.PCategories.Add(pCategory);
int RowCount = await _dbContext.SaveChangesAsync();
return CreatedAtAction(nameof(AddProductCategory),RowCount);
}
[HttpGet("Show")]
public async Task<ActionResult<List<PCategory>>> ShowProductCategory()
{
var categories = await _dbContext.PCategories.ToListAsync();
return categories;
}
[HttpGet("GetById")]
public async Task<ActionResult<PCategory>> GetSpecificCategoryById(string id)
{
if (string.IsNullOrWhiteSpace(id))
return NotFound();
var category = await _dbContext.PCategories.AsNoTracking()
.FirstOrDefaultAsync(item=>item.CId==new Guid(id));
return category;
}
[HttpPut("Update")]
public async Task<ActionResult<int>> UpdateCategory(PCategory pCategory)
{
if (pCategory == null)
return NotFound();
if (string.IsNullOrEmpty(pCategory.CTitle))
return NotFound();
_dbContext.Entry(pCategory).State = EntityState.Modified;
var count = await _dbContext.SaveChangesAsync();
return count;
}
[HttpDelete("Delete")]
public ActionResult<int> DeleteCategory(string id)
{
if (string.IsNullOrWhiteSpace(id))
return NotFound();
var category = _dbContext.PCategories.Find(new Guid(id));
if (category == null)
return NotFound();
_dbContext.PCategories.Remove(category);
return _dbContext.SaveChanges();
}
}
}
运行效果
删除这边的API存取我们采用同步的方式确保不会因为entity取到null造成null例外错误
在此会发现Entity Framework的更新写法怎麽跟之前在
EntityFramework Core笔记(2)_CodeFirst配置_搭配MVC应用的开发_编辑、删除
所写的方式不一样
事实上在EntityFramework中有很多种更新的写法
在之前文章的.net core MVC这边的更新
我们采用的是直接
context.Update(实体);
context.SaveChanges();//或 context.SaveChangesAsync();
而在.net core WebAPI这边
我们采用的是
context.Entry(实体).State = EntityState.Modified;
context.SaveChanges();//或 context.SaveChangesAsync();
若想观察 EF Core 产生的 SQL 指令
我们可以到Startup.cs
引入using Microsoft.Extensions.Logging;
并调整ConfigureServices
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<MyTestDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("MyTestDbConn"))
.UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
);
services.AddControllersWithViews();
}
再次执行编辑就可以观察的到当执行SaveChange时候EF 底层实际执行的 SQL script
在输出视窗中记得调整为你的.net core应用才看的到
预设是没有带实际参数值的版本
这里只要启动敏感资料log机制即可
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<MyTestDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("MyTestDbConn"))
.UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
.EnableSensitiveDataLogging()
);
services.AddControllersWithViews();
}
在MVC这里用的
context.Update(实体);
对应SQL语句
Executed DbCommand (4ms) [Parameters=[@p3='abffc939-0413-4e71-9565-7c48de43f8db', @p0='33', @p1='Ted' (Size = 4000), @p2='True'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Students] SET [Age] = @p0, [Name] = @p1, [Sex] = @p2
WHERE [Id] = @p3;
SELECT @@ROWCOUNT;
在WebAPI这里用的
context.Entry(实体).State = EntityState.Modified;
对应SQL语句
Executed DbCommand (11ms) [Parameters=[@p1='f46104c4-7672-4db6-9d75-a1f038a16216', @p0='平板电脑' (Size = 100)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [PCategories] SET [CTitle] = @p0
WHERE [CId] = @p1;
SELECT @@ROWCOUNT;
在执行到state变更的该句之前事实上
都还是处於Detached的状态
直到被标记为Modified後
才会被变更追踪
EF中EntityState共分为如下几种
namespace Microsoft.EntityFrameworkCore
{
//
// 摘要:
// The state in which an entity is being tracked by a context.
public enum EntityState
{
//
// 摘要:
// The entity is not being tracked by the context.
Detached = 0,
//
// 摘要:
// The entity is being tracked by the context and exists in the database. Its property
// values have not changed from the values in the database.
Unchanged = 1,
//
// 摘要:
// The entity is being tracked by the context and exists in the database. It has
// been marked for deletion from the database.
Deleted = 2,
//
// 摘要:
// The entity is being tracked by the context and exists in the database. Some or
// all of its property values have been modified.
Modified = 3,
//
// 摘要:
// The entity is being tracked by the context but does not yet exist in the database.
Added = 4
}
}
直接改之前MVC里面Update写法
看起来还是一样全部栏位更新(不管是否跟之前一样)
事实上由於目前这两种写法都是直接针对该资料物件所有栏位做更新的写法
因此Update事实上也就是将所有都标记为Modified去做变更的追踪更新
我们这两种写法目前设计都是直接完全接收并覆写的做法(预设Entity状态都处於Detached)
若是改采用先查询後更新则预设从既有DB取出的Entity状态就是被追踪中的
则可以改为如下作法
这边我们先从DB捞取出指定的entity後
目前有改的只有年龄栏位
entity预设状态就是已被追踪中的
可以看到不用任何state标记或者呼叫Update都能完成更新
此外也会只更新有变动的栏位
因此在网上时常看到怎麽有人EF的例子是这样写
另一个又那样子写
事实上就是看你是否属於对於update有洁癖的人(有要更新的栏位再更新)
若是则可以先从DB捞取出来预设就会是attach
Ref:
Modifying data via the DbContext
https://www.learnentityframeworkcore.com/dbcontext/modifying-data
C# Entity Framework 更新数据的三种方法
https://blog.csdn.net/weixin_43602710/article/details/93638575
[C#.NET][Entity Framework] Update Record
https://dotblogs.com.tw/yc421206/2015/05/02/151197
Q&A：Entity Framework更新资料表部分栏位
http://vmiv.blogspot.com/2016/10/q-framework.html
观察 EF Core 产生的 SQL 指令
https://blog.darkthread.net/blog/efcore-logging/
.NET Core(C#) Entity Framework Core (EF Core) Update更新记录的方法及示例代码
https://www.cjavapy.com/article/1882/
本篇已同步发表至个人部落格
https://coolmandiary.blogspot.com/2022/01/net-core-web-api25apiefcorepart3.html
<<: .Net Core Web Api_笔记24_api结合EFCore资料库操作part2_产品分类资料新增_资料查询呈现(带入非同步API修饰)
好记性的浏览器：localStorage
谈这个主题之前，我们先来回忆一下日常的场景，你来到桃子购物网站，选了你要的商品往下卷动网页，结果竟...
分支系列 - 7：合并发生冲突怎麽办？
角色情境 小明同时学会输入指令操作着终端机、 以及透过滑鼠操作着图像化介面的 Sourcetree ...
Day 14:Coroutine，那是什麽？好吃嘛？
Keyword: coroutine 这几天在使用网路功能时，都使用到了Kotlin的Corouti...
Outlook PST Repair Tool - Repair PST data file
When you are troubling to access your PST file due...
D1. 学习基础C、C++语言
学习动机： 资工大一最刚开始就是从c语言开始，刚上大一的时候都不知道程序语言在干嘛，然後进度也教的很...