1.8.0 新功能 BeginEdit 编辑数据功能讨论

[2881099]

using (var ctx = fsql.CreateDbContext())
{
    var setTag = ctx.Set<Tag>();
    var tags = setTag.Select.Limit(10).ToList();

    setTag.BeginEdit(tags); //开始对 tags 进行编辑

    tags.Add(new Tag
    {
        Ddd = DateTime.Now.Second,
        Name = "test_manytoMany_01_中国2234234"
    });
    tags[0].Name = "123123";
    tags.RemoveAt(1);

    var affrows = setTag.EndEdit(); //执行保存，返回影响的行
    Assert.Equal(3, affrows );
}

上面的代码在 EndEdit 方法执行的时候产生 3 条 SQL 如下：

INSERT INTO "Tag"("Parent_id", "Ddd", "Name") 
VALUES(NULL, 41, 'test_manytoMany_01_中国2234234'); SELECT last_insert_rowid();


UPDATE "Tag" SET "Name" = '123123' 
WHERE ("Id" = 1)


DELETE FROM "Tag" WHERE ("Id" = 2)

场景：winform 加载表数据后，一顿添加、修改、删除操作之后，点击【保存】

提醒：该操作只对 tags 有效，不是针对全表对比更新。

[densen2014]

666, 重构winfom的我正好用得上

[mekumiao]

666.很实用的功能

[pigwing]

如果存在脏数据呢?会有相关异常吗?例如有另外的用户把你要修改的那条记录提前给删除了.

[2881099]

如果存在脏数据呢?会有相关异常吗?例如有另外的用户把你要修改的那条记录提前给删除了.

如果有这个要求，可以用事务级别来控制，把行锁(for update)住再操作，最高可以锁表。

[2881099]

仓储 BeginEdit/EndEdit 测试代码：

[Fact]
public void BeginEdit()
{
    fsql.Delete<BeginEdit01>().Where("1=1").ExecuteAffrows();
    var repo = fsql.GetRepository<BeginEdit01>();
    var cts = new[] {
        new BeginEdit01 { Name = "分类1" },
        new BeginEdit01 { Name = "分类1_1" },
        new BeginEdit01 { Name = "分类1_2" },
        new BeginEdit01 { Name = "分类1_3" },
        new BeginEdit01 { Name = "分类2" },
        new BeginEdit01 { Name = "分类2_1" },
        new BeginEdit01 { Name = "分类2_2" }
    }.ToList();
    repo.Insert(cts);

    repo.BeginEdit(cts);

    cts.Add(new BeginEdit01 { Name = "分类2_3" });
    cts[0].Name = "123123";
    cts.RemoveAt(1);

    Assert.Equal(3, repo.EndEdit());
}
class BeginEdit01
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

执行的 SQL：

INSERT INTO "BeginEdit01"("Id", "Name") 
VALUES('5f26bf00-6ac3-cbe8-00da-7dd01be76e26', '分类1'), 
('5f26bf00-6ac3-cbe8-00da-7dd11bcf54dc', '分类1_1'), 
('5f26bf00-6ac3-cbe8-00da-7dd205be550b', '分类1_2'), 
('5f26bf00-6ac3-cbe8-00da-7dd3783095b1', '分类1_3'), 
('5f26bf00-6ac3-cbe8-00da-7dd41cad690f', '分类2'), 
('5f26bf00-6ac3-cbe8-00da-7dd549f45025', '分类2_1'),
 ('5f26bf00-6ac3-cbe8-00da-7dd6039c34d2', '分类2_2')


INSERT INTO "BeginEdit01"("Id", "Name") VALUES('5f26bf07-6ac3-cbe8-00da-7dd74818c3a6', '分类2_3')


UPDATE "BeginEdit01" SET "Name" = '123123' 
WHERE ("Id" = '5f26bf00-6ac3-cbe8-00da-7dd01be76e26')


DELETE FROM "BeginEdit01" WHERE ("Id" = '5f26bf00-6ac3-cbe8-00da-7dd11bcf54dc')

[densen2014]

好像有bug,
1.添加10条 => 影响行数 0
2.移除5条 => 影响行数 5
3.移除全部 => 影响行数 5
4.添加10条 => 影响行数 0
5.再执行一次 => 影响行数 10

补充测试结果:
原始有记录的一切正常,当_orderDetails清空一次后,就bug了

每次repos.EndEdit() 后 都跟了 repos.BeginEdit(_orderDetails)

Friend _orderDetails As List(Of OrderDetailsLite)

dim repos = fsql.GetGuidRepository(Of OrderDetailsLite)
_orderDetails = repos.Where(Function(a) a.OrderID = _OrderID).ToList()
repos.BeginEdit(_orderDetails)


    Friend Sub 测试()
        '添加10条
        For index = 1 To 10
            _orderDetails.Add(New OrderDetailsLite With {
              .OrderID = _OrderID,
              .UserCode = "1",
              .BarCode = "1",
              .ProductName = "1",
              .UnitPrice = 1,
              .UnitPricePuchase = 1,
              .QuantityPerUnit = 1,
              .QuantityPerUnit2nd = 1,
              .Quantity = index
             })
        Next
        Dim affrows = repos.EndEdit()
        _logger.LogTrace("1.添加10条 => " & affrows)
        repos.BeginEdit(_orderDetails)


        '移除5条
        Dim limit = If(_orderDetails.Count < 5, _orderDetails.Count, 5)
        For index = 0 To limit - 1
            _orderDetails.RemoveAt(0)
        Next
        affrows = repos.EndEdit()
        _logger.LogTrace("2.移除5条 => " & affrows)
        repos.BeginEdit(_orderDetails)


        '移除全部
        For index = 0 To _orderDetails.Count - 1
            _orderDetails.RemoveAt(0)
        Next
        affrows = repos.EndEdit()
        _logger.LogTrace("3.移除全部 => " & affrows)
        repos.BeginEdit(_orderDetails)


        '添加10条
        For index = 1 To 10
            _orderDetails.Add(New OrderDetailsLite With {
              .OrderID = _OrderID,
              .UserCode = "1",
              .BarCode = "1",
              .ProductName = "1",
              .UnitPrice = 1,
              .UnitPricePuchase = 1,
              .QuantityPerUnit = 1,
              .QuantityPerUnit2nd = 1,
              .Quantity = index
             })
        Next
        affrows = repos.EndEdit()
        _logger.LogTrace("4.添加10条 => " & affrows)
        repos.BeginEdit(_orderDetails)

        '再执行一次
        affrows = repos.EndEdit()
        _logger.LogTrace("5.再执行一次 => " & affrows)
        repos.BeginEdit(_orderDetails)

    End Sub

执行结果

trce: tienda_Sql.FormOrderTest[0]
      1.添加10条 => 0

 SQL==> 
INSERT INTO [Order Details]([BarCode], [Bultos2nd], [Bultos], [Quantity], [Discount], [UnitPricePuchase], [UnitPrice], [ProductName], [QuantityPerUnit], [QuantityPerUnit2nd], [Tax], [UserCode], [ProductID], [OrderID], [ProductName2], [TaxC], [DGvip], [EC], [EDiscount], [EDvip], [PriceBase], [PriceIncTax], [Action], [fjson]) OUTPUT INSERTED.[BarCode] as [BarCode], INSERTED.[Bultos2nd] as [Bultos2nd], INSERTED.[Bultos] as [Bultos], INSERTED.[Quantity] as [Quantity], INSERTED.[Discount] as [Discount], INSERTED.[UnitPricePuchase] as [UnitPricePuchase], INSERTED.[UnitPrice] as [UnitPrice], INSERTED.[ProductName] as [ProductName], INSERTED.[QuantityPerUnit] as [QuantityPerUnit], INSERTED.[QuantityPerUnit2nd] as [QuantityPerUnit2nd], INSERTED.[Tax] as [Tax], INSERTED.[UserCode] as [UserCode], INSERTED.[ProductID] as [ProductID], INSERTED.[ID] as [ID], INSERTED.[OrderID] as [OrderID], INSERTED.[ProductName2] as [ProductName2], INSERTED.[TaxC] as [TaxC], INSERTED.[DGvip] as [DGvip], INSERTED.[EC] as [EC], INSERTED.[EDi
scount] as [EDiscount], INSERTED.[EDvip] as [EDvip], INSERTED.[PriceBase] as [PriceBase], INSERTED.[PriceIncTax] as [PriceIncTax], INSERTED.[Action] as [Action], INSERTED.[fjson] as [fjson] VALUES(N'1', NULL, NULL, 6, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 7, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 8, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 9, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 10, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
trce: tienda_Sql.FormOrderTest[0]
      2.移除5条 => 5

 SQL==> 
DELETE FROM [Order Details] WHERE ([ID] = 236955 OR [ID] = 236954 OR [ID] = 236953 OR [ID] = 236952 OR [ID] = 236951)
trce: tienda_Sql.FormOrderTest[0]
      3.移除全部 => 5
trce: tienda_Sql.FormOrderTest[0]
      4.添加10条 => 0

 SQL==> 
INSERT INTO [Order Details]([BarCode], [Bultos2nd], [Bultos], [Quantity], [Discount], [UnitPricePuchase], [UnitPrice], [ProductName], [QuantityPerUnit], [QuantityPerUnit2nd], [Tax], [UserCode], [ProductID], [OrderID], [ProductName2], [TaxC], [DGvip], [EC], [EDiscount], [EDvip], [PriceBase], [PriceIncTax], [Action], [fjson]) OUTPUT INSERTED.[BarCode] as [BarCode], INSERTED.[Bultos2nd] as [Bultos2nd], INSERTED.[Bultos] as [Bultos], INSERTED.[Quantity] as [Quantity], INSERTED.[Discount] as [Discount], INSERTED.[UnitPricePuchase] as [UnitPricePuchase], INSERTED.[UnitPrice] as [UnitPrice], INSERTED.[ProductName] as [ProductName], INSERTED.[QuantityPerUnit] as [QuantityPerUnit], INSERTED.[QuantityPerUnit2nd] as [QuantityPerUnit2nd], INSERTED.[Tax] as [Tax], INSERTED.[UserCode] as [UserCode], INSERTED.[ProductID] as [ProductID], INSERTED.[ID] as [ID], INSERTED.[OrderID] as [OrderID], INSERTED.[ProductName2] as [ProductName2], INSERTED.[TaxC] as [TaxC], INSERTED.[DGvip] as [DGvip], INSERTED.[EC] as [EC], INSERTED.[EDi
scount] as [EDiscount], INSERTED.[EDvip] as [EDvip], INSERTED.[PriceBase] as [PriceBase], INSERTED.[PriceIncTax] as [PriceIncTax], INSERTED.[Action] as [Action], INSERTED.[fjson] as [fjson] VALUES(N'1', NULL, NULL, 1, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 2, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 3, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 4, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 5, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 6, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 7, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NUL
L, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 8, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 9, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (N'1', NULL, NULL, 10, 0, 1, 1, N'1', 1, 1, 0.21, N'1', 0, 3310, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
trce: tienda_Sql.FormOrderTest[0]
      5.再执行一次 => 10