VB.NET中MS Access双查询对账及SACK_NUMBER字段更新咨询

问题说明 #

需要在VB.NET环境下对MS Access数据库的两个查询（LoadData1和LoadData2）进行对账，校验QTY_STOCK、QTY_STOCKIN、QTY_STOCKOUT字段的数据是否一致。原本直接使用LoadData2无需对账，但LoadData2的查询结果无法更新SACK_NUMBER字段，求正确实现方法。

现有代码 #

Private Function CreateConnection() As OleDbConnection
        Return New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\DEMO.accdb;Persist Security Info=False;")
    End Function
    Private Function LoadData1() As DataTable
        Dim dt As New DataTable()

        Using con = CreateConnection(), cmd = con.CreateCommand(),
        ta = New OleDbDataAdapter(cmd)
            Dim sql = <sql>
SELECT * From Stocks;
</sql>.Value
            cmd.CommandText = sql
            ta.Fill(dt)
        End Using
        Return dt
    End Function
    Private Function LoadData2() As DataTable
        Dim dt As New DataTable()

        Using con = CreateConnection(), cmd = con.CreateCommand(),
        ta = New OleDbDataAdapter(cmd)
            Dim sql = <sql>
SELECT  
    t1.CodeProduct, t1.Barcode, t1.Colorcode, t1.[Size], 
    t1.ColorName,
    IIf(IsNull(t1.Qty),'0',t1.Qty)-IIf(IsNull(t2.Qty),'0',t2.Qty) AS QTY_STOCK,
    t1.Qty AS QTY_STOCKIN, t2.Qty AS QTY_STOCKOUT
FROM
    StocksinDetail AS t1
LEFT JOIN 
    StocksoutDetail AS t2 ON (t1.[Size] = t2.[Size])
                          AND (t1.Colorcode = t2.Colorcode)
                          AND (t1.Barcode = t2.Barcode)
                          AND (t1.CodeProduct = t2.CodeProduct);
</sql>.Value
            cmd.CommandText = sql
            ta.Fill(dt)
        End Using
        Return dt
    End Function
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Datagridview1.DataSource = LoadData1()
    End Sub

界面效果 #

解决方案 #

一、对账逻辑实现 #

1. 内存中关联DataTable比对

利用CodeProduct、Barcode、Colorcode、Size四个字段作为关联主键，在内存中匹配两张DataTable的记录并校验字段：

Private Sub ReconcileData()
    Dim dtStocks As DataTable = LoadData1()
    Dim dtCalculated As DataTable = LoadData2()

    ' 给计算结果表设置主键，提升匹配效率
    dtCalculated.PrimaryKey = {
        dtCalculated.Columns("CodeProduct"),
        dtCalculated.Columns("Barcode"),
        dtCalculated.Columns("Colorcode"),
        dtCalculated.Columns("Size")
    }

    ' 添加对账状态列
    dtStocks.Columns.Add("对账状态", GetType(String))

    ' 遍历Stocks表逐行校验
    For Each stockRow As DataRow In dtStocks.Rows
        Dim keyValues As Object() = {
            stockRow("CodeProduct"),
            stockRow("Barcode"),
            stockRow("Colorcode"),
            stockRow("Size")
        }
        Dim calcRow As DataRow = dtCalculated.Rows.Find(keyValues)

        If calcRow IsNot Nothing Then
            ' 逐一校验目标字段
            Dim statusList As New List(Of String)
            If Not Equals(stockRow("QTY_STOCK"), calcRow("QTY_STOCK")) Then
                statusList.Add("库存数量不一致")
            End If
            If Not Equals(stockRow("QTY_STOCKIN"), calcRow("QTY_STOCKIN")) Then
                statusList.Add("入库数量不一致")
            End If
            If Not Equals(stockRow("QTY_STOCKOUT"), calcRow("QTY_STOCKOUT")) Then
                statusList.Add("出库数量不一致")
            End If
            stockRow("对账状态") = If(statusList.Count > 0, String.Join(";", statusList), "一致")
        Else
            stockRow("对账状态") = "无匹配计算记录"
        End If
    Next

    ' 绑定对账结果到DataGridView
    Datagridview1.DataSource = dtStocks
End Sub

2. 数据库层面直接生成对账结果

数据量较大时，推荐在数据库中执行对账查询，减少内存消耗：

SELECT 
    s.CodeProduct, s.Barcode, s.Colorcode, s.[Size],
    s.QTY_STOCK AS 库存表_库存,
    calc.QTY_STOCK AS 计算_库存,
    s.QTY_STOCKIN AS 库存表_入库,
    calc.QTY_STOCKIN AS 计算_入库,
    s.QTY_STOCKOUT AS 库存表_出库,
    calc.QTY_STOCKOUT AS 计算_出库,
    CASE 
        WHEN s.QTY_STOCK <> calc.QTY_STOCK THEN '库存数量不一致'
        WHEN s.QTY_STOCKIN <> calc.QTY_STOCKIN THEN '入库数量不一致'
        WHEN s.QTY_STOCKOUT <> calc.QTY_STOCKOUT THEN '出库数量不一致'
        ELSE '一致'
    END AS 对账状态
FROM Stocks s
LEFT JOIN (
    SELECT  
        t1.CodeProduct, t1.Barcode, t1.Colorcode, t1.[Size], 
        IIf(IsNull(t1.Qty),'0',t1.Qty)-IIf(IsNull(t2.Qty),'0',t2.Qty) AS QTY_STOCK,
        t1.Qty AS QTY_STOCKIN, t2.Qty AS QTY_STOCKOUT
    FROM StocksinDetail AS t1
    LEFT JOIN StocksoutDetail AS t2 
        ON t1.[Size] = t2.[Size]
        AND t1.Colorcode = t2.Colorcode
        AND t1.Barcode = t2.Barcode
        AND t1.CodeProduct = t2.CodeProduct
) calc ON s.CodeProduct = calc.CodeProduct
    AND s.Barcode = calc.Barcode
    AND s.Colorcode = calc.Colorcode
    AND s.[Size] = calc.[Size]

在VB.NET中调用该查询：

Private Function LoadReconcileResult() As DataTable
    Dim dt As New DataTable()
    Using con = CreateConnection(), cmd = con.CreateCommand(), ta = New OleDbDataAdapter(cmd)
        cmd.CommandText = "上述对账SQL语句"
        ta.Fill(dt)
    End Using
    Return dt
End Function

' 在窗体加载时调用
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Datagridview1.DataSource = LoadReconcileResult()
End Sub

二、解决LoadData2无法更新SACK_NUMBER的问题 #

LoadData2是关联查询的虚拟结果集，无主键支持更新，需直接操作原始表：

1. 直接更新原始表字段

假设SACK_NUMBER存储在StocksinDetail表中，编写更新方法：

Private Sub UpdateSackNumber(codeProduct As String, barcode As String, colorcode As String, size As String, newSackNumber As String)
    Using con = CreateConnection(), cmd = con.CreateCommand()
        cmd.CommandText = "UPDATE StocksinDetail SET SACK_NUMBER = @SackNum WHERE CodeProduct = @Code AND Barcode = @Bar AND Colorcode = @Color AND [Size] = @Size"
        cmd.Parameters.AddWithValue("@SackNum", newSackNumber)
        cmd.Parameters.AddWithValue("@Code", codeProduct)
        cmd.Parameters.AddWithValue("@Bar", barcode)
        cmd.Parameters.AddWithValue("@Color", colorcode)
        cmd.Parameters.AddWithValue("@Size", size)
        
        con.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

2. 绑定可更新数据源到DataGridView

若需在界面直接编辑SACK_NUMBER，应绑定Stocks或StocksinDetail等原始表，编辑完成后重新执行对账操作即可同步数据。

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