我們在上一篇的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
iThome鐵人賽
看影片追技術