.Net Core Web Api_筆記20_api結合ADO.NET資料庫操作part8_新聞文章查詢

2021 iThome 鐵人賽

自我挑戰組

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

13th鐵人賽

Samuel

2021-12-23 15:01:52

5952 瀏覽

分享至

在大量數據同時表格陳列於畫面上時通常
都會需要有一些關鍵字查詢的機制

新增Search的Action
這邊我們By 文章內容和標題的模糊查詢

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清除避免資料重疊

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

本篇已同步發表至個人部落格
https://coolmandiary.blogspot.com/2021/12/net-core-web-api20apiadonetpart8.html