.Net Core Web Api_筆記20_api結合ADO.NET資料庫操作part8_新聞文章查詢
在大量數據同時表格陳列於畫面上時通常
都會需要有一些關鍵字查詢的機制
新增Search的Action
這邊我們By 文章內容和標題的模糊查詢
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 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | 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; } [HttpGet("Search")] public ActionResult<List<NewsInfo>> SearchNews(string keyword) { string strSQL = @"select * from NewsInfo where NewsTitle like @keyword1 or NewsContent like @keyword2 "; Hashtable htParams = new Hashtable(); htParams.Add("@keyword1", $"%{keyword}%"); htParams.Add("@keyword2", $"%{keyword}%"); SqlDataReader dataReader = MSSQLHelper.GetSqlDataReader(strSQL, htParams); 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 Ajax呼叫查詢回來結果處理
每次呼叫前都把table的tbody清除避免資料重疊
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 | <!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;"> <div> <input type="text" id="keyword" style="height:30px;" /> <input type="button" id="search" class="btn btn-primary btn-sm" onclick="javascript:search()" value="查詢" /> </div> <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"; } } }); } function search() { var key_word = $("#keyword").val(); var tbody = $('#tbNews tbody') $.ajax({ type: "get", url: "/api/News/search?keyword=" + key_word, dataType: "json", success: function (result) { tbody.empty(); $.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); } }) } </script> </body> </html> |
留言
張貼留言