.NET Core第10天_搭配EF Core串聯資料庫Db First_使用EntityFramework執行檢視的MVC控制器
首先新建好專案後
一樣記得要先弄好MVC服務注入跟MVC預設路由形式設定
新增好空的.net core專案後
到nuget中安裝entityframework core相關套件
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
在PMC (Package Manager Console) 下Scaffold-DbContext指令
將建立既有資料庫的 EF Core 模型。
「Tools」 - 「NuGet Package Manager」 - 「Package Manager Console」，輸入以下指令：
Scaffold-DbContext "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Employees,Orders
指令完整的格式定義
Scaffold-DbContext "Server=伺服器位置;Database=資料庫;Trusted_Connection=True;MultipleActiveResultSets=true;User ID=帳號;Password=密碼" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force
北風資料庫若本機SSMS還沒有可以自己新建
"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true"
將連接到本機SqlExpress伺服器裡頭的Northwind資料庫
Trusted_Connection=True
信任目前登入帳號登入資料庫。
MultipleActiveResultSets=true
允許在單一次DB連線中可執行多個批次作業。
-OutputDir Models
代表要將自動產生的檔案放入到 /Models之下
否則預設會統一放在專案跟目錄就容易零亂
此時若一執行會報錯
Scaffold-DbContext : 無法辨識 'Scaffold-DbContext' 詞彙是否為 Cmdlet、函數、指令檔或可執行程式的名稱。
這裡要再補安裝一個nuget套件
Microsoft.EntityFrameworkCore.Tools
再次執行又遇到一個新錯誤
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SNI_PN11, error: 26 - Error Locating Server/Instance Specified)
這裡由於本機裝的是SQL Server 2019 打開SQLServerManager15.msc跟services.msc
做確認後原因在於現在實體指定的是指向到本機預設MSSQLSERVER而非SQLEXPRESS
因此只要打一個.即可
修正過後的指令
Scaffold-DbContext "Server=.;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Employees,Orders
有關於SQLExpress跟MSSQLSERVER的差異可以參考這一篇來調整指令中連線字串
預設在安裝SQL Server時候若沒有去特別設定額外的實體名稱
則默認實體名稱會是.， (local)或者machine name跟ip address，當然在服務中默認捨麼都沒有改的情況下會顯示MSSQLSERVER
有特別去指定命名SQLEXPRESS的實體則寫法會是
.\SQLEXPRESS
localhost\SQLEXPRESS
當指令成功執行完後就會自動產生EF Core幫我們準備好的Model class跟Context class
DbContext當中可以看到
DbSet部分的變數名對應資料庫中table名稱(不可更動)
因此也會建議在table命名上建議用複數形式表示這樣子才能跟Model Class加以區隔
NorthwindContext 完整程式
using System; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata; #nullable disable namespace prjNet5_3_DbFirst.Models { public partial class NorthwindContext : DbContext { public NorthwindContext() { } public NorthwindContext(DbContextOptions<NorthwindContext> options) : base(options) { } public virtual DbSet<Employee> Employees { get; set; } public virtual DbSet<Order> Orders { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { #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. optionsBuilder.UseSqlServer("Server=.;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true"); } } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.HasAnnotation("Relational:Collation", "Chinese_Taiwan_Stroke_CI_AS"); modelBuilder.Entity<Employee>(entity => { entity.HasIndex(e => e.LastName, "LastName"); entity.HasIndex(e => e.PostalCode, "PostalCode"); entity.Property(e => e.EmployeeId).HasColumnName("EmployeeID"); entity.Property(e => e.Address).HasMaxLength(60); entity.Property(e => e.BirthDate).HasColumnType("datetime"); entity.Property(e => e.City).HasMaxLength(15); entity.Property(e => e.Country).HasMaxLength(15); entity.Property(e => e.Extension).HasMaxLength(4); entity.Property(e => e.FirstName) .IsRequired() .HasMaxLength(10); entity.Property(e => e.HireDate).HasColumnType("datetime"); entity.Property(e => e.HomePhone).HasMaxLength(24); entity.Property(e => e.LastName) .IsRequired() .HasMaxLength(20); entity.Property(e => e.Notes).HasColumnType("ntext"); entity.Property(e => e.Photo).HasColumnType("image"); entity.Property(e => e.PhotoPath).HasMaxLength(255); entity.Property(e => e.PostalCode).HasMaxLength(10); entity.Property(e => e.Region).HasMaxLength(15); entity.Property(e => e.Title).HasMaxLength(30); entity.Property(e => e.TitleOfCourtesy).HasMaxLength(25); entity.HasOne(d => d.ReportsToNavigation) .WithMany(p => p.InverseReportsToNavigation) .HasForeignKey(d => d.ReportsTo) .HasConstraintName("FK_Employees_Employees"); }); modelBuilder.Entity<Order>(entity => { entity.HasIndex(e => e.CustomerId, "CustomerID"); entity.HasIndex(e => e.CustomerId, "CustomersOrders"); entity.HasIndex(e => e.EmployeeId, "EmployeeID"); entity.HasIndex(e => e.EmployeeId, "EmployeesOrders"); entity.HasIndex(e => e.OrderDate, "OrderDate"); entity.HasIndex(e => e.ShipPostalCode, "ShipPostalCode"); entity.HasIndex(e => e.ShippedDate, "ShippedDate"); entity.HasIndex(e => e.ShipVia, "ShippersOrders"); entity.Property(e => e.OrderId).HasColumnName("OrderID"); entity.Property(e => e.CustomerId) .HasMaxLength(5) .HasColumnName("CustomerID") .IsFixedLength(true); entity.Property(e => e.EmployeeId).HasColumnName("EmployeeID"); entity.Property(e => e.Freight) .HasColumnType("money") .HasDefaultValueSql("((0))"); entity.Property(e => e.OrderDate).HasColumnType("datetime"); entity.Property(e => e.RequiredDate).HasColumnType("datetime"); entity.Property(e => e.ShipAddress).HasMaxLength(60); entity.Property(e => e.ShipCity).HasMaxLength(15); entity.Property(e => e.ShipCountry).HasMaxLength(15); entity.Property(e => e.ShipName).HasMaxLength(40); entity.Property(e => e.ShipPostalCode).HasMaxLength(10); entity.Property(e => e.ShipRegion).HasMaxLength(15); entity.Property(e => e.ShippedDate).HasColumnType("datetime"); entity.HasOne(d => d.Employee) .WithMany(p => p.Orders) .HasForeignKey(d => d.EmployeeId) .HasConstraintName("FK_Orders_Employees"); }); OnModelCreatingPartial(modelBuilder); } partial void OnModelCreatingPartial(ModelBuilder modelBuilder); } }
在繼承自DbContext的NorthwindContext.cs程式中預設在
OnConfiguring方法中有一串標記為警告的代表含意是說不建議把連線資訊放在程式中
(可能不安全或者不易更換建議寫於設定檔)
#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.
那可以先擺著
在此我們可看到
Employee類別對應資料庫表當中型態產生的Model
Employee.cs
using System; using System.Collections.Generic; #nullable disable namespace prjNet5_3_DbFirst.Models { public partial class Employee { public Employee() { InverseReportsToNavigation = new HashSet<Employee>(); Orders = new HashSet<Order>(); } public int EmployeeId { get; set; } public string LastName { get; set; } public string FirstName { get; set; } public string Title { get; set; } public string TitleOfCourtesy { get; set; } public DateTime? BirthDate { get; set; } public DateTime? HireDate { get; set; } public string Address { get; set; } public string City { get; set; } public string Region { get; set; } public string PostalCode { get; set; } public string Country { get; set; } public string HomePhone { get; set; } public string Extension { get; set; } public byte[] Photo { get; set; } public string Notes { get; set; } public int? ReportsTo { get; set; } public string PhotoPath { get; set; } public virtual Employee ReportsToNavigation { get; set; } public virtual ICollection<Employee> InverseReportsToNavigation { get; set; } public virtual ICollection<Order> Orders { get; set; } } }
使用精靈產生檢視畫面與控制器
模型類別:先選Employee
內容類別:就是選NorthwindContext
暫時勾消版面配置
預設就會把EmployeeController跟View對應操作畫面檢視產生出來
EmployeesController.cs的程式碼
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.Rendering; using Microsoft.EntityFrameworkCore; using prjNet5_3_DbFirst.Models; namespace prjNet5_3_DbFirst.Controllers { public class EmployeesController : Controller { private readonly NorthwindContext _context; public EmployeesController(NorthwindContext context) { _context = context; } // GET: Employees public async Task<IActionResult> Index() { var northwindContext = _context.Employees.Include(e => e.ReportsToNavigation); return View(await northwindContext.ToListAsync()); } // GET: Employees/Details/5 public async Task<IActionResult> Details(int? id) { if (id == null) { return NotFound(); } var employee = await _context.Employees .Include(e => e.ReportsToNavigation) .FirstOrDefaultAsync(m => m.EmployeeId == id); if (employee == null) { return NotFound(); } return View(employee); } // GET: Employees/Create public IActionResult Create() { ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName"); return View(); } // POST: Employees/Create // To protect from overposting attacks, enable the specific properties you want to bind to. // For more details, see http://go.microsoft.com/fwlink/?LinkId=317598. [HttpPost] [ValidateAntiForgeryToken] public async Task<IActionResult> Create([Bind("EmployeeId,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City,Region,PostalCode,Country,HomePhone,Extension,Photo,Notes,ReportsTo,PhotoPath")] Employee employee) { if (ModelState.IsValid) { _context.Add(employee); await _context.SaveChangesAsync(); return RedirectToAction(nameof(Index)); } ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName", employee.ReportsTo); return View(employee); } // GET: Employees/Edit/5 public async Task<IActionResult> Edit(int? id) { if (id == null) { return NotFound(); } var employee = await _context.Employees.FindAsync(id); if (employee == null) { return NotFound(); } ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName", employee.ReportsTo); return View(employee); } // POST: Employees/Edit/5 // To protect from overposting attacks, enable the specific properties you want to bind to. // For more details, see http://go.microsoft.com/fwlink/?LinkId=317598. [HttpPost] [ValidateAntiForgeryToken] public async Task<IActionResult> Edit(int id, [Bind("EmployeeId,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City,Region,PostalCode,Country,HomePhone,Extension,Photo,Notes,ReportsTo,PhotoPath")] Employee employee) { if (id != employee.EmployeeId) { return NotFound(); } if (ModelState.IsValid) { try { _context.Update(employee); await _context.SaveChangesAsync(); } catch (DbUpdateConcurrencyException) { if (!EmployeeExists(employee.EmployeeId)) { return NotFound(); } else { throw; } } return RedirectToAction(nameof(Index)); } ViewData["ReportsTo"] = new SelectList(_context.Employees, "EmployeeId", "FirstName", employee.ReportsTo); return View(employee); } // GET: Employees/Delete/5 public async Task<IActionResult> Delete(int? id) { if (id == null) { return NotFound(); } var employee = await _context.Employees .Include(e => e.ReportsToNavigation) .FirstOrDefaultAsync(m => m.EmployeeId == id); if (employee == null) { return NotFound(); } return View(employee); } // POST: Employees/Delete/5 [HttpPost, ActionName("Delete")] [ValidateAntiForgeryToken] public async Task<IActionResult> DeleteConfirmed(int id) { var employee = await _context.Employees.FindAsync(id); _context.Employees.Remove(employee); await _context.SaveChangesAsync(); return RedirectToAction(nameof(Index)); } private bool EmployeeExists(int id) { return _context.Employees.Any(e => e.EmployeeId == id); } } }
這裡預設直接用精靈產生的仍然會執行報錯!
主要原因就是因為warning錯誤標記那段我們直接忽略沒調整
但事實上那段資料庫連線並沒有起到作用所以直接訪問會報錯
這裡我們把NorthwindContext.cs當中的
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)這方法裡面的連線字串程式碼整段註解或刪除掉
將連線字串("Server=.;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true")
若你是屬於有實體名稱的反斜線記得要兩條
("Server=.\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true")
設定移置appsettings.json
預設appsettings.json
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
調整後的appsettings.json
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DbConnectonString": "Server=伺服器位置;Database=資料庫;Trusted_Connection=True;MultipleActiveResultSets=true;User ID=帳號;Password=密碼"
}
}
所以在程式中KEY值取得就是透過DbConnectonString
在Startup.cs中加入資料庫物件的DI (服務注入一律寫在ConfigureServices)
加入如下程式調整
調整過後的Startup.cs
using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Http; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using prjNet5_3_DbFirst.Models; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace prjNet5_3_DbFirst { public class Startup { public Startup(IConfiguration configuration) { Configuration = configuration; } public IConfiguration Configuration { get; } // This method gets called by the runtime. Use this method to add services to the container. // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940 public void ConfigureServices(IServiceCollection services) { services.AddMvc(); services.AddDbContext<NorthwindContext>(options =>options.UseSqlServer(Configuration.GetConnectionString("DbConnectonString"))); } // This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } app.UseRouting(); app.UseEndpoints(endpoints => { endpoints.MapControllerRoute( name: "default", pattern: "{Controller=Home}/{Action=Index}/{id?}" );//設置MVC默認路由 }); } } }
再次執行就正常了
Ref:
教學課程：開始使用 ASP.NET MVC web 應用程式中的 EF Core
https://docs.microsoft.com/zh-tw/aspnet/core/data/ef-mvc/intro?view=aspnetcore-5.0
還原工程 (Scaffolding)
https://docs.microsoft.com/zh-tw/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli
ASP.NET Core - (DB-First) Scaffold-DbContext的錯誤訊息
https://dotblogs.com.tw/mis2000lab/2020/02/05/ASPnet_Core_Scaffold-DbContext_Error
對 SQL Server 資料庫引擎的連線進行疑難排解
https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15
[MSSQL] 無法連線到 MS-SQL error-a-network-related-or-instance-specific-error-occurred-while-establishing
https://marcus116.blogspot.com/2017/12/error-a-network-related-or-instance-specific-error-occurred-while-establishing.html
What is the difference between SQLEXPRESS and MSSQLSERVER?
https://dba.stackexchange.com/questions/22246/what-is-the-difference-between-sqlexpress-and-mssqlserver/22248
謝謝！！！
回覆刪除