如何在 VB.NET 中使用 dapper 从股票卡进行余额列计算

How to make balance column calculations from stockcards with dapper in VB.NET

提问人：siwa86 提问时间：10/23/2023 最后编辑：Amit Joshisiwa86 更新时间：10/24/2023 访问量：98

问：

我正在尝试在 VB.NET 中用 dapper 平衡库存卡的列计算。我有下面的代码，但还不对：

请指导我。

谢谢

Public Class Form2
    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim scs As New Stockcardservice()
        DataGridView1.DataSource = scs.LoadData("A")
    End Sub
End Class
Public Class Stockcardservice
    Private Function CreateConnection() As String
        Return ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Stockcard2.accdb;Persist Security Info=False;")
    End Function
    Public Function LoadData(ByVal code As String) As IEnumerable(Of DTOStockcard)
        Dim sql = $"SELECT DetailUNION.InvoNo,DetailUNION.InvoDate, DetailUNION.Transaction,DetailUNION.[No], DetailUNION.CodeProduct, DetailUNION.Info, DetailUNION.Remark, DetailUNION.NameSC, Sum(IIf(Transaction='Purchase',Qty,0)) AS [IN], Sum(IIf(Transaction='Sales',Qty,0)) AS OUT
FROM (SELECT [No], InvoDate, Purchase.Invono,CodeProduct, Qty, Info,Remark,NameSC,Transaction 
    FROM PurchaseDetail INNER JOIN Purchase 
    ON Purchase.Invono = PurchaseDetail.Invono
    UNION 
    SELECT [No], InvoDate, Sales.Invono,CodeProduct, Qty,Info,Remark,NameSC,Transaction 
    FROM SalesDetail INNER JOIN Sales 
    ON Sales.Invono = SalesDetail.Invono
)  AS DetailUNION
WHERE (((DetailUNION.CodeProduct)='{code}'))
GROUP BY DetailUNION.InvoDate, DetailUNION.Transaction, DetailUNION.InvoNo, DetailUNION.[No], DetailUNION.CodeProduct, DetailUNION.Info, DetailUNION.Remark, DetailUNION.NameSC;"
        Using _conn = New OleDbConnection(CreateConnection())
            Return _conn.Query(Of DTOStockcard)(sql).ToList()
        End Using
    End Function
End Class
Public Class DTOStockcard
    Public Property InvoNo() As String
    Public Property InvoDate() As Date
    Public Property Transaction() As String
    Public Property No() As Integer
    Public Property CodeProduct() As String
    Public Property Info() As String
    Public Property Remark() As String
    Public Property NameSC() As String
    Public Property [IN] As Integer
    Public Property OUT As Integer
    Public Property BALANCE As Integer
End Class

上述代码的结果

因沃诺 Invodate 交易 不 代码产品 信息 备注 名称SC 在 外 平衡
1000 18-10月，23 购买 1 一个 白 再重复一次 测试1 50 0
1000 18-10月，23 销售 1 一个 白 测试10 25 0
1001 19-10月，23 购买 2 一个 棕色 测试2 25 0
1001 19-10月，23 销售 2 一个 白 测试20 15 0
1002 20-10月，23 销售 1 一个 棕色 测试30 25 0

期望的结果

因沃诺 Invodate 交易 不 代码产品 信息 备注 名称SC 在 外 平衡
1000 18-10月，23 购买 1 一个 白 再重复一次 测试1 50 50
1000 18-10月，23 销售 1 一个 白 测试10 25 25
1001 19-10月，23 购买 2 一个 棕色 测试2 25 50
1001 19-10月，23 销售 2 一个 白 测试20 15 35
1002 20-10月，23 销售 1 一个 棕色 测试30 25 10
SQL vb.net LINQ DataGridView

评论

0赞 Nick Abbot 10/23/2023
呵呵，直到现在我才知道Dapper是什么。可能是因为 Visual Studio 中的内置控件使用起来非常简单。
0赞 dr.null 10/23/2023
应该如何计算？总和？不同？其他数学？在模型中创建属性，并在 getter 中进行数学运算并返回结果。如果模型不包含所需的 db 字段来执行您不希望在网格中显示的数学运算，请添加该字段并使用该特性修饰属性。BalanceBalanceReadOnly<Browsable(False)>
1赞 June7 10/24/2023
@dr.null，OP 需要一个运行总和。相关问题 stackoverflow.com/questions/77331025/...
0赞 siwa86 10/24/2023
@dr.null ，对不起，我的回复迟到了。 所以计算是这样的，这意味着我只是更改类中的属性，然后它会产生一个余额列，请指导我How should the Balance be calculated? Sum? Different? Other Math?sum(IN-OUT)+BALANCE (PREVIOUS BALANCE)Make the Balance property in your model ReadOnly and do the math in the getter and return the result. If the model does not include a required db field to do the math that you don't want to display it in the grid, add it and decorate the property with the <Browsable(False)> attribute.
0赞 siwa86 10/24/2023
@dr.null ，是的，你是对的，这是一个相关的问题，但它是一篇仅在 ms access 中使用 sql 的帖子，但对于余额列，它仍然不起作用，所以这就是为什么我以不同的方式发表了一篇新文章OP wants a running sum. Related quesiton stackoverflow.com/questions/77331025/…

答：

1赞 Shahram Alemzadeh 10/24/2023 #1

当我们可以从原始表中调整数据时，无需使用复杂的查询。

类：

Public Class Invoice
    Property Invono() As Integer
    Property Invodate() As Date
    Property Transaction() As String
    Property Remark() As String
    Property NameSC() As String
End Class
    
Public Class Detail
    Public Property InvoNo() As String
    Public Property No() As Integer
    Public Property CodeProduct() As String
    Public Property Info() As String
    Public Property Qty() As Integer
End Class
    
Public Class StockCard
    Public Property InvoNo As String
    Public Property InvoDate As Date
    Public Property Transaction As String
    Public Property No As Integer
    Public Property CodeProduct As String
    Public Property Info As String
    Public Property Remark As String
    Public Property NameSC As String
    Public Property [IN] As Integer
    Public Property [OUT] As Integer
    Public Property BALANCE As Integer
End Class

数据降级：

Private Purchase, Sale As New List(Of Invoice)
Private PurchaseDetails, SaleDetails As New List(Of Detail)

使用 Dapper 加载数据：

Using Connection = New OleDbConnection(My.Settings.Cons)
    Purchase = Connection.Query(Of Invoice)("SELECT * FROM Purchase")
    PurchaseDetails = Connection.Query(Of Detail)("SELECT * FROM PurchaseDetails")
    Sale = Connection.Query(Of Invoice)("SELECT * FROM Sale")
    SaleDetails = Connection.Query(Of Detail)("SELECT * FROM SaleDetails")
End Using

创建 StockCard（对于 CodeProduct=A）：

Dim ps = From p In Purchase
         From pd In PurchaseDetails
         Where pd.InvoNo = p.Invono And pd.CodeProduct = "A"
         Select
             Invono = p.Invono,
             Invodate = p.Invodate,
             p.Transaction,
             pd.No,
             pd.CodeProduct,
             pd.Info,
             p.Remark,
             p.NameSC,
             [IN] = pd.Qty,
             [OUT] = 0,
             BALANCE = pd.Qty
         Order By Invono, Invodate


Dim ss = From s In Sale
         From sd In SaleDetails
         Where sd.InvoNo = s.Invono And sd.CodeProduct = "A"
         Select
             Invono = s.Invono,
             Invodate = s.Invodate,
             s.Transaction,
             sd.No,
             sd.CodeProduct,
             sd.Info,
             s.Remark,
             s.NameSC,
             [IN] = 0,
             [OUT] =
             sd.Qty,
             BALANCE = -sd.Qty
         Order By Invono, Invodate

Dim Card_temp = ps.Union(ss).OrderBy(Function(w) w.Invono).ThenBy(Function(w) w.Invodate)

Dim Card As New List(Of Stockcard)

Dim RunningBalance As Integer = 0

For Each ct In Card_temp
    Dim sc As New StockCard
    With sc
        .InvoNo = ct.Invono
        .InvoDate = ct.Invodate
        .Transaction = ct.Transaction
        .No = ct.No
        .CodeProduct = ct.CodeProduct
        .Info = ct.Info
        .Remark = ct.Remark
        .NameSC = ct.NameSC
        .IN = ct.IN
        .OUT = ct.OUT
        .BALANCE = RunningBalance + ct.BALANCE
        RunningBalance = .BALANCE
    End With
    Card.Add(sc)
Next

DGV_Result.DataSource = Card

enter image description here

要清除零值：

1-将StockCard中[IN]和[OUT]的数据类型从整数更改为字符串

Public Property [IN] As String
Public Property [OUT] As String

2- 编辑 select 语句

    Dim ps = ...
    ...
    [IN] = CStr(pd.Qty),
    [OUT] = String.Empty,
    ...

   Dim ss ...
   ...
   [IN] = String.Empty,
   [OUT] = CStr(sd.Qty),
   ...

enter image description here

评论

0赞 siwa86 10/24/2023
对不起，我回复晚了。谢谢你的回答
0赞 siwa86 10/25/2023
谢谢你的回答，对不起，我迟到了

上一个：DBF SQL标准转义列名？

下一个：如何处理以前删除的顺序交易编号，以便它们可以保持顺序