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

 
專案前置準備

新建好資料庫以及資料表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
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


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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)

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


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
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

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

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

























留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題