.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
留言
張貼留言