如何在VB.NET中使用Dapper实现库存卡余额列计算

问题 #

使用VB.NET结合Dapper实现库存卡余额列计算时，BALANCE列始终显示为0，无法得到正确的累计余额结果。

原代码 #

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

当前运行结果 #

期望结果 #

修正方案 #

方案1：内存中计算累计余额（推荐） #

原代码问题在于SQL未计算BALANCE，DTO中BALANCE默认值为0，因此返回结果全为0。我们可以在获取数据后，通过代码遍历计算累计余额：

修改Stockcardservice类的LoadData方法：

Public Function LoadData(ByVal code As String) As IEnumerable(Of DTOStockcard)
    ' 新增ORDER BY确保数据按时间顺序排列，这是累计计算的前提
    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
ORDER BY DetailUNION.InvoDate, DetailUNION.[No];"
    
    Using _conn = New OleDbConnection(CreateConnection())
        ' 使用参数化查询避免SQL注入
        Dim stockCards = _conn.Query(Of DTOStockcard)(sql, New With {.Code = code}).ToList()
        
        ' 计算累计余额
        Dim runningBalance As Integer = 0
        For Each card In stockCards
            runningBalance += card.IN - card.OUT
            card.BALANCE = runningBalance
        Next
        
        Return stockCards
    End Using
End Function

关键修改点：

1. 给SQL添加ORDER BY子句，确保数据按InvoDate和No顺序排列，保证累计计算的正确性；
2. 替换字符串拼接为参数化查询@Code，避免SQL注入风险；
3. 遍历查询结果，用runningBalance变量累计计算每一行的余额，赋值给BALANCE属性。

方案2：通过Access SQL直接计算累计余额 #

如果希望直接通过SQL返回余额，可以使用Access支持的子查询方式计算累计值，但这种方式对于大数据量性能较差：

修改SQL语句如下：

Dim sql = $"SELECT 
    t.InvoNo, t.InvoDate, t.Transaction, t.[No], t.CodeProduct, t.Info, t.Remark, t.NameSC, t.[IN], t.OUT,
    (SELECT Sum(IIf(Transaction='Purchase',Qty,0) - IIf(Transaction='Sales',Qty,0)) 
     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 sub
     WHERE sub.CodeProduct = t.CodeProduct 
       AND (sub.InvoDate < t.InvoDate OR (sub.InvoDate = t.InvoDate AND sub.[No] <= t.[No]))
    ) AS BALANCE
FROM (
    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
) AS t
ORDER BY t.InvoDate, t.[No];"

同样需要使用参数化查询传递@Code参数，执行后SQL会直接返回计算好的BALANCE列。

内容的提问来源于stack exchange，提问作者user22579796