ORM-SqlRepoEx 是 .Net平臺下相容.NET Standard 2.0，一個實現以Lambda表達式轉轉換標準SQL語句，使用強類型操作數據的輕量級ORM工具，在減少魔法字串同時，通過靈活的Lambda表達式組合，實現業務數據查詢的多樣性。 ...
ORM-SqlRepoEx 是 .Net平臺下相容.NET Standard 2.0，一個實現以Lambda表達式轉轉換標準SQL語句，使用強類型操作數據的輕量級ORM工具，在減少魔法字串同時，通過靈活的Lambda表達式組合，實現業務數據查詢的多樣性。
ORM-SqlRepoEx 也是一個極易使用的工具，通過在AspNetCore中的應用可以展示。
本案例源碼在：
https://github.com/AzThinker/SqlRepoEx2.0DemoForAspCore
或
https://gitee.com/azthinker/SqlRepoEx2.0DemoForAspCore
源碼部分代碼是使用代碼工具生成
https://github.com/AzThinker/CodeToolSolution
1、新建一個AspNetCore項目
2、通過Nuget下載SqlRepoEx庫、由於本例中是AspNetCore.Mvc項目，案例中使用的是SQL Server的Northwind資料庫，所以選擇下載
SqlRepoEx.MsSql.ServiceCollection
3、在Startup.cs文件的public void ConfigureServices(IServiceCollection services)中添加
string ConnectionString = "Data Source=(Local);Initial Catalog=Northwind;User ID=test;Password=test";
services.AddSimpleSqlRepo(ConnectionString);
4、增加一個簡單類AzCustomers，其屬性來源於 Customers 表。為使SqlRepoEx 精準訪問，增加特性標識 [TableName("Customers")] 。
1 using System; 2 using SqlRepoEx.Core.CustomAttribute; 3 4 // 客戶 業務類 5 namespace DemoTools.BLL.DemoNorthwind 6 { 7 [TableName("Customers")] 8 /// <summary> 9 /// 客戶 業務類 10 /// </summary> 11 public sealed class AzCustomers 12 { 13 public string CustomerID { get; set; } 14 15 public string CompanyName { get; set; } 16 17 public string ContactName { get; set; } 18 19 public string ContactTitle { get; set; } 20 21 public string Address { get; set; } 22 23 public string City { get; set; } 24 25 public string Region { get; set; } 26 27 public string PostalCode { get; set; } 28 29 public string Country { get; set; } 30 31 public string Phone { get; set; } 32 33 public string Fax { get; set; } 34 35 } 36 }View Code
5、增加一人簡單的列表類 AzCustomersList，其中實現了IPagedList介面，此介面是Webdiyer.WebControls.AspNetCore分頁控制項中定義，由於Webdiyer.WebControls.AspNetCore的源碼不支持core2.1，所以重新編譯，並將源碼加工程中。
using System; using System.Collections.Generic; using Webdiyer.WebControls.AspNetCore; //客戶列表類 namespace DemoTools.BLL.DemoNorthwind { /// <summary> /// 客戶 列表類 /// </summary> public class AzCustomersList : List<AzCustomers>, IPagedList { public string DisplayDescription = "客戶"; public int PageSize { get; set; } public int TotalItemCount { get; set; } public int CurrentPageIndex { get; set; } public static AzCustomersList GetModelList((IEnumerable<AzCustomers> QueryResult, int PageCount) queryresult, int pageSize, int currentPageIndex) { AzCustomersList models = new AzCustomersList(); models.AddRange(queryresult.QueryResult); models.TotalItemCount = queryresult.PageCount; models.PageSize = pageSize; models.CurrentPageIndex = currentPageIndex; return models; } } }View Code
6、增加一個控制器併在控制器的構造方法 AzCustomersController(IRepositoryFactory repositoryFactory)，IRepositoryFactory是SqlRepoEx 工廠類的介面，由於前面（第2條中）已經註冊了SqlRepoEx 所需的依賴，此處僅需在構造中加入此介面即可。
1 using System.Linq; 2 using DemoTools.BLL.DemoNorthwind; 3 using Microsoft.AspNetCore.Authorization; 4 using Microsoft.AspNetCore.Mvc; 5 using SqlRepoEx.Abstractions; 6 7 // 客戶 控制器 8 namespace DemoTools.WebUI.DemoNorthwind.Controllers 9 { 10 /// <summary> 11 /// 客戶 12 /// </summary> 13 public class AzCustomersController : Controller 14 { 15 IRepositoryFactory repositoryFactory; 16 IRepository<AzCustomers> repository; 17 public AzCustomersController(IRepositoryFactory repositoryFactory) 18 { 19 this.repositoryFactory = repositoryFactory; 20 this.repository = repositoryFactory.Create<AzCustomers>(); 21 } 22 23 /// <summary> 24 /// 返回 客戶 列表 25 /// 非同步調用數據,其非同步部分明細View沒有Controller只有View 26 /// </summary> 27 public IActionResult Index(int pageindex = 1) 28 { 29 var queryresult = repository.Query() 30 .Select(s => s.CustomerID 31 , s => s.CompanyName 32 , s => s.ContactName 33 , s => s.ContactTitle 34 , s => s.Address 35 , s => s.City 36 , s => s.Region 37 , s => s.PostalCode 38 , s => s.Country 39 , s => s.Phone 40 , s => s.Fax 41 ).OrderBy(o => o.CustomerID).Page(20, pageindex).PageGo(); 42 var model = AzCustomersList.GetModelList(queryresult, 20, pageindex); 43 string xrh = Request.Headers["X-Requested-With"]; 44 if (!string.IsNullOrEmpty(xrh) && xrh.Equals("XMLHttpRequest", System.StringComparison.OrdinalIgnoreCase)) 45 { 46 return PartialView("DetailsPage", model); 47 } 48 return View(model); 49 } 50 51 /// <summary> 52 /// 增加客戶 53 /// </summary> 54 public ActionResult Create() 55 { 56 var model = new AzCustomers(); 57 return View(model); 58 } 59 60 /// <summary> 61 /// 增加保存客戶 62 /// </summary> 63 [HttpPost, ValidateAntiForgeryToken] 64 [ActionName("Create")] 65 public IActionResult CreatePost(AzCustomers model) 66 { 67 if (ModelState.IsValid) 68 { 69 repository.Insert().With(s => s.CustomerID, model.CustomerID) 70 .With(s => s.CompanyName, model.CompanyName) 71 .With(s => s.ContactName, model.ContactName) 72 .With(s => s.ContactTitle, model.ContactTitle) 73 .With(s => s.Address, model.Address) 74 .With(s => s.City, model.City) 75 .With(s => s.Region, model.Region) 76 .With(s => s.PostalCode, model.PostalCode) 77 .With(s => s.Country, model.Country) 78 .With(s => s.Phone, model.Phone) 79 .With(s => s.Fax, model.Fax) 80 .Go();//按增加保存 81 return RedirectToAction("Index"); 82 } 83 return View(model); 84 } 85 86 /// <summary> 87 /// 編輯客戶 88 /// </summary> 89 public IActionResult Edit(string Id) 90 { 91 var model = repository.Query() 92 .Select(s => s.CustomerID 93 , s => s.CompanyName 94 , s => s.ContactName 95 , s => s.ContactTitle 96 , s => s.Address 97 , s => s.City 98 , s => s.Region 99 , s => s.PostalCode 100 , s => s.Country 101 , s => s.Phone 102 , s => s.Fax 103 ).Where(s => s.CustomerID == Id).Go().FirstOrDefault(); 104 return View(model); 105 } 106 107 /// <summary> 108 /// 保存編輯的客戶 109 /// </summary> 110 [HttpPost, ValidateAntiForgeryToken] 111 [ActionName("Edit")] 112 public IActionResult EditPost(AzCustomers model) 113 { 114 if (ModelState.IsValid) 115 { 116 repository.Update().Set(s => s.CustomerID, model.CustomerID) 117 .Set(s => s.CompanyName, model.CompanyName) 118 .Set(s => s.ContactName, model.ContactName) 119 .Set(s => s.ContactTitle, model.ContactTitle) 120 .Set(s => s.Address, model.Address) 121 .Set(s => s.City, model.City) 122 .Set(s => s.Region, model.Region) 123 .Set(s => s.PostalCode, model.PostalCode) 124 .Set(s => s.Country, model.Country) 125 .Set(s => s.Phone, model.Phone) 126 .Set(s => s.Fax, model.Fax) 127 .Go();//按增加保存 128 return RedirectToAction("Index"); 129 } 130 return View(model); 131 } 132 133 /// <summary> 134 /// 顯示客戶單個記錄 135 /// </summary> 136 public IActionResult Details(string Id) 137 { 138 var model = repository.Query() 139 .Select(s => s.CustomerID 140 , s => s.CompanyName 141 , s => s.ContactName 142 , s => s.ContactTitle 143 , s => s.Address 144 , s => s.City 145 , s => s.Region 146 , s => s.PostalCode 147 , s => s.Country 148 , s => s.Phone 149 , s => s.Fax 150 ).Where(s => s.CustomerID == Id).Go().FirstOrDefault(); 151 return View(model); 152 } 153 154 /// <summary> 155 /// 獨立頁面刪除客戶 156 /// </summary> 157 public ActionResult Delete(string Id) 158 { 159 var model = repository.Query() 160 .Select(s => s.CustomerID 161 , s => s.CompanyName 162 , s => s.ContactName 163 , s => s.ContactTitle 164 , s => s.Address 165 , s => s.City 166 , s => s.Region 167 , s => s.PostalCode 168 , s => s.Country 169 , s => s.Phone 170 , s => s.Fax 171 ).Where(s => s.CustomerID == Id).Go().FirstOrDefault(); 172 return View(model); 173 } 174 175 /// <summary> 176 /// 獨立頁面刪除客戶 177 /// </summary> 178 [HttpPost, ActionName("Delete")] 179 public IActionResult DeleteConfirmed(AzCustomers model) 180 { 181 repository.Delete().Where(c => c.CustomerID == model.CustomerID).Go(); 182 return RedirectToAction("Index"); 183 } 184 185 } 186 }View Code
7、View的實現和其他代碼參見上面給出的地址中的源碼。
總結：從上面看出，對特SqlRepoEx 所需要特定的操作，僅在第2、第3、第6中是必需的
（1）、引用SqlRepoEx.MsSql.ServiceCollection
（2）、 services.AddSimpleSqlRepo(ConnectionString);
（3）、 AzCustomersController(IRepositoryFactory repositoryFactory)
（4）、this.repository = repositoryFactory.Create<AzCustomers>();
然後就可以輕鬆的通過SqlRepoEx 訪問資料庫了。
