ASP.Net Core 資料庫連線設定

簡介

MS SQL Server以Database First為主的連線設定

ASP.Net Core Web API

安裝套件

安裝下列套件

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools
• Microsoft.EntityFrameworkCore.Design

連線設定

Scaffold-DbContext "Server=伺服器位置;Database=資料庫;Trusted_Connection=True;
TrustServerCertificate=True;User ID=帳號;Password=密碼" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

Scaffold-DbContext "Server=DESKTOP-C7VSQGJ\SQLEXPRESS;Database=ERP;User ID=chwang;Password=Agh120986;Trusted_Connection=True;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force -CoNtext ErpContext

產生ERPContext.cs會出現一個警告

#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.

清空OnConfiguring

program.cs 增加下列之程式碼

LocalDB 設定

SQL Server 2022 Express LocalDB 安裝

匯入mdf

開啟完全控制權限

開啟NuGet-Console

Scaffold-DbContext "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\Todo.mdf;Integrated Security=True;Connect Timeout=30;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force -CoNtext TodoContext

開啟伺服器管理

資料庫物件使用

https://localhost:7165/api/Todo

CRUD

Create

Read

Update

Delete

References

1. ASP.NET Core Web API 入門教學 - 文章目錄
   https://blog.talllkai.com/ASPNETCore/Catalog

2. [解決方式] 此憑證鏈結是由不受信任的授權單位發出的
   https://www.ruyut.com/2023/02/sqlexception-ssl-provider.html

3. ASP.NET Core 6 - how to deal with the missing Startup.cs file
   https://mobiletonster.com/blog/code/aspnet-core-6-how-to-deal-with-the-missing-startupcs-file

4. 如何下載與升級 SQL Server Express LocalDB 到最新版
   https://blog.miniasp.com/post/2020/02/16/install-and-upgrade-sql-server-express-localdb