.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)
)

建立并配置好visual studio .net core web api专案

配置好资料库连线设定(位在appsetting.json中)

安装 Microsoft.Extensions.Configuration.Json
我们会藉由它提供的api做组态档案读取跟设置

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

测试是否能成功取得连线字串

安装Microsoft.Data.SqlClient

在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参数内文

有成功传进Action

也成功塞入DB table

这里准备好一个前端html页面
引入好bs4跟jquery3.6
测试Client端有UI时的互动存取(透过jQuery)

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添加