C# SQLite 交易處理兩種方式：SQL 指令與 ADO.NET 交易 API

方式一：純 SQL 指令

using (var conn = new SQLiteConnection(connStr))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = @"
            BEGIN TRANSACTION;
            INSERT INTO Users (Name, Email) VALUES ('Alice', 'alice@example.com');
            INSERT INTO Users (Name, Email) VALUES ('Bob', 'bob@example.com');
            UPDATE Accounts SET Balance = Balance - 100 WHERE UserId = 1;
            UPDATE Accounts SET Balance = Balance + 100 WHERE UserId = 2;
            COMMIT;
        ";
        cmd.ExecuteNonQuery();
    }
}

• 優點：簡單、單次往返、適合批次腳本
• 缺點：錯誤處理靠 SQLite 內部回滾、難以在 C# 邏輯中攔截特定錯誤、參數化查詢較麻煩

方式二：ADO.NET DbTransaction API (推薦)

using System.Data;
using System.Data.SQLite;

public void Transfer(int fromUserId, int toUserId, decimal amount)
{
    using (var conn = new SQLiteConnection(GetConnectionString()))
    {
        conn.Open();
        using (var txn = conn.BeginTransaction())
        {
            try
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = txn; // 關鍵：關聯交易
                    
                    // 扣款
                    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - @amt WHERE UserId = @id";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@amt", amount);
                    cmd.Parameters.AddWithValue("@id", fromUserId);
                    int rows1 = cmd.ExecuteNonQuery();
                    if (rows1 == 0) throw new Exception($"扣款失敗: UserId={fromUserId}");
                    
                    // 入款
                    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + @amt WHERE UserId = @id";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@amt", amount);
                    cmd.Parameters.AddWithValue("@id", toUserId);
                    int rows2 = cmd.ExecuteNonQuery();
                    if (rows2 == 0) throw new Exception($"入款失敗: UserId={toUserId}");
                    
                    // 記錄流水
                    cmd.CommandText = @"INSERT INTO Transfers (FromUser, ToUser, Amount, CreatedAt) 
                                        VALUES (@from, @to, @amt, @now)";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@from", fromUserId);
                    cmd.Parameters.AddWithValue("@to", toUserId);
                    cmd.Parameters.AddWithValue("@amt", amount);
                    cmd.Parameters.AddWithValue("@now", DateTime.Now);
                    cmd.ExecuteNonQuery();
                }
                
                txn.Commit(); // 成功提交
            }
            catch (Exception ex)
            {
                txn.Rollback(); // 失敗回滾
                // 記錄日誌、拋出或轉換例外
                throw new TransferException("轉帳失敗", ex);
            }
        }
    }
}

關鍵點總結

輔助類別：SQLiteHelper

public static class SQLiteHelper
{
    private static string _connStr = "Data Source=app.db;Version=3;";
    
    public static string GetConnectionString() => _connStr;
    
    public static SQLiteConnection GetConnection()
    {
        var conn = new SQLiteConnection(_connStr);
        conn.Open();
        return conn;
    }
    
    // 交易範本方法
    public static void ExecuteInTransaction(Action action)
    {
        using (var conn = GetConnection())
        using (var txn = conn.BeginTransaction())
        {
            try
            {
                action(conn, txn);
                txn.Commit();
            }
            catch
            {
                txn.Rollback();
                throw;
            }
        }
    }
}

// 使用
SQLiteHelper.ExecuteInTransaction((conn, txn) => {
    using (var cmd = conn.CreateCommand())
    {
        cmd.Transaction = txn;
        cmd.CommandText = "INSERT INTO Log (Msg) VALUES (@msg)";
        cmd.Parameters.AddWithValue("@msg", "測試");
        cmd.ExecuteNonQuery();
    }
});

效能提示

• 大量寫入時：單一交易包含所有 INSERT，比自動提交快 10-100 倍
• PRAGMA journal_mode = WAL;：啟用 Write-Ahead Logging，大幅提升併發讀寫效能
• PRAGMA synchronous = NORMAL;：平衡安全與速度 (預設 FULL 最安全但較慢)
• 重用 SQLiteCommand 物件，透過 Parameters.Clear() 重設參數

常見錯誤

1. 忘記指派 cmd.Transaction = txn → 命令不在交易中，自動提交
2. catch 區塊未呼叫 Rollback() → 交易殘留，資料庫鎖定
3. 長時間交易持有鎖 → 阻塞其他連線，盡量縮短交易範圍
4. 跨方法傳遞 SQLiteTransaction → 設計困難，建議用委派/範本方法封裝

正確使用 ADO.NET 交易 API，配合參數化查詢與例外處理，是 C# 操作 SQLite 進行可靠資料操作的標準模式。