Blazor实战——Known框架多表增删改查

多表增删改查示例

本章介绍学习多张表增、删、改、查功能如何实现，下面以销货出库单作为示例，该业务栏位如下：

销货出库单栏位

• 销货单号、销货日期、状态、客户、备注

销货出库单明细栏位

• 商品编码、商品名称、规格型号、数量、单位、单价、金额

该示例适用于出货明细数量较小情况，单据表头和表体组合查询和提交。

对于出货明细数量较大的情况，建议表头与表体分开查询和提交，表体采用分页查询。

1. 前后端共用

1.1. 创建实体类

• 在KIMS项目Entities文件夹下创建KmBill和KmBillList实体类
• 该类继承EntityBase类
• 属性使用Column特性描述，用于生成页面字段和数据校验

//销货出库单 public class KmBill : EntityBase {     [Column("销货单号", "", true, "1", "50")]     public string? BillNo { get; set; }     ......     [Column("客户", "", true, "1", "50")]     public string? BillDate { get; set; }     [Column("备注", "", false)]     public string? Note { get; set; }      //出货明细，为了降低代码量，扩展实体类，不再创建DTO类     //此处使用虚属性，ORM映射SQL时忽略该属性     public virtual List<KmBillList>? Lists { get; set; } } //销货出库单明细 public class KmBillList : EntityBase {     [Column("销货单ID", "", true, "1", "50")]     public string? HeadId { get; set; }     [Column("商品编码", "", true, "1", "50")]     public string? Code { get; set; }     [Column("数量", "", false)]     public decimal? Qty { get; set; }     [Column("单价", "", false)]     public decimal? Price { get; set; }     [Column("金额", "", false)]     public decimal? Amount { get; set; }      //如下虚属性用于关联商品资料表查询显示     public virtual string? Name { get; set; }  //商品名称     public virtual string? Model { get; set; } //规格型号     public virtual string? Unit { get; set; }   //计量单位 } 

1.2. 创建Client类

• 在KIMS项目Clients文件夹下创建BillClient类
• 该类是前后端数据交互接口，继承ClientBase类
• 该类只需提供分页查询、删除和保存，导入功能由框架统一异步处理

public class BillClient : ClientBase {     public BillClient(Context context) : base(context) { }      public Task<PagingResult<KmBill>> QueryBillsAsync(PagingCriteria criteria) => Context.QueryAsync<KmBill>("Bill/QueryBills", criteria);     public Task<Result> DeleteBillsAsync(List<KmBill> models) => Context.PostAsync("Bill/DeleteBills", models);     public Task<KmBill> GetBillAsync(string id) => Context.GetAsync<KmBill>($"Bill/GetBill?id={id}");     public Task<Result> SaveBillAsync(KmBill model) => Context.PostAsync("Bill/SaveBill", model); } 

2. 前端

2.1. 创建List页面

• 在KIMS.Razor项目BillData文件夹下创建BillList类
• 该类是数据列表页面，继承WebGridView<KmBill, BillForm>类
• 列表页面按钮和栏位在框架模块管理中配置

class BillList : WebGridView<KmBill, BillForm> {     protected override Task InitPageAsync()     {         //表格栏位格式化显示         //销货单号链接，点击显示销货单查看表单         Column(c => c.BillNo).Template((b, r) => b.Link(r.BillNo, Callback(() => View(r))));         Column(c => c.Status).Template(BillStatusCell);//显示状态标签         return base.InitPageAsync();     }      //分页查询     protected override Task<PagingResult<KmBill>> OnQueryData(PagingCriteria criteria)     {         return Client.Bill.QueryBillsAsync(criteria);     }          public void New() => ShowForm();//新增按钮方法     public void DeleteM() => DeleteRows(Client.Bill.DeleteBillsAsync);//批量删除按钮方法     public void Edit(KmBill row) => ShowForm(row);//编辑操作方法     public void Delete(KmBill row) => DeleteRow(row, Client.Bill.DeleteBillsAsync);//删除操作方法 } 

2.2. 创建Form页面

• 在KIMS.Razor项目BillDataForms文件夹下创建BillForm类
• 该类是数据编辑和查看明细页面，继承WebForm类

[Dialog(980, 580)]//设置对话框大小 class BillForm : WebForm<KmBill> {     private KmBill? head;     //初始化表单，查询表头表体组合数据     protected override async Task InitFormAsync()     {         var model = TModel;         head = await Client.Bill.GetBillAsync(model.Id);     }     //表单布局     protected override void BuildFields(FieldBuilder<KmBill> builder)     {         builder.Hidden(f => f.Id);//隐藏字段         builder.Table(table =>         {             table.ColGroup(11, 25, 11, 25, 11, 17);             table.Tr(attr =>             {                 //销货单号不可编辑，自动生成编号                 table.Field<Text>(f => f.BillNo).Enabled(false).Build();                 table.Field<Date>(f => f.BillDate).Build();                 table.Field<Text>(f => f.Status).Enabled(false).Build();             });             table.Tr(attr => table.Field<TextArea>(f => f.Note).ColSpan(5).Build());             builder.FormList<BillListGrid>("商品明细", "", attr =>             {                 attr.Set(c => c.ReadOnly, ReadOnly)                     .Set(c => c.Data, head?.Lists);//设置表体数据             });         });     }     //表单底部按钮     protected override void BuildButtons(RenderTreeBuilder builder)     {         builder.Button(FormButton.Save, Callback(OnSave), !ReadOnly);         base.BuildButtons(builder);     }     //保存按钮方法     private void OnSave()     {         SubmitAsync(data =>         {             head?.FillModel(data);//自动填充表单修改数据             return Client.Bill.SaveBillAsync(head);         });     } } 

2.3. 创建表体页面

• 在KIMS.Razor项目BillDataForms文件夹下创建BillListGrid类
• 该类是表体数据编辑表格，继承EditGrid类

//可编辑表体组件 class BillListGrid : EditGrid<KmBillList> {     public BillListGrid()     {         OrderBy = nameof(KmBillList.ItemNo);//默认排序         Name = "商品明细";     }     //初始化表格栏位     protected override Task OnInitializedAsync()     {         //如下栏位有Edit方法为可编辑栏位，否则不可编辑         var builder = new ColumnBuilder<KmBillList>();         //商品库存选择器，弹出对话框查询商品库存，双击选择要出库的商品         builder.Field(r => r.Code).Edit(new GoodsStock(), OnCodeChanged);         builder.Field(r => r.Name);//不可编辑         builder.Field(r => r.Model);         builder.Field(r => r.Qty).Edit<Number>(OnQtyChanged);//可编辑         builder.Field(r => r.Unit);         builder.Field(r => r.Price).Edit<Number>(OnPriceChanged);         builder.Field(r => r.Amount).IsSum().Edit<Number>(OnAmountChanged);         builder.Field(r => r.Note).Edit();         Columns = builder.ToColumns();         return base.OnInitializedAsync();     }     //切换商品编码，自动带出商品信息     private void OnCodeChanged(KmBillList row, object value)     {         var g = value as StockInfo;         row.Type = g?.Type;         row.Code = g?.Code;         row.Name = g?.Name;         row.Model = g?.Model;         row.Unit = g?.Unit;     }     //更改数量，自动计算金额     private void OnQtyChanged(KmBillList row, object value)     {         var qty = Utils.ConvertTo<decimal>(value);         row.Amount = Utils.Round(qty * (row.Price ?? 0), 2);     }     //更改单价，自动计算金额     private void OnPriceChanged(KmBillList row, object value)     {         var price = Utils.ConvertTo<decimal>(value);         row.Amount = Utils.Round(price * row.Qty, 2);     }     //更改金额，自动计算单价     private void OnAmountChanged(KmBillList row, object value)     {         var amount = Utils.ConvertTo<decimal>(value);         row.Price = row.Qty == 0 ? 0 : Utils.Round(amount / row.Qty, 2);     } } 

3. 后端

3.1. 创建Controller类

• 在KIMS.Core项目Controllers文件夹下创建BillController类
• 该类为服务端WebApi，继承BaseController类

[Route("[controller]")] public class BillController : BaseController {     private BillService Service => new(Context);      [HttpPost("[action]")]     public PagingResult<KmBill> QueryBills([FromBody] PagingCriteria criteria) => Service.QueryBills(criteria);      [HttpPost("[action]")]     public Result DeleteBills([FromBody] List<KmBill> models) => Service.DeleteBills(models);      [HttpGet("[action]")]     public KmBill GetBill([FromQuery] string id) => Service.GetBill(id);          [HttpPost("[action]")]     public Result SaveBill([FromBody] KmBill model) => Service.SaveBill(model); } 

3.2. 创建Service类

• 在KIMS.Core项目Services文件夹下创建BillService类
• 该类为业务逻辑服务类，继承ServiceBase类

class BillService : ServiceBase {     internal BillService(Context context) : base(context) { }     //分页查询     internal PagingResult<KmBill> QueryBills(PagingCriteria criteria)     {         return BillRepository.QueryBills(Database, criteria);     }     //删除数据     internal Result DeleteBills(List<KmBill> models)     {         if (models == null || models.Count == 0)             return Result.Error(Language.SelectOneAtLeast);          //此处增加删除数据校验         return Database.Transaction(Language.Delete, db =>         {             foreach (var item in models)             {                 //删除表体                 BillRepository.DeleteBillLists(db, item.Id);                 db.Delete(item);             }         });     }     //获取组合数据     internal KmBill GetBill(string id)     {         if (string.IsNullOrEmpty(id))//id为空返回默认值             return GetDefaultBill();          var entity = Database.QueryById<KmBill>(id);         if (entity == null)//为空返回默认值             entity = GetDefaultBill();         else//否则组装表体数据返回             entity.Lists = BillRepository.GetBillLists(Database, id);         return entity;     }     //保存数据     internal Result SaveBill(KmBill model)     {         if (model == null)             return Result.Error("不能提交空数据！");          var vr = model.Validate();//验证输入栏位         if (!vr.IsValid)             return vr;          return Database.Transaction(Language.Save, db =>         {             if (model.IsNew)                 model.BillNo = GetBillMaxNo(db);              //清空表体合计数据             model.TotalAmount = 0;             model.GoodsName = string.Empty;             //先删除表体，再插入表体             BillRepository.DeleteBillLists(db, model.Id);             if (model.Lists != null && model.Lists.Count > 0)             {                 var index = 0;                 var lists = new List<KmBillList>();                 foreach (var item in model.Lists)                 {                     item.HeadId = model.Id;                     item.ItemNo = ++index;                     db.Insert(item);                     lists.Add(item);                 }                 //合计表体数据                 model.TotalAmount = lists.Sum(l => l.Amount);                 model.GoodsName = string.Join(",", lists.Select(l => l.Name));             }             db.Save(model);         }, model);     }     //获取默认表头     private KmBill GetDefaultBill()     {         return new KmBill         {             BillNo = GetBillMaxNo(Database),             BillDate = DateTime.Now,             Status = "暂存",             Lists = new List<JxBillList>()         };     }     //获取销货单最大编号     private static string GetBillMaxNo(Database db)     {         var prefix = $"S{DateTime.Now:yyyy}";         var maxNo = BillRepository.GetBillMaxNo(db, prefix);         if (string.IsNullOrWhiteSpace(maxNo))             maxNo = $"{prefix}00000";         return GetMaxFormNo(prefix, maxNo);     } } 

3.3. 创建Repository类

• 在KIMS.Core项目Repositories文件夹下创建BillRepository类
• 该类为数据访问类

class BillRepository {     //Head     //分页查询     internal static PagingResult<KmBill> QueryBills(Database db, PagingCriteria criteria)     {         var sql = "select * from KmBill where CompNo=@CompNo";         return db.QueryPage<KmBill>(sql, criteria);//查询条件自动绑定     }     //获取销货单最大编号     internal static string GetBillMaxNo(Database db, string prefix)     {         var sql = $"select max(BillNo) from KmBill where CompNo=@CompNo and BillNo like '{prefix}%'";         return db.Scalar<string>(sql, new { db.User.CompNo });     }     //List     //根据表头ID获取表体数据     internal static List<KmBillList> GetBillLists(Database db, string headId)     {         //关联商品资料表查询商品信息         var sql = "select a.*,b.Name,b.Model,b.Unit from KmBillList a,KmGoods b where a.Code=b.Code and HeadId=@headId";         return db.QueryList<KmBillList>(sql, new { headId });     }     //根据表头ID删除表体数据     internal static void DeleteBillLists(Database db, string headId)     {         var sql = "delete from KmBillList where HeadId=@headId";         db.Execute(sql, new { headId });     } } 

4. 运行测试

• 运行效果如下