.Net Core Web Api_笔记25_api结合EFCore数据库操作part3_产品分类数据的编辑与删除(EF的更新写法怎么这么多种! 如何观察EF产生的SQL)

我们在上一篇的Show.html
已经完成了数据查询呈现
这里要多出操作(比方像是编辑、删除…)
https://ithelp.ithome.com.tw/upload/images/20220113/20107452Nk2k53yywS.png

在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 回填
https://ithelp.ithome.com.tw/upload/images/20220113/20107452aaLDNOIk7z.png

更改之前
https://ithelp.ithome.com.tw/upload/images/20220113/201074524ShEfJqdMx.png

执行编辑更新后
https://ithelp.ithome.com.tw/upload/images/20220113/20107452Wo6fWvngMU.png

https://ithelp.ithome.com.tw/upload/images/20220113/20107452eS6rgQAeMa.png

扩充删除用的 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();
        }
    }
}

运行效果
https://ithelp.ithome.com.tw/upload/images/20220113/20107452GU9Dg5SYT9.png

https://ithelp.ithome.com.tw/upload/images/20220113/20107452ofmUqygJcA.png

删除这边的API访问我们采用同步的方式确保不会因为entity取到null造成null例外错误

在此会发现Entity Framework的更新写法怎么跟之前
在EntityFramework Core笔记(2)_CodeFirst配置_搭配MVC应用的开发_编辑、删除
所写的方式不一
样 事实上在EntityFramework中有很多种更新的写法

在之前文章的.net core MVC这边
的更新 我们采用的是直接

context. Update(实体);
context. SaveChanges();-或 context. SaveChangesAsync();

https://ithelp.ithome.com.tw/upload/images/20220113/201074522xPPkWWb2b.png

而在.net core WebAPI这边
我们采用的是

context. Entry(实体). State = EntityState.Modified;
context. SaveChanges();-或 context. SaveChangesAsync();
https://ithelp.ithome.com.tw/upload/images/20220113/20107452SyMqEL3bWY.png

若想观察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應用才看的到
https://ithelp.ithome.com.tw/upload/images/20220113/20107452o4t51lU1fH.png

預設是沒有帶實際參數值的版本
https://ithelp.ithome.com.tw/upload/images/20220113/20107452FxugHfq0Xm.png

這裡只要啟動敏感資料log機制即可

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<MyTestDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("MyTestDbConn"))
               .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
               .EnableSensitiveDataLogging()
    );
    services.AddControllersWithViews();
}
https://ithelp.ithome.com.tw/upload/images/20220113/20107452wzAlLOfjVF.png

在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;
https://ithelp.ithome.com.tw/upload/images/20220113/20107452lknqt1ZHcY.png
https://ithelp.ithome.com.tw/upload/images/20220113/20107452z5ugcByExv.png

在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的状态
https://ithelp.ithome.com.tw/upload/images/20220113/20107452JeHSQ0GDSL.png

直到被标记为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写法
https://ithelp.ithome.com.tw/upload/images/20220113/20107452Q1kTi1s3cO.png

https://ithelp.ithome.com.tw/upload/images/20220113/20107452P2qqXhpZ7p.png

看起来还是一样全部字段更新(不管是否跟之前一样)

事实上由于目前这两种写法都是直接针对该数据对象所有字段做更新的写法
因此Update事实上也就是将所有都标记为Modified去做变更的追踪更新

我们这两种写法目前设计都是直接完全接收并覆盖的做法(默认Entity状态都处于Detached)
若是改采用先查询后更新则默认从既有DB取出的Entity状态就是被追踪中的

则可以改为如下作法
这边我们先从DB捞取出指定的entity
后 目前有改的只有年龄字段

entity默认状态就是已被追踪中的
可以看到不用任何state标记或者呼叫Update都能完成更新
此外也会只更新有变动的字段

https://ithelp.ithome.com.tw/upload/images/20220113/20107452HvlqMlhp8b.png
https://ithelp.ithome.com.tw/upload/images/20220113/20107452fPqLFqdG9E.png
https://ithelp.ithome.com.tw/upload/images/20220113/201074520CBx7XRZNB.png

因此在网上时常看到怎么有人EF的例子是这样写
另一个又那样子
写 事实上就是看你是否属于对于update有洁癖的人(有要更新的字段再更新)

若是则可以先从DB捞取出来预设就会是attach

(0)
打赏 微信扫一扫 微信扫一扫

相关推荐

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注