.Net Core Web Api_筆記19_api結合ADO.NET資料庫操作part7_新聞文章的編輯更新與刪除




首先操作部分

刪除及編輯頁面回填By NewsId查詢的Action 擴充

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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的執行
而編輯則是跳轉到特定一頁

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
<!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吻合則設置為預設被選取的狀態

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
<!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>


剩下編輯的表單更新提交

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
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觸發呼叫


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
<!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



after

















 

留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題