C# 依據資料庫最大編號自動生成指定位數流水號 (如 BH1000001)

需求場景

資料庫主鍵或單據編號需為定長格式：前綴 + 定位數數字 (如 BH1000001、ORD20240001)，新增時自動遞增。

核心邏輯

1. 查詢資料表中現有最大編號
2. 擷取數字部分並轉整數
3. 加 1 後格式化回定長字串
4. 組合前綴輸出

完整實作 (ADO.NET 參數化查詢版)

using System;
using System.Data;
using System.Data.SqlClient;

public class SerialNumberGenerator
{
    private readonly string _connectionString;
    private readonly string _tableName;
    private readonly string _columnName;
    private readonly string _prefix;
    private readonly int _numberLength; // 數字部分總長度 (含前導零)

    public SerialNumberGenerator(string connStr, string table, string column, string prefix, int numberLength)
    {
        _connectionString = connStr;
        _tableName = table;
        _columnName = column;
        _prefix = prefix;
        _numberLength = numberLength;
    }

    /// 

    /// 取得下一個流水號
    /// 
    public string GetNextSerial()
    {
        const string sql = "SELECT MAX({0}) FROM {1}"; // 參數化無法用於識別符，需驗證輸入安全

        using (var conn = new SqlConnection(_connectionString))
        using (var cmd = new SqlCommand(string.Format(sql, _columnName, _tableName), conn))
        {
            conn.Open();
            object result = cmd.ExecuteScalar();
            
            if (result == null || result == DBNull.Value)
            {
                // 無資料，回傳初始編號
                return FormatSerial(1);
            }

            string maxSerial = result.ToString();
            int numericPart = ExtractNumericPart(maxSerial);
            return FormatSerial(numericPart + 1);
        }
    }

    private int ExtractNumericPart(string serial)
    {
        // 假設格式：Prefix + Number (如 BH1000001)
        if (!serial.StartsWith(_prefix))
            throw new InvalidOperationException($"編號格式不符：預期前綴 {_prefix}，實際 {serial}");

        string numStr = serial.Substring(_prefix.Length);
        if (!int.TryParse(numStr, out int num))
            throw new InvalidOperationException($"編號數字部分解析失敗：{numStr}");

        return num;
    }

    private string FormatSerial(int number)
    {
        // 格式化為定長數字 (前導零補足)
        string numStr = number.ToString($"D{_numberLength}"); // D7 -> 0000001
        return $"{_prefix}{numStr}";
    }
}

// 使用範例
class Program
{
    static void Main()
    {
        string connStr = "Data Source=.;Initial Catalog=MyDB;Integrated Security=True";
        var generator = new SerialNumberGenerator(connStr, "tb_Orders", "OrderNo", "ORD", 7);
        
        string nextNo = generator.GetNextSerial(); // 如 ORD0000001
        Console.WriteLine($"下一張單號：{nextNo}");
    }
}

進階：支援年份前綴 (如 ORD20240001)

public class YearlySerialGenerator
{
    private readonly string _connectionString;
    private readonly string _tableName;
    private readonly string _columnName;
    private readonly string _prefixFormat; // "ORD{0:yyyy}" -> ORD2024
    private readonly int _sequenceLength;    // 流水號長度 (4 碼 -> 0001)

    public string GetNextSerial()
    {
        string prefix = string.Format(_prefixFormat, DateTime.Now); // 今年份前綴
        string likePattern = prefix + "%"; // 查詢今年份最大值

        const string sql = "SELECT MAX({0}) FROM {1} WHERE {0} LIKE @pattern";

        using (var conn = new SqlConnection(_connectionString))
        using (var cmd = new SqlCommand(string.Format(sql, _columnName, _tableName), conn))
        {
            cmd.Parameters.AddWithValue("@pattern", likePattern);
            conn.Open();
            object result = cmd.ExecuteScalar();

            int seq = 1;
            if (result != null && result != DBNull.Value)
            {
                string maxSerial = result.ToString();
                string seqStr = maxSerial.Substring(prefix.Length);
                if (int.TryParse(seqStr, out int maxSeq))
                    seq = maxSeq + 1;
            }

            return prefix + seq.ToString($"D{_sequenceLength}");
        }
    }
}

資料庫層面優化建議

1. 建立索引：`CREATE INDEX IX_tb_Orders_OrderNo ON tb_Orders(OrderNo)` 加速 MAX 查詢。
2. 序列物件 (SQL Server 2012+)：使用 `CREATE SEQUENCE` + `NEXT VALUE FOR` 取代 MAX 查詢，避免並發競爭。
3. IDENTITY + 計算欄位：主鍵用 `ID INT IDENTITY`，單據號用計算欄位 `'ORD' + RIGHT('0000000'+CAST(ID AS VARCHAR), 7)`。
4. 交易鎖定：高併發下 `SELECT MAX` + `INSERT` 非原子，需 `SERIALIZABLE` 隔離級或 `sp_getapplock` 應用鎖。

並發安全版本 (使用 sp_getapplock)

public string GetNextSerialSafe()
{
    using (var conn = new SqlConnection(_connectionString))
    {
        conn.Open();
        using (var tran = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
        {
            try
            {
                // 取得應用鎖 (資源名稱唯一)
                using (var lockCmd = new SqlCommand("EXEC sp_getapplock @Resource='SerialGen_OrderNo', @LockMode='Exclusive', @LockTimeout=5000", conn, tran))
                {
                    int lockResult = (int)lockCmd.ExecuteScalar();
                    if (lockResult < 0) throw new TimeoutException("無法取得編號鎖定");
                }

                // 同交易內查詢 MAX 並插入
                string nextSerial = GetNextSerialInternal(conn, tran);
                
                // 釋放鎖
                using (var unlockCmd = new SqlCommand("EXEC sp_releaseapplock @Resource='SerialGen_OrderNo'", conn, tran))
                    unlockCmd.ExecuteNonQuery();

                tran.Commit();
                return nextSerial;
            }
            catch
            {
                tran.Rollback();
                throw;
            }
        }
    }
}