iT邦幫忙

2021 iThome 鐵人賽

DAY 2
0
自我挑戰組

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

.Net Core Web Api_筆記13_api結合ADO.NET資料庫操作part1_專案前置準備到資料新增

專案前置準備

新建好資料庫以及資料表

create table NewsType
(
	NewsTypeId int identity primary key,
	NewsTypeName nvarchar(200),
	isEnabled bit
)

create table NewsInfo
(
	NewsId int identity primary key,
	NewsTitle nvarchar(300),
	NewsContent nvarchar(max),
	CreateDate datetime,
	NewsTypeId int foreign key references NewsType(NewsTypeId)
)

https://ithelp.ithome.com.tw/upload/images/20210930/20107452G8ba83BTEZ.png

建立並配置好visual studio .net core web api專案

https://ithelp.ithome.com.tw/upload/images/20210930/20107452IhDRSwaq5r.png

配置好資料庫連線設定(位在appsetting.json中)
https://ithelp.ithome.com.tw/upload/images/20210930/20107452BuyDTYa6bT.png

安裝 Microsoft.Extensions.Configuration.Json
我們會藉由它提供的api做組態檔案讀取跟設置

https://ithelp.ithome.com.tw/upload/images/20210930/20107452FFT4leZiuU.png

新增Models、Utility跟Services三目錄
在Services目錄新建一個Class
命名為
AppConfigurationService.cs

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Services
{
    public class AppConfigurationService
    {
        public static IConfiguration Configuration { get; set; }
        static AppConfigurationService()
        {
            Configuration = new Microsoft.Extensions.Configuration.ConfigurationBuilder()
                .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true }).Build();
        }
    }
}

這裡新增一個API控制器-空白名稱取名HomeController.cs
新增 Index Action method

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using MyNet5ApiAdoTest.Services;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class HomeController : ControllerBase
    {
        [HttpGet("index")]
        public string Index()
        {            
            string strConn = AppConfigurationService.Configuration.GetConnectionString("NewsDb");
            return strConn;
        }
    }
}

測試是否能成功取得連線字串
https://ithelp.ithome.com.tw/upload/images/20210930/20107452iEHqvqY8F4.png

安裝Microsoft.Data.SqlClient
https://ithelp.ithome.com.tw/upload/images/20210930/20107452pYhRwBfEQJ.png

在Utility目錄新增MSSQLHelper (based on ado.net)

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using MyNet5ApiAdoTest.Services;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Utility
{
    public static class MSSQLHelper
    {
        public static readonly string connectionString = AppConfigurationService.Configuration.GetConnectionString("NewsDb");

        public static int ExecuteNonQuery(string strSQL, Hashtable htParams = null)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand sqlCommand = conn.CreateCommand();
                sqlCommand.CommandText = strSQL;
                sqlCommand.CommandType = CommandType.Text;
                PrepareParams(htParams, sqlCommand);
                return sqlCommand.ExecuteNonQuery();
            }
        }

        public static SqlDataReader GetSqlDataReader(string strSQL, Hashtable htParams = null)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand sqlCommand = conn.CreateCommand();
            sqlCommand.CommandText = strSQL;
            sqlCommand.CommandType = CommandType.Text;
            PrepareParams(htParams, sqlCommand);
            return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public static object ExecuteScalar(string strSQL, Hashtable htParams = null)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand sqlCommand = conn.CreateCommand();
                sqlCommand.CommandText = strSQL;
                sqlCommand.CommandType = CommandType.Text;
                PrepareParams(htParams, sqlCommand);
                return sqlCommand.ExecuteScalar();
            }
        }

        private static void PrepareParams(Hashtable htParams, SqlCommand sqlCommand)
        {
            if(htParams != null)
            {
                foreach (DictionaryEntry entry in htParams)
                {
                    sqlCommand.Parameters.AddWithValue(entry.Key.ToString(), entry.Value);
                }
            }            
        }
    }
}

於Models目錄下準備好兩個DTO Class

NewsType.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNet5ApiAdoTest.Models
{
    public class NewsType
    {
        public int NewsTypeId { get; set; }

        public string NewsTypeName { get; set; }

        public bool? isEnabled { get; set; }

    }
}

NewsInfo.cs

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

NewsType資料新增

新增NewsTypeController

NewsTypeController 先準備一個新增NewsType的Action

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
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 NewsTypeController : ControllerBase
    {
        [HttpPost("Add")]
        public ActionResult<int> AddNewsType(NewsType newsType)
        {
            int RowCount = 0;
            if (newsType != null)
            {
                string strSQL = @"INSERT INTO NewsType (NewsTypeName,IsEnabled)
                                VALUES (@NewsTypeName,@IsEnabled)";

                Hashtable htParms = new Hashtable();
                htParms.Add("@NewsTypeName",newsType.NewsTypeName);
                htParms.Add("@IsEnabled",newsType.isEnabled);
                RowCount = MSSQLHelper.ExecuteNonQuery(strSQL, htParms);
                return CreatedAtAction(nameof(AddNewsType), RowCount);
            }
            return RowCount;
        }

    }
}

用Postman先測試看看運行起專案
content type設置為application/json
body填入json參數內文

https://ithelp.ithome.com.tw/upload/images/20210930/20107452TNKuO4cEP8.png

https://ithelp.ithome.com.tw/upload/images/20210930/20107452eamB9GQTwa.png

有成功傳進Action
https://ithelp.ithome.com.tw/upload/images/20210930/20107452PqoHJ7Y9Py.png

也成功塞入DB table
https://ithelp.ithome.com.tw/upload/images/20210930/20107452se9sWIwddg.png

這裡準備好一個前端html頁面
引入好bs4跟jquery3.6
測試Client端有UI時的互動存取(透過jQuery)
https://ithelp.ithome.com.tw/upload/images/20210930/20107452ZZ7Z74FQvr.png

Add.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Add NewsType</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 #0094ff;width:600px;margin:30px;">
        <h3>新聞文章類別</h3>
        <hr />
        <div class="form-horizontal">
            <div class="form-group col-4">
                <label>文章類別</label>
                <input type="text" class="form-control" id="NewsTypeName">
            </div>
            <div class="form-group col-4">
                <label>是否啟用</label>
                <input type="checkbox" class="form-check" id="isEnabled">
            </div>
            <div class="form-group">
                <div class="col-md-2 col-md-10">
                    <button type="submit" id="btnSubmit" class="btn btn-primary">Submit</button>
                </div>
                <div>
                    <span id="msg" class="danger"></span>
                </div>
            </div>
        </div>
    </div>
    <script type="text/javascript">
        $('#btnSubmit').click(function(){
            $.ajax({
                type:'post',
                url:"/api/newstype/add",
                dataType:"text",
                data: JSON.stringify({
                    NewsTypeId: 1,
                    NewsTypeName: $('#NewsTypeName').val(),
                    isEnabled: $('#isEnabled').prop('checked')
                }),
                contentType: 'application/json',
                success: function (result) {
                    if (result == "1") {
                        $('#msg').text('成功添加');
                    }
                }
            });
        });
    </script>
</body>
</html>

也可成功存取api添加
https://ithelp.ithome.com.tw/upload/images/20210930/20107452tHz6jwKOD7.png

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


上一篇
.Net Core Web Api_筆記12_自定義屬性路由
下一篇
.Net Core Web Api_筆記14_api結合ADO.NET資料庫操作part2_資料查詢呈現
系列文
.NET Core WebApi網頁應用開發16

尚未有邦友留言

立即登入留言