.Net Core Web Api_笔记19_api结合ADO.NET资料库操作part7_新闻文章的编辑更新与删除

https://ithelp.ithome.com.tw/upload/images/20211223/20107452s51dravWJT.png

首先操作部分

删除及编辑页面回填By NewsId查询的Action 扩充

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using MyNet5ApiAdoTest.Models;
using MyNet5ApiAdoTest.Utility;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class NewsController : ControllerBase
    {

        //.......之前的略

        [HttpDelete("Delete")]
        public ActionResult<int> DeleteNewsInfo(int? id)
        {
            if (id == null)
                return NotFound();

            string strSQL = @"delete from NewsInfo where NewsId=@Id";
            Hashtable htParms = new Hashtable();
            htParms.Add("@Id", id);
            int RowCount = MSSQLHelper.ExecuteNonQuery(strSQL, htParms);
            return RowCount;
        }

        [HttpGet("GetById")]
        public ActionResult<NewsInfo> GetNewsInfoById(int? id)
        {
            if (id == null)
                return NotFound();

            string strSQL = @"select * from NewsInfo where NewsId=@Id";
            Hashtable htParams = new Hashtable();
            htParams.Add("@Id", id);
            SqlDataReader dataReader = MSSQLHelper.GetSqlDataReader(strSQL, htParams);
            NewsInfo newsInfo = new NewsInfo();
            while (dataReader.Read())
            {
                newsInfo.NewsId = dataReader.GetInt32(0);
                newsInfo.NewsTitle = dataReader.GetString(1);
                newsInfo.NewsContent = dataReader.GetString(2);
                newsInfo.NewsTypeId = dataReader.GetInt32(4);
            }
            dataReader.Close();
            return newsInfo;
        }


    }
}

Show.html中 jQuery Ajax 呼叫程序 及画面调整
新增操作Column 并附上跳转Edit页面跟删除的功能
主要差别在於删除直接呼叫js後端触发删除就只是一段js的执行
而编辑则是跳转到特定一页

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Show News</title>
    <link href="../css/bootstrap.min.css" rel="stylesheet" />
    <script src="../js/jquery/jquery.min.js"></script>
</head>
<body style="margin:20px;">
    <table id="tbNews" class="table table-bordered">
        <thead>
            <tr>
                <td nowrap>文章ID</td>
                <td nowrap>文章标题</td>
                <td nowrap>文章内文</td>
                <td nowrap>文章分类</td>
                <td nowrap>操作</td>
            </tr>
        </thead>
        <tbody></tbody>
    </table>

    <script type="text/javascript">
        $(function () {
            var tbody = $('#tbNews tbody')
            $.ajax({
                type: "get",
                url: "/api/News/show",
                dataType: "json",
                success: function (result) {
                    console.log(result);
                    $.each(result, function (n, value) {
                        var tr_val = "";
                        tr_val += "<tr>" +
                            "<td>" + value.newsId + "</td>" +
                            "<td>" + value.newsTitle + "</td>" +
                            "<td>" + value.newsContent + "</td>" +
                            /*"<td>" + value.newsTypeId + "</td>" +*/
                            "<td>" + value.newsTypeName + "</td>" +
                            "<td nowrap>" +
                            "<a href='Edit.html?id=" + value.newsId + "&tid=" + value.newsTypeId + "'>编辑</a> " +
                            "<a href='javascript:Del(" + value.newsId + ")'>删除</a>" +
                            "</td>" +
                            "</tr>";
                        tbody += tr_val;
                    });
                    $('#tbNews').append(tbody);
                }
            });
        });

        function Del(id) {
            $.ajax({
                type: "delete",
                url: "/api/news/delete?id=" + id,
                dataType: "json",
                success: function (result) {
                    if (result != "0") {
                        location.href = "Show.html";
                    }
                }
            });
        }


    </script>
</body>
</html>

News Edit.html 画面
则是By特定NewsId去查询回填至画面中
在文章分类下拉选单则是额外一个ajax存取查询回填
当type_id吻合则设置为预设被选取的状态

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Edit News</title>
    <link href="../css/bootstrap.min.css" rel="stylesheet" />
    <script src="../js/jquery/jquery.min.js"></script>
</head>
<body>
    <div style="padding:20px;border:1px solid #ccc;width:600px;margin:30px;">
        <h3>新增新闻文章</h3>
        <hr />
        <div class="form-horizontal">
            <div class="form-group col-8">
                <label>新闻标题:</label>
                <input type="text" id="NewsTitle" class="form-control" />
            </div>
            <div class="form-group col-8">
                <label>新闻内容:</label>
                <textarea id="NewsContent" class="form-control"></textarea>
            </div>
            <div class="form-group col-8">
                <label>新闻分类:</label>
                <select id="NewsTypeId"></select>
            </div>
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <input type="submit" id="savebtn" value="更新" class="btn btn-primary" />
                </div>
                <div>
                    <span id="msg" class="bg-danger"></span>
                </div>
            </div>
        </div>
    </div>

    <script type="text/javascript">
        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");
            var type_id = getQueryGetParams("tid");

            $.ajax({
                type: "get",
                url: "/api/news/getbyid?id=" + id,
                dataType: "json",
                success: function (result) {
                    $("#NewsTitle").val(result.newsTitle);
                    $("#NewsContent").val(result.newsContent);
                }
            });

            $.ajax({
                type: "get",
                url: "/api/newstype/show",
                dataType: "json",
                success: function (result) {
                    var opt = "";
                    $.each(result, function (n, value) {
                        if (value.newsTypeId == type_id) {
                            opt += "<option selected='selected' id='" + value.newsTypeId + "'>" + value.newsTypeName + "</option>";
                        } else {
                            opt += "<option id='" + value.newsTypeId + "'>" + value.newsTypeName + "</option>";
                        }
                    });
                    $("#NewsTypeId").append(opt);
                }
            });

        });
    </script>
</body>
</html>

https://ithelp.ithome.com.tw/upload/images/20211223/20107452sN2Dqr7zb2.png

剩下编辑的表单更新提交

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using MyNet5ApiAdoTest.Models;
using MyNet5ApiAdoTest.Utility;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class NewsController : ControllerBase
    {

        [HttpPost("Add")]
        public ActionResult<int> AddNewsInfo(NewsInfo newsInfo)
        {
            int RowCount = 0;
            if (newsInfo == null)
                return NotFound();

            string strSQL = @"INSERT INTO NewsInfo (NewsTitle,NewsContent,CreateDate,NewsTypeId) 
                                  VALUES (@NewsTitle,@NewsContent,@CreateDate,@NewsTypeId) ";
            Hashtable htParams = new Hashtable();
            htParams.Add("@NewsTitle", newsInfo.NewsTitle);
            htParams.Add("@NewsContent", newsInfo.NewsContent);
            //htParams.Add("@CreateDate", newsInfo.CreateDate);
            htParams.Add("@CreateDate", DateTime.Now);
            htParams.Add("@NewsTypeId", newsInfo.NewsTypeId);
            RowCount = MSSQLHelper.ExecuteNonQuery(strSQL, htParams);
            return RowCount;
        }

        [HttpGet("Show")]
        public ActionResult<List<NewsInfo>> ShowNewsInfo()
        {
            string strSQL = @" select * from NewsInfo ";
            SqlDataReader dataReader = MSSQLHelper.GetSqlDataReader(strSQL);
            if (!dataReader.HasRows)
                return NotFound();
            List<NewsInfo> lsNewsInfo = new List<NewsInfo>();
            while (dataReader.Read())
            {
                lsNewsInfo.Add(new NewsInfo()
                {
                    NewsId = dataReader.GetInt32(0),
                    NewsTitle = dataReader.GetString(1),
                    NewsContent = dataReader.GetString(2),
                    NewsTypeId = dataReader.GetInt32(4),
                    NewsTypeName = GetNewsTypeNameById(dataReader.GetInt32(4))
                });
            }
            dataReader.Close();
            return lsNewsInfo;
        }

        private string GetNewsTypeNameById(int newsTypeId)
        {
            string strSQL = @"select NewsTypeName from NewsType where NewsTypeId=@NewsTypeId";
            Hashtable htParams = new Hashtable();
            htParams.Add("@NewsTypeId", newsTypeId);
            var newsTypeName = MSSQLHelper.ExecuteScalar(strSQL, htParams);
            return newsTypeName.ToString();
        }

        [HttpDelete("Delete")]
        public ActionResult<int> DeleteNewsInfo(int? id)
        {
            if (id == null)
                return NotFound();

            string strSQL = @"delete from NewsInfo where NewsId=@Id";
            Hashtable htParms = new Hashtable();
            htParms.Add("@Id", id);
            int RowCount = MSSQLHelper.ExecuteNonQuery(strSQL, htParms);
            return RowCount;
        }

        [HttpGet("GetById")]
        public ActionResult<NewsInfo> GetNewsInfoById(int? id)
        {
            if (id == null)
                return NotFound();

            string strSQL = @"select * from NewsInfo where NewsId=@Id";
            Hashtable htParams = new Hashtable();
            htParams.Add("@Id", id);
            SqlDataReader dataReader = MSSQLHelper.GetSqlDataReader(strSQL, htParams);
            NewsInfo newsInfo = new NewsInfo();
            while (dataReader.Read())
            {
                newsInfo.NewsId = dataReader.GetInt32(0);
                newsInfo.NewsTitle = dataReader.GetString(1);
                newsInfo.NewsContent = dataReader.GetString(2);
                newsInfo.NewsTypeId = dataReader.GetInt32(4);
            }
            dataReader.Close();
            return newsInfo;
        }

        [HttpPut("Update")]
        public ActionResult<int> UpdateNewsInfo(NewsInfo newsInfo)
        {
            if (newsInfo == null)
                return NotFound();
            int RowCount = 0;

            string strSQL = @" UPDATE NewsInfo 
                               SET NewsTitle = @NewsTitle,
                                   NewsContent = @NewsContent,
                                   CreateDate = @CreateDate,
                                   NewsTypeId = @NewsTypeId 
                               WHERE NewsId = @NewsId ";

            Hashtable htParams = new Hashtable();
            htParams.Add("@NewsTitle", newsInfo.NewsTitle);
            htParams.Add("@NewsContent", newsInfo.NewsContent);
            htParams.Add("@CreateDate", DateTime.Now);
            htParams.Add("@NewsTypeId", newsInfo.NewsTypeId);
            htParams.Add("@NewsId", newsInfo.NewsId);

            RowCount = MSSQLHelper.ExecuteNonQuery(strSQL,htParams);
            return RowCount;
        }
    }
}

前端部分jQuery触发呼叫

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Edit News</title>
    <link href="../css/bootstrap.min.css" rel="stylesheet" />
    <script src="../js/jquery/jquery.min.js"></script>
</head>
<body>
    <div style="padding:20px;border:1px solid #ccc;width:600px;margin:30px;">
        <h3>编辑新闻文章</h3>
        <hr />
        <div class="form-horizontal">
            <div class="form-group col-8">
                <label>新闻标题:</label>
                <input type="text" id="NewsTitle" class="form-control" />
            </div>
            <div class="form-group col-8">
                <label>新闻内容:</label>
                <textarea id="NewsContent" class="form-control"></textarea>
            </div>
            <div class="form-group col-8">
                <label>新闻分类:</label>
                <select id="NewsTypeId"></select>
            </div>
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <input type="submit" id="savebtn" value="更新" class="btn btn-primary" />
                </div>
                <div>
                    <span id="msg" class="bg-danger"></span>
                </div>
            </div>
        </div>
    </div>

    <script type="text/javascript">
        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");
            var type_id = getQueryGetParams("tid");

            $.ajax({
                type: "get",
                url: "/api/news/getbyid?id=" + id,
                dataType: "json",
                success: function (result) {
                    $("#NewsTitle").val(result.newsTitle);
                    $("#NewsContent").val(result.newsContent);
                }
            });

            $.ajax({
                type: "get",
                url: "/api/newstype/show",
                dataType: "json",
                success: function (result) {
                    var opt = "";
                    $.each(result, function (n, value) {
                        if (value.newsTypeId == type_id) {
                            opt += "<option selected='selected' id='" + value.newsTypeId + "'>" + value.newsTypeName + "</option>";
                        } else {
                            opt += "<option id='" + value.newsTypeId + "'>" + value.newsTypeName + "</option>";
                        }
                    });
                    $("#NewsTypeId").append(opt);
                }
            });

        });

        $('#savebtn').click(function () {
            var id = getQueryGetParams("id");
            $.ajax({
                type: 'put',
                url: '/api/news/update',
                dataType: 'text',
                data: JSON.stringify({
                    NewsTitle: $('#NewsTitle').val(),
                    NewsContent: $('#NewsContent').val(),
                    NewsTypeId: Number.parseInt($("#NewsTypeId").find("option:selected").attr("id")),
                    NewsId: Number.parseInt(id)
                }),
                contentType: 'application/json',
                success: function (result) {
                    if (result == "1") {
                        $('#msg').text('成功更新');
                    }
                }
            });
        });


    </script>
</body>
</html>

效果
before
https://ithelp.ithome.com.tw/upload/images/20211223/20107452hsjC7jmxvH.png

https://ithelp.ithome.com.tw/upload/images/20211223/20107452k11AWNQPG6.png

https://ithelp.ithome.com.tw/upload/images/20211223/20107452yXJcissNn0.png

after
https://ithelp.ithome.com.tw/upload/images/20211223/20107452N723vao92o.png

本篇已同步发表至个人部落格
https://coolmandiary.blogspot.com/2021/12/net-core-web-api19apiadonetpart7.html


<<:  .Net Core Web Api_笔记18_api结合ADO.NET资料库操作part6_新闻文章表格陈列查询

>>:  .Net Core Web Api_笔记20_api结合ADO.NET资料库操作part8_新闻文章查询

# Day1 简介

Linux 是一个泛用型的作业系统，在各式各样、形形色色的机器上都找的到他的踪迹， 而 Linux...

18 程序竞赛前中後准备实例 (NCPC 2021)

昨天写完「程序竞赛前中後准备技巧」後，今天下午便紧接着 NCPC，因此想趁这个机会做个纪录，顺便为上...

如果你竭尽全力，就不会有时间担心失败。

如果你竭尽全力，就不会有时间担心失败。 If you're doing your best, you...

[Day14]PHP Class 类别01

class类 class基本概念 每个类的定义都以关键字 class 开头，後面跟着类的名，再一个括...

Kotlin Android 第19天，从 0 到 ML - RecyclerView 动态列表

前言: RecyclerView 可以轻松高效地显示大量数据。 RecyclerView回收这些单独...