在VB.NET+Dapper+Access中实现按类型按月重置的流水交易号

问题根源 #

现有代码未按交易类型和当前年月双重维度过滤查询最大流水号，导致不同交易类型共用了同一序列。要实现需求，核心是在查询最大流水号时，同时限定交易类型和当月时间范围。

假设表结构（基于问题描述） #

假设Stocksin表核心字段：

• TransNo：交易编号（格式示例：IN-202405-00001）
• TransType：交易类型（如IN入库、OUT出库）
• TransDate：交易日期

修正后的代码实现 #

1. 核心流水号生成方法

Imports Dapper
Imports System.Data.OleDb

Public Class TransNumberHelper
    ' 替换为你的Access数据库连接字符串
    Private Const DB_CONN_STR As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourDB.accdb;"

    ''' <summary>
    ''' 按交易类型生成当月流水号
    ''' </summary>
    ''' <param name="transType">交易类型标识（如IN/OUT）</param>
    ''' <returns>格式化后的完整交易编号</returns>
    Public Function GenerateTransNo(transType As String) As String
        Dim currentYm As String = DateTime.Now.ToString("yyyyMM")
        Dim newSeq As Integer = 1

        ' Access SQL：按交易类型+年月分组，提取最大流水号
        Dim sql As String = "SELECT MAX(CInt(Split(TransNo, '-')(2))) AS MaxSeq " &
                            "FROM Stocksin " &
                            "WHERE TransType = @TransType " &
                            "AND Format(TransDate, 'yyyyMM') = @CurrentYm"

        Using conn As New OleDbConnection(DB_CONN_STR)
            conn.Open()
            ' 执行查询，获取分组后的最大流水号（允许null）
            Dim maxSeq As Integer? = conn.Query(Of Integer?)(sql, New With {
                .TransType = transType,
                .CurrentYm = currentYm
            }).FirstOrDefault()

            ' 若存在历史流水号，则新序号+1
            If maxSeq.HasValue AndAlso maxSeq.Value > 0 Then
                newSeq = maxSeq.Value + 1
            End If
        End Using

        ' 拼接成最终编号：类型-年月-00001格式
        Return $"{transType}-{currentYm}-{newSeq.ToString("D5")}"
    End Function
End Class

2. 并发安全的插入逻辑（可选但推荐）

Access并发能力较弱，为避免多用户同时生成重复编号，建议在插入时增加事务+存在性校验：

Public Sub InsertStockRecord(transType As String, goodsName As String, qty As Integer)
    Dim transNo As String = GenerateTransNo(transType)
    Dim insertSql As String = "INSERT INTO Stocksin (TransNo, TransType, TransDate, GoodsName, Qty) " &
                              "VALUES (@TransNo, @TransType, @TransDate, @GoodsName, @Qty)"

    Using conn As New OleDbConnection(DB_CONN_STR)
        conn.Open()
        Using trans As OleDbTransaction = conn.BeginTransaction()
            Try
                ' 校验当前编号是否已存在（防止并发重复）
                Dim exists As Integer = conn.Query(Of Integer)(
                    "SELECT COUNT(*) FROM Stocksin WHERE TransNo = @TransNo",
                    New With {.TransNo = transNo},
                    transaction:=trans
                ).FirstOrDefault()

                If exists > 0 Then
                    ' 编号重复，回滚事务后重新生成插入
                    trans.Rollback()
                    InsertStockRecord(transType, goodsName, qty)
                    Return
                End If

                ' 执行插入操作
                conn.Execute(insertSql, New With {
                    .TransNo = transNo,
                    .TransType = transType,
                    .TransDate = DateTime.Now,
                    .GoodsName = goodsName,
                    .Qty = qty
                }, transaction:=trans)

                trans.Commit()
            Catch ex As Exception
                trans.Rollback()
                Throw New Exception("插入记录失败：" & ex.Message)
            End Try
        End Using
    End Using
End Sub

关键修正点说明 #

1. 维度过滤：SQL查询强制按TransType和当月时间（Format(TransDate, 'yyyyMM')）分组，确保不同类型、不同月份的流水号完全独立。
2. 流水号提取：使用Access的Split函数按-分割交易编号，直接取第三段作为流水号，避免因交易类型长度变化导致的提取错误。
3. 格式统一：用ToString("D5")将流水号格式化为固定5位数字，保证编号格式一致性。
4. 并发处理：通过事务包裹查询+插入操作，增加编号存在性校验，避免高并发场景下的重复编号问题。

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