.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添加
留言
張貼留言