SqlCommandBuilder 批次更新機制深度解析：原理、適用場景與優缺點

為什麼需要 SqlCommandBuilder？

使用設計工具拖曳 `SqlDataAdapter` 時，Visual Studio 自動產生 `SelectCommand`/`InsertCommand`/`UpdateCommand`/`DeleteCommand`。但手動建立 `SqlDataAdapter` 時，這四個命令屬性為 `null`，呼叫 `Update()` 會拋出例外：「找不到 UpdateCommand」。

核心解決方案

public int UpdateByDataSet(DataSet ds, string tableName, string connectionString)
{
    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand($"SELECT * FROM [{tableName}]", conn);
            
            // 關鍵：SqlCommandBuilder 自動根據 SelectCommand 產生其餘三個命令
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            
            // 執行批次更新
            return adapter.Update(ds, tableName);
        }
    }
    catch (Exception ex)
    {
        // 記錄日誌並拋出自訂例外
        throw new BusinessException("資料更新失敗", ex);
    }
}

適用時機

1. 批次編輯確認模式：使用者在 GridView 編輯多筆資料，最後點擊「儲存」一次性寫入資料庫
2. 離線資料同步：行動裝置/離線應用同步本地變更至伺服器
3. 匯入預覽確認：Excel 匯入預覽，使用者確認無誤後批次寫入
4. 無初始查詢的純更新場景：DataSet 非從資料庫填充，需明確指定 `SelectCommand` 讓 Builder 解析結構

運作原理

1. `SelectCommand` 執行查詢取得資料表結構描述
2. `SqlCommandBuilder` 分析 `SelectCommand` 的 `CommandText` 與參數
3. 自動產生參數化的 `INSERT`/`UPDATE`/`DELETE` SQL 陳述式
4. `DataAdapter.Update()` 遍歷 `DataSet` 中 `RowState` 為 `Added`/`Modified`/`Deleted` 的列
5. 對應執行產生的命令，支援 `UpdateBatchSize` 批次處理

必要條件與限制

優缺點深度分析

優點

• 程式碼極度精簡：幾行代碼替代數百行手寫 SQL
• 自動參數化：天然防止 SQL 注入
• 維護性高：資料表結構變更只需更新 `SelectCommand`，命令自動同步
• 支援批次更新：設定 `UpdateBatchSize` 減少網路往返

缺點

• 雙倍資料庫往返：先執行 `SELECT *` 取得結構，再執行 `UPDATE`（大資料量明顯）
• 效能較手寫存儲過程低：無法利用資料庫端優化、批次合併、減少日誌
• 難以處理複雜邏輯：觸發器、計算欄位、並行控制（Timestamp/RowVersion）需額外處理
• 除錯困難：自動產生的 SQL 不易在 Profiler 中識別

進階替代方案

1. Dapper + 手寫 SQL：高效能、完全控制、支援批次（`ExecuteAsync` 迴圈或 `BulkInsert` 擴充）
2. Entity Framework Core：變更追蹤、批次更新（`SaveChangesAsync`）、遷移管理
3. SqlBulkCopy：大量純 Insert 場景極致效能
4. Table-Valued Parameters (TVP)：單次呼叫傳遞資料表給預存程序，ACID 保證

最佳實踐建議

• 中小型專案、原型開發、後台管理功能：`SqlCommandBuilder` 足夠
• 高併發、大數據量、核心業務流程：改用 Dapper/EF Core + 手寫優化 SQL
• 務必在 `SelectCommand` 明確列出欄位（避免 `SELECT *`），減少網路傳輸並確保主鍵包含
• 設定 `adapter.UpdateBatchSize = 100~500` 平衡效能與記憶體
• 生產環境記錄自動產生的 SQL（`builder.GetUpdateCommand().CommandText`）供審計