记开发个人图书收藏清单小程序开发(六)Web开发
Web页面开发暂时是没有问题了,现在开始接上Ptager.BL的DB部分。
首先需要初始化用户和书房信息。因为还没有给其他多余的设计,所以暂时只有个人昵称和书房名称。
添加 Init Razor Pages(/Pages/Shelves/Init) 。
/Pages/Shelves/Init.cshtml
1 @page 2 @model InitModel 3 @{ 4 ViewData["Title"] = "Shelf Init"; 5 } 6 7 <nav aria-label="breadcrumb"> 8 <ol class="breadcrumb"> 9 <li class="breadcrumb-item"><a asp-page="/Index">Home</a></li> 10 <li class="breadcrumb-item"><a asp-page="/My Books/Index">My Books</a></li> 11 <li class="breadcrumb-item active" aria-current="page">Bookcase Init</li> 12 </ol> 13 </nav> 14 15 16 <form method="post"> 17 <div class="form-group form-group-lg"> 18 <label asp-for="Input.NickName"></label> 19 <input class="form-control form-control-lg" asp-for="Input.NickName" autocomplete="off"> 20 </div> 21 <div class="form-group form-group-lg"> 22 <label asp-for="Input.ShelfName"></label> 23 <input class="form-control form-control-lg" asp-for="Input.ShelfName" autocomplete="off"> 24 </div> 25 <div class="form-group text-right"> 26 <button class="btn btn-warning btn-lg" type="submit">Save</button> 27 </div> 28 </form>
现在去实现底层Repo的部分。
需要在BL层添加基本的底层代码:
在PTager.BL.Core层增加DataContract、Models和Repos Interface。
暂时还没有用到查询,所以只需要增加Init Model和Repo Interface就好。
新增Shelf.InitSpec Model文件:
Shelf.InitSpec.cs
1 namespace PTager.BL 2 { 3 public partial class Shelf 4 { 5 public class InitSpec 6 { 7 public string NickName { get; set; } 8 public string ShelfName { get; set; } 9 } 10 } 11 }
Install Package Newtonsoft.Json
新增_module扩展文件:
_module.cs
1 namespace PTager.BL 2 { 3 [System.Diagnostics.DebuggerNonUserCode] 4 public static partial class extOC 5 { 6 public static string ToJson<T>(this T me) where T : class 7 => JsonConvert.SerializeObject(me); 8 } 9 }
并且引用Projects:Ptager.BL
新增IShelfRepo:
IShelfRepo.cs
1 using System.Threading.Tasks; 2 3 namespace PTager.BL 4 { 5 using M = Shelf; 6 public interface IShelfRepo 7 { 8 Task Init(M.InitSpec spec); 9 } 10 }
在PTager.BL.Data层增加Store和Repos的实现。
引用Projects:Ptager.BL和PTager.BL.Core,并且添加Nuget Package:System.Data.SqlClient、Microsoft.EntityFrameworkCore.Relational
其中Store的内容是模仿Linq to SQL的分层做的,但没那么高的性能,勉强够用而已。
RepoBase.cs
1 namespace PTager.BL.Data 2 { 3 public class RepoBase 4 { 5 protected readonly BLDbContext _context; 6 public RepoBase(BLDbContext context) 7 { 8 _context = context; 9 } 10 } 11 }
_module.cs
1 namespace PTager.BL.Data 2 { 3 [System.Diagnostics.DebuggerNonUserCode] 4 public static partial class extBL { } 5 }
BLDbContext.cs
1 using System.Threading.Tasks; 2 3 namespace PTager.BL.Data.Store 4 { 5 public class BLDbContext : DbContext 6 { 7 public BLDbContext(DbContextOptions<BLDbContext> options) 8 : base(options) 9 { 10 this.ChangeTracker.AutoDetectChangesEnabled = false; 11 } 12 13 #region { Functions } 14 15 #endregion 16 17 #region { Actions } 18 19 public async Task Shelf_Init(string json) 20 => await this.ExecuteMethodCallAsync(nameof(Shelf_Init), args: json); 21 22 #endregion 23 } 24 }
BLDbContext.ext.cs(这是目前代码中最脏的部分了,找时间需要优化这个部分)
1 using Microsoft.EntityFrameworkCore; 2 using System; 3 using System.Data; 4 using System.Data.SqlClient; 5 using System.Linq; 6 using System.Reflection; 7 using System.Threading.Tasks; 8 9 namespace PTager.BL.Data.Store 10 { 11 public static partial class extUSDContext 12 { 13 public static IQueryable<T> CreateMethodCallQuery<T>(this DbContext me, MethodBase method, string schema = "svc") 14 where T : class 15 => me.CreateMethodCallQuery<T>(method, schema, null); 16 public static IQueryable<T> CreateMethodCallQuery<T>(this DbContext me, MethodBase method, string schema = "svc", params object[] args) 17 where T : class 18 { 19 var hasArgs = args != null && args.Length > 0; 20 //var fields = typeof(T).GetType().ToSelectFields(); 21 var sql = $"select * from {schema}.{method.Name.Replace("_", "$")}("; 22 if (hasArgs) sql += string.Join(", ", args.Select(x => $"@p{args.ToList().IndexOf(x)}")); 23 sql += ")"; 24 me.Database.SetCommandTimeout(TimeSpan.FromMinutes(30)); 25 return hasArgs ? me.Set<T>().FromSql(sql, args) : me.Set<T>().FromSql(sql); 26 } 27 28 public static async Task<string> ExecuteMethodCallAsync(this DbContext me, string methodName) 29 => await me.ExecuteMethodCallAsync(methodName, null); 30 public static async Task<string> ExecuteMethodCallAsync(this DbContext me, string methodName, params object[] args) 31 => await me.ExecuteMethodCallAsync(methodName, false, args); 32 public static async Task<string> ExecuteMethodCallAsync(this DbContext me, string methodName, bool lastOutput, params object[] args) 33 { 34 var hasArgs = args != null && args.Length > 0; 35 var sql = $"svc.{methodName.Replace("_", "$")} "; 36 if (!hasArgs) 37 { 38 if (lastOutput == false) 39 { 40 await me.Database.ExecuteSqlCommandAsync(sql); 41 return string.Empty; 42 } 43 } 44 else 45 { 46 sql += string.Join(", ", args.Select(x => $"@p{args.ToList().IndexOf(x)}")); 47 if (lastOutput == false) 48 { 49 await me.Database.ExecuteSqlCommandAsync(sql, args); 50 return string.Empty; 51 } 52 sql += ", "; 53 } 54 sql += " @result output"; 55 var parameters = args.Select(x => new SqlParameter($"@p{args.ToList().IndexOf(x)}", x)).ToList(); 56 var outParam = new SqlParameter("@result", SqlDbType.VarChar, int.MaxValue) 57 { 58 Value = string.Empty, 59 Direction = ParameterDirection.Output 60 }; 61 parameters.Add(outParam); 62 me.Database.SetCommandTimeout(TimeSpan.FromMinutes(30)); 63 await me.Database.ExecuteSqlCommandAsync(sql, parameters.ToArray()); 64 return outParam.Value.ToString(); 65 } 66 } 67 }
好了,基础已建好,新增ShelfRepo的实现。
ShelfRepo.cs
1 namespace PTager.BL.Data.Repos 2 { 3 using System.Threading.Tasks; 4 using PTager.BL.Data.Store; 5 using M = Shelf; 6 public class ShelfRepo : RepoBase, IShelfRepo 7 { 8 public ShelfRepo(BLDbContext context) : base(context) 9 { 10 } 11 12 public async Task Init(M.InitSpec spec) 13 => await _context.Shelf_Init(spec.ToJson()); 14 } 15 }
在WebUI的Project中,引用新增的三个底层项目,并在Statup.cs的ConfigureServices中注册IShelfRepo的依赖和BL DB的连接注册:
1 services.AddDbContext<BLDbContext>(options => 2 options.UseSqlServer( 3 Configuration.GetConnectionString("BLConnection"))); 4 services.AddScoped<IShelfRepo, ShelfRepo>();
当然,这个时会报错的,因为我们并没有在appsetting中添加这个DB Connection。
修改后的appsettings.json
1 { 2 "ConnectionStrings": { 3 "DefaultConnection": "Server=.\\SQL2017;Database=PTager;Trusted_Connection=True;MultipleActiveResultSets=true", 4 "BLConnection": "Server=.\\SQL2017;Database=PTagerBL;Trusted_Connection=True;MultipleActiveResultSets=true" 5 }, 6 "Logging": { 7 "LogLevel": { 8 "Default": "Warning" 9 } 10 }, 11 "AllowedHosts": "*" 12 }
接下来需要去新建DB的Script实现这个Shelf_Init功能了。