.Net Core Web Api_筆記18_api結合ADO.NET資料庫操作part6_新聞文章表格陳列查詢
由前面幾個開發方式可以漸漸了解到前後端分離的開發方式
接著我們要進行新聞文章表格陳列
在Controller新增Show的查詢動作方法
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 | 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) }); } dataReader.Close(); return lsNewsInfo; } } } |
wwwroot/News/Show.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 | <!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>文章ID</td> <td>文章標題</td> <td>文章內文</td> <td>文章分類</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>" + "</tr>"; tbody += tr_val; }); $('#tbNews').append(tbody); } }); }); </script> </body> </html> |
那其實目前有個問題在於文章類別顯示ID流水號其實看不懂
應該要改為中文定義顯示
於NewsInfo Model中擴充TypeName
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace MyNet5ApiAdoTest.Models { public class NewsInfo { public int NewsId { get; set; } public string NewsTitle { get; set; } public string NewsContent { get; set; } public DateTime? CreateDate { get; set; } public int? NewsTypeId { get; set; } public string NewsTypeName { get; set; } } } |
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 | 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(); } } } |
至於在Show.html就簡單把ID替換成newsTypeName
留言
張貼留言