C# 與 SQL Server 存儲過程配合 UpdateDataSet 實現批量寫入 DataSet

場景

前端傳入 DataSet（含多筆簡訊記錄），需一次性寫入資料庫，要求原子性、高效能、參數化防注入。

核心代碼

public bool SaveSMSMessage(SMSBatch smsBatch, DataSet smsMessages)
{
    // 1. 標記所有行為 Modified，觸發 UpdateDataSet 執行 UPDATE/INSERT
    foreach (DataRow row in smsMessages.Tables[0].Rows)
        row.SetModified();

    // 2. 建立命令：呼叫存儲過程 cn.SMSSaveSMSMessage
    var db = new SqlDatabase(ConfigurationManager.ConnectionStrings["ConsultantsConnString"].ConnectionString);
    const string sqlCmd = @"EXEC cn.SMSSaveSMSMessage 
        @SMSBatchID, @SMSTypeID, @SubsidiaryID, @ContactID, @Message, @PhoneNumber";

    using (var cmd = (SqlCommand)db.GetSqlStringCommand(sqlCmd))
    {
        cmd.CommandTimeout = 600; // 10 分鐘超時，防大批次逾時

        // 3. 批次級參數（所有行共用）
        cmd.Parameters.AddWithValue("@SMSBatchID", smsBatch.SMSBatchID);
        cmd.Parameters.AddWithValue("@SMSTypeID", smsBatch.SMSType.SMSTypeID);
        cmd.Parameters.AddWithValue("@SubsidiaryID", smsBatch.SMSType.SubsidiaryID);

        // 4. 行級參數映射（DataRow 欄位名 ↔ 參數名）
        db.AddInParameter(cmd, "@ContactID", DbType.Int64, "ContactID", DataRowVersion.Current);
        db.AddInParameter(cmd, "@Message", DbType.String, "Message", DataRowVersion.Current);

        // 5. 選填欄位處理
        if (smsMessages.Tables[0].Columns.Contains("PhoneNumber"))
            db.AddInParameter(cmd, "@PhoneNumber", DbType.String, "PhoneNumber", DataRowVersion.Current);
        else
            db.AddInParameter(cmd, "@PhoneNumber", DbType.String, DBNull.Value);

        // 6. 一次性批量執行（TransactionScope 可選）
        int rowsEffected = db.UpdateDataSet(smsMessages, "SMSMessage", null, cmd, null, UpdateBehavior.Transactional);
        // rowsEffected 可用於日誌審計
    }
    return true;
}

關鍵點解析

存儲過程建議簽名

CREATE PROCEDURE cn.SMSSaveSMSMessage
    @SMSBatchID   BIGINT,
    @SMSTypeID    INT,
    @SubsidiaryID INT,
    @ContactID    BIGINT,
    @Message      NVARCHAR(MAX),
    @PhoneNumber  NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    MERGE INTO dbo.SMSMessage AS tgt
    USING (SELECT @ContactID AS ContactID) AS src
    ON tgt.SMSBatchID = @SMSBatchID AND tgt.ContactID = @ContactID
    WHEN MATCHED THEN UPDATE SET Message = @Message, PhoneNumber = @PhoneNumber
    WHEN NOT MATCHED THEN INSERT (SMSBatchID, SMSTypeID, SubsidiaryID, ContactID, Message, PhoneNumber)
        VALUES (@SMSBatchID, @SMSTypeID, @SubsidiaryID, @ContactID, @Message, @PhoneNumber);
END;

• 用 MERGE 實現 UPSERT，單次往返完成新增/更新。
• 配合 UpdateDataSet，千行級批次通常在秒級完成。

擴展：更大批次優化

1. SqlBulkCopy：適合純插入、百萬行級，繞過存儲過程直接寫表。
2. Table-Valued Parameter (TVP)：將 DataTable 作為參數傳入存儲過程，單次執行集合操作。
3. 分批提交：每 5,000 行一個交易，避免長交易鎖升級與記憶體暴漲。

選對工具，批量寫入不再是痛點。