using Dapper; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Threading.Tasks;
namespace DapperExample.Dapper { public class DataControl : IDisposable { private IDbConnection db = null; private string _connectStr;
//建構子初始化連線字串/DB連接 public DataControl() { // 自行替換資料庫連結字串 string connectionStr = @"Server=.\SQLExpress;Database=Orders;Trusted_Connection=True;ConnectRetryCount=0"; _connectStr = connectionStr; db = new SqlConnection(_connectStr); }
//共用新增刪除修改方法 public int Execute(string sql, object param) { return db.Execute(sql, param); }
//釋放連接 public void Dispose() { db.Dispose(); } } }
說明: 唯一要改的在20行要套入自己的資料庫連結字串。
2.再來建立對應的資料表Model
~/Models/Customer.cs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
using System.ComponentModel.DataAnnotations; namespace DapperExample.Models { public class Customer { [Display(Name = "編號")] public string Number { get; set; } [Display(Name = "名子")] [Required(ErrorMessage = "名字不可為空")] public string Name { get; set; } [Display(Name = "電話")] public string Tel { get; set; } } }
using DapperExample.Dapper; using DapperExample.Models; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Threading.Tasks;
namespace DapperExample.Controllers { public class HomeController : Controller { private readonly ILogger<HomeController> _logger;
public HomeController(ILogger<HomeController> logger) { _logger = logger; }
public IActionResult Index() { Customer customer; // 使用using，區塊結束後會自動執行Dispose using (DataControl dc = new DataControl()) { string number = "U001"; string sql = @"SELECT * FROM Customers WHERE number = @number"; var result = dc.Query<Customer>(sql, new { number });//執行DataControl的Query方法 customer = result.First(); } return View(customer); }
<div class="text-center"> <h1 class="display-4">Welcome</h1> <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p> </div> <ul> <li>@Model.Number</li> <li>@Model.Name</li> <li>@Model.Tel</li> </ul>
public void linqInnerJoinExample() { var query = from product in products join product_ProductTypeRelationship in product_ProductTypeRelationships on product.Id equals product_ProductTypeRelationship.ProductID join productType in productTypes on product_ProductTypeRelationship.ProductTypeID equals productType.Id select new { product.Id, product.Name, product.Price, productTypeName = productType.Name }; var result = query.ToList(); }
結果:
說明:因為是InnerJoin，都要有重疊才會關聯出，而XBOX Series X的Id沒有在product_ProductTypeRelationships裡面，所以最後沒有被顯示出來。 基本上跟原生SQL的寫法挺像的。
public void linqLeftJoinExample() { // product 跟 product_ProductTypeRelationship 先left join var query1 = from product in products join product_ProductTypeRelationship in product_ProductTypeRelationships on product.Id equals product_ProductTypeRelationship.ProductID into tmp from product_ProductTypeRelationship in tmp.DefaultIfEmpty() select new { product.Id, product.Name, product.Price, ProductTypeID = product_ProductTypeRelationship?.ProductTypeID };
// query1結果再跟 productTypes left join var query2 = from q in query1 join productType in productTypes on q.ProductTypeID equals productType.Id into tmp from product_ProductTypeRelationship in tmp.DefaultIfEmpty() select new { q.Id, q.Name, q.Price, ProductName = product_ProductTypeRelationship?.Name }; var result = query2.ToList(); }
結果:
說明:可以得到XBOX Series X了。 因為LINQ沒有LEFT JOIN這種簡單作法，只能靠DefaultIfEmpty來操作，作法比較複雜、難直覺判斷。 ((ps.不是要用來讓大家好用的查詢嗎?搞複雜了
作法2:組多個DefaultIfEmpty
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
public void linqLeftJoinExample2() { var query = from a in products join b in product_ProductTypeRelationships on a.Id equals b.ProductID into result1 from ab in result1.DefaultIfEmpty() join c in productTypes on ab?.ProductTypeID equals c.Id into result2 from abc in result2.DefaultIfEmpty() select new{ a.Id, a.Name, a.Price, ProductName = abc?.Name } ; var result = query.ToList(); }
結果:
說明:可以得到XBOX Series X了。 這種多個DefaultIfEmpty組法感覺蠻難讀的。 要獨立出一個值，導致得用a、b、c來組不然難知道誰是誰，就很難閱讀 且要小心有些值讀出來是空的要使用「?.」的做法，把empty的值設定為null才有辦法繼續下去，否則會執行到中間就報錯了。
var p = (from a in products select new ProductViewModel { Id = a.Id, Name = a.Name, Price = a.Price, }).ToList();
foreach (var item in p) { var productTypeIDs = from a in product_ProductTypeRelationships where a.ProductID == item.Id select a.ProductTypeID;
var productTypeData = from a in productTypeIDs join b in productTypes on a equals b.Id select new ProductType { Id = b.Id, Name = b.Name }; item.ProductTypes = new List<ProductType>(); foreach (var x in productTypeData) { item.ProductTypes.Add(x); } } var result = p; string jsonData = JsonConvert.SerializeObject(result); System.Diagnostics.Debug.WriteLine(jsonData); }
Language Integrated Query (LINQ) 是一組以直接將查詢功能整合至 C# 語言為基礎之技術的名稱。 傳統上，資料查詢是以簡單的字串表示，既不會在編譯時進行類型檢查，也不支援 IntelliSense。 此外，您還必須針對每種資料來源類型學習不同的查詢語言：SQL 資料庫、XML 文件、各種 Web 服務等等。透過 LINQ，查詢會是第一級語言建構，和類別、方法及事件相同。 您可以使用語言關鍵字和熟悉的運算子，針對強型別的物件集合撰寫查詢。 LINQ 技術系列會針對物件 (LINQ to Object)、關聯式資料庫 (LINQ to SQL) 與 XML (LINQ to XML)，提供一致的查詢體驗。
using Microsoft.AspNetCore.Authorization; namespace CookieAuthentication.Authorization { public class PermissionAuthorizationRequirement : IAuthorizationRequirement { public string[] Permissions { get; set; }
public PermissionAuthorizationRequirement(string[] permissions) { Permissions = permissions; } } }
using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.Filters; using Microsoft.Extensions.DependencyInjection; using System; using System.Threading.Tasks;
namespace CookieAuthentication.Authorization { public class PermissionFilter:Attribute, IAsyncAuthorizationFilter { public string[] permissions { get; set; }
public PermissionFilter(params string[] permissions) { this.permissions = permissions; }
public async Task OnAuthorizationAsync(AuthorizationFilterContext context) { var authorizationService = context.HttpContext.RequestServices.GetRequiredService<IAuthorizationService>(); var authorizationResult = await authorizationService.AuthorizeAsync(context.HttpContext.User, null, new PermissionAuthorizationRequirement(permissions)); if (!authorizationResult.Succeeded) { // 如果授權失敗，設定為未授權 context.Result = new UnauthorizedResult(); } } } }
說明: 重點在21～27，使用authorizationService.AuthorizeAsync去認證權限，就會把資料傳到我們上面建立的PermissionAuthorizationHandler.cs裡面判斷。 如果最後判斷沒權限則設定context.Result = new UnauthorizedResult();表示禁止。
using CookieAuthentication.Authorization; using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using System; namespace CookieAuthentication { 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. public void ConfigureServices(IServiceCollection services) { // 註冊需求和處理常式，套用自訂權限控制器 services.AddSingleton<IAuthorizationHandler, PermissionAuthorizationHandler>();
// 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(); } else { app.UseExceptionHandler("/Home/Error"); // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts. app.UseHsts(); } app.UseHttpsRedirection(); app.UseStaticFiles();
using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.HttpsPolicy; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks;
namespace CookieAuthentication { 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. public void ConfigureServices(IServiceCollection services) {
// 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(); } else { app.UseExceptionHandler("/Home/Error"); // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts. app.UseHsts(); } app.UseHttpsRedirection(); app.UseStaticFiles();
using CookieAuthentication.Models; using Microsoft.AspNetCore.Authentication; using Microsoft.AspNetCore.Authentication.Cookies; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.Logging; using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Security.Claims; using System.Text; using System.Threading.Tasks;
namespace CookieAuthentication.Controllers {
public class HomeController : Controller { private readonly ILogger<HomeController> _logger;
public HomeController(ILogger<HomeController> logger) { _logger = logger; }
public IActionResult Index() { return View(); }
[Authorize] public IActionResult Privacy() { StringBuilder sb = new StringBuilder(); sb.AppendLine("<ul>");
<div class="text-center"> <h1 class="display-4">Welcome</h1> <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p> </div>
using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using WebApplication1.Models;
namespace WebApplication1.ViewComponents { public class ShowUserInfo : ViewComponent { public async Task<IViewComponentResult> InvokeAsync(string number, string name,string tel) { Customer customer = new Customer(); customer.Name = name; customer.Number = number; customer.Tel = tel; return View(customer); } } }
下圖顯示完整的查詢作業。
