.Net Core Web Api_筆記18_api結合ADO.NET資料庫操作part6_新聞文章表格陳列查詢

2021 iThome 鐵人賽

自我挑戰組

.NET Core WebApi網頁應用開發系列第 18 篇

13th鐵人賽

Samuel

2021-12-23 14:54:38

4417 瀏覽

分享至

由前面幾個開發方式可以漸漸了解到前後端分離的開發方式

接著我們要進行新聞文章表格陳列

在Controller新增Show的查詢動作方法

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

<!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

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; }
    }
}

並在NewsController擴充方法GetNewsTypeNameById

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