ASP.NET 使用存儲過程批次寫入資料庫：SqlParameter 陣列防注入、效能優化完整範例

大量資料寫入或複雜業務邏輯時，存儲過程配合 SqlParameter 陣列可有效防止 SQL Injection、減少網路往返、利用伺服器端執行計畫快取提升效能。本文示範從前端收集參數、建立參數陣列、呼叫存儲過程、資料庫端邏輯的完整流程。

一、前端頁面 (ASPX) 收集多組參數

<table>
  <tr>
    <td>收看視頻標準 (分鐘)</td>
    <td><asp:TextBox ID="txtV1_BZ" runat="server" /></td>
    <td>收看視頻非標準</td>
    <td><asp:TextBox ID="txtV1_FZ" runat="server" /></td>
  </tr>
  <tr>
    <td>作業批閱標準 (篇)</td>
    <td><asp:TextBox ID="txtZ1_BZ" runat="server" /></td>
    <td>作業批閱非標準</td>
    <td><asp:TextBox ID="txtZ1_FZ" runat="server" /></td>
  </tr>
  <!-- 更多欄位... -->
</table>
<asp:Button ID="btnSave" runat="server" Text="儲存" OnClick="btnSave_Click" />

二、後端程式碼 (CS) 參數化呼叫存儲過程

protected void btnSave_Click(object sender, EventArgs e)
{
    // 1. 取得並驗證輸入
    int projectId = GetCurrentProjectId(); // 從 Session/QueryString 取得

    var parameters = new[]
    {
        new SqlParameter("@PROJECT_ID", projectId),

        // 視頻標準
        new SqlParameter("@V1_BIAOZHOU", ParseInt(txtV1_BZ.Text)),
        new SqlParameter("@V1_FENGZHI",  ParseInt(txtV1_FZ.Text)),
        new SqlParameter("@V2_BIAOZHOU", ParseInt(txtV2_BZ.Text)),
        new SqlParameter("@V2_FENGZHI",  ParseInt(txtV2_FZ.Text)),
        new SqlParameter("@V3_BIAOZHOU", ParseInt(txtV3_BZ.Text)),
        new SqlParameter("@V3_FENGZHI",  ParseInt(txtV3_FZ.Text)),

        // 批閱標準
        new SqlParameter("@Z1_BIAOZHUN", ParseInt(txtZ1_BZ.Text)),
        new SqlParameter("@Z1_FENZHI",   ParseInt(txtZ1_FZ.Text)),
        new SqlParameter("@Z2_BIAOZHUN", ParseInt(txtZ2_BZ.Text)),
        new SqlParameter("@Z2_FENZHI",   ParseInt(txtZ2_FZ.Text)),
        new SqlParameter("@Z3_BIAOZHUN", ParseInt(txtZ3_BZ.Text)),
        new SqlParameter("@Z3_FENZHI",   ParseInt(txtZ3_FZ.Text)),
    };

    // 2. 執行存儲過程
    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))
    using (var cmd = new SqlCommand("insert_Update_PROJECT_STANDARD", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(parameters);

        conn.Open();
        int affected = cmd.ExecuteNonQuery();
        lblMsg.Text = affected > 0 ? "儲存成功" : "無資料異動";
    }
}

private int ParseInt(string s) => int.TryParse(s, out int v) ? v : 0;

三、資料庫端存儲過程

CREATE PROCEDURE insert_Update_PROJECT_STANDARD
    @PROJECT_ID     INT,
    @V1_BIAOZHOU    INT, @V1_FENGZHI    INT,
    @V2_BIAOZHOU    INT, @V2_FENGZHI    INT,
    @V3_BIAOZHOU    INT, @V3_FENGZHI    INT,
    @Z1_BIAOZHUN    INT, @Z1_FENZHI     INT,
    @Z2_BIAOZHUN    INT, @Z2_FENZHI     INT,
    @Z3_BIAOZHUN    INT, @Z3_FENZHI     INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 視頻標準 (假設 Sid=類別, KHBZ=標準值, FZ=非標準值)
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ) VALUES (@PROJECT_ID, @V1_BIAOZHOU, @V1_FENGZHI);
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ) VALUES (@PROJECT_ID, @V2_BIAOZHOU, @V2_FENGZHI);
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ) VALUES (@PROJECT_ID, @V3_BIAOZHOU, @V3_FENGZHI);

    -- 批閱標準
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ) VALUES (@PROJECT_ID, @Z1_BIAOZHUN, @Z1_FENZHI);
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ) VALUES (@PROJECT_ID, @Z2_BIAOZHUN, @Z2_FENZHI);
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ) VALUES (@PROJECT_ID, @Z3_BIAOZHUN, @Z3_FENZHI);
END

四、進階優化：Table-Valued Parameter (TVP) 批次插入

若筆數動態且大量 (如上百筆)，建議改用 TVP 一次傳遞資料表給存儲過程。

-- 1. 建立使用者定義資料表類型
CREATE TYPE dbo.StandardTableType AS TABLE
(
    Sid INT, KHBZ INT, FZ INT
);
GO

-- 2. 存儲過程接收 TVP
CREATE PROCEDURE BulkInsert_Standard
    @Data dbo.StandardTableType READONLY
AS
BEGIN
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ)
    SELECT Sid, KHBZ, FZ FROM @Data;
END
GO

// 3. C# 呼叫
var dt = new DataTable();
dt.Columns.Add("Sid", typeof(int));
dt.Columns.Add("KHBZ", typeof(int));
dt.Columns.Add("FZ", typeof(int));

foreach (var item in items)
    dt.Rows.Add(projectId, item.StandardValue, item.NonStandardValue);

using (var cmd = new SqlCommand("BulkInsert_Standard", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    var p = cmd.Parameters.AddWithValue("@Data", dt);
    p.SqlDbType = SqlDbType.Structured;
    p.TypeName = "dbo.StandardTableType";
    cmd.ExecuteNonQuery();
}

五、關鍵最佳實踐

六、效能比較 (萬筆資料插入)

掌握 SqlParameter 陣列與 TVP 兩大技巧，即可應付 90% 以上的批次寫入場景，兼顧安全與效能。