多表增删改查示例

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

销货出库单栏位

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

销货出库单明细栏位

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

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

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

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项目BillData\Forms文件夹下创建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项目BillData\Forms文件夹下创建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)
                {
                    if (isReturn && item.Qty == 0)
                        continue;

                    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 });
    }
}