萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 使用預存程序批次寫入資料庫:參數化命令與多筆資料處理範例

情境

頁面包含多組相同結構的輸入欄位 (如三階段觀影標準、三階段作業批閱標準),需一次性寫入資料庫。使用預存程序 + 參數陣列可確保效能、安全性 (防 SQL Injection) 與可維護性。

資料庫端:預存程序 insert_Update_PROJECT_STANDARD

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;

    -- 觀影標準 (三筆)
    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

前端 .aspx 介面佈局 (表格式輸入)

<tr>
    <td style="width:101px; text-align:center;">
        <asp:Label ID="Label2" runat="server" Text="觀影標準(分鐘):" Font-Size="9pt" Width="132px" />
    </td>
    <td style="text-align:left;" colspan="2">
        <table>
            <tr style="height:25px;">
                <td style="width:180px;"><asp:TextBox ID="TextBox1" runat="server" /></td> <!-- V1_BIAOZHOU -->
                <td style="width:180px;"><asp:TextBox ID="TextBox4" runat="server" /></td> <!-- V1_FENGZHI -->
            </tr>
            <tr>
                <td><asp:TextBox ID="TextBox2" runat="server" /></td> <!-- V2_BIAOZHOU -->
                <td><asp:TextBox ID="TextBox5" runat="server" /></td> <!-- V2_FENGZHI -->
            </tr>
            <tr style="height:25px;">
                <td><asp:TextBox ID="TextBox3" runat="server" /></td> <!-- V3_BIAOZHOU -->
                <td><asp:TextBox ID="TextBox6" runat="server" /></td> <!-- V3_FENGZHI -->
            </tr>
        </table>
    </td>
</tr>

<tr>
    <td style="width:101px; text-align:center;">
        <asp:Label ID="Label3" runat="server" Text="作業批閱標準(篇):" Font-Size="9pt" Width="132px" />
    </td>
    <td style="text-align:left;" colspan="2">
        <table>
            <tr style="height:25px;">
                <td><asp:TextBox ID="TextBox7" runat="server" /></td> <!-- Z1_BIAOZHUN -->
                <td><asp:TextBox ID="TextBox10" runat="server" /></td> <!-- Z1_FENZHI -->
            </tr>
            <tr style="height:25px;">
                <td><asp:TextBox ID="TextBox8" runat="server" /></td> <!-- Z2_BIAOZHUN -->
                <td><asp:TextBox ID="TextBox11" runat="server" /></td> <!-- Z2_FENZHI -->
            </tr>
            <tr style="height:25px;">
                <td><asp:TextBox ID="TextBox9" runat="server" /></td> <!-- Z3_BIAOZHUN -->
                <td><asp:TextBox ID="TextBox12" runat="server" /></td> <!-- Z3_FENZHI -->
            </tr>
        </table>
    </td>
</tr>

後端 .aspx.cs 呼叫預存程序

protected void btnSave_Click(object sender, EventArgs e)
{
    // 1. 取得並驗證輸入 (簡化示範,實務需完整驗證)
    int projectId = GetCurrentProjectId(); // 取得專案 ID

    var parameters = new[]
    {
        new SqlParameter("@PROJECT_ID", projectId),
        new SqlParameter("@V1_BIAOZHOU", ParseInt(TextBox1.Text)),
        new SqlParameter("@V1_FENGZHI",  ParseInt(TextBox4.Text)),
        new SqlParameter("@V2_BIAOZHOU", ParseInt(TextBox2.Text)),
        new SqlParameter("@V2_FENGZHI",  ParseInt(TextBox5.Text)),
        new SqlParameter("@V3_BIAOZHOU", ParseInt(TextBox3.Text)),
        new SqlParameter("@V3_FENGZHI",  ParseInt(TextBox6.Text)),
        new SqlParameter("@Z1_BIAOZHUN", ParseInt(TextBox7.Text)),
        new SqlParameter("@Z1_FENZHI",   ParseInt(TextBox10.Text)),
        new SqlParameter("@Z2_BIAOZHUN", ParseInt(TextBox8.Text)),
        new SqlParameter("@Z2_FENZHI",   ParseInt(TextBox11.Text)),
        new SqlParameter("@Z3_BIAOZHUN", ParseInt(TextBox9.Text)),
        new SqlParameter("@Z3_FENZHI",   ParseInt(TextBox12.Text)),
    };

    // 2. 執行預存程序
    try
    {
        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand("insert_Update_PROJECT_STANDARD", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameters);
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        lblMsg.Text = "儲存成功";
    }
    catch (Exception ex)
    {
        // 記錄日誌
        lblMsg.Text = "儲存失敗:" + ex.Message;
    }
}

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

進階優化:Table-Valued Parameter (TVP) 替代大量參數

當參數超過 20+ 或結構重複時,SQL Server 2008+ 支援 **Table-Valued Parameter**,將資料表型別當參數傳遞:

1. 建立資料表型別

CREATE TYPE dbo.ProjectStandardType AS TABLE
(
    ProjectId INT NOT NULL,
    Category  VARCHAR(20) NOT NULL, -- 'V' 或 'Z'
    Seq       TINYINT NOT NULL,     -- 1,2,3
    KHBZ      INT NOT NULL,
    FZ        INT NOT NULL
);

2. 修改預存程序

CREATE PROCEDURE usp_SaveProjectStandards
    @Standards dbo.ProjectStandardType READONLY
AS
BEGIN
    INSERT INTO T_LEARNING_PROJECT_STANDARD (Sid, KHBZ, FZ)
    SELECT ProjectId, KHBZ, FZ FROM @Standards;
END

3. C# 傳遞 DataTable

var tvp = new DataTable();
tvp.Columns.Add("ProjectId", typeof(int));
tvp.Columns.Add("Category", typeof(string));
tvp.Columns.Add("Seq", typeof(byte));
tvp.Columns.Add("KHBZ", typeof(int));
tvp.Columns.Add("FZ", typeof(int));

// 填入 6 筆資料
AddRow(tvp, projectId, "V", 1, TextBox1, TextBox4);
AddRow(tvp, projectId, "V", 2, TextBox2, TextBox5);
AddRow(tvp, projectId, "V", 3, TextBox3, TextBox6);
AddRow(tvp, projectId, "Z", 1, TextBox7, TextBox10);
AddRow(tvp, projectId, "Z", 2, TextBox8, TextBox11);
AddRow(tvp, projectId, "Z", 3, TextBox9, TextBox12);

var param = new SqlParameter("@Standards", tvp) { SqlDbType = SqlDbType.Structured, TypeName = "dbo.ProjectStandardType" };
cmd.Parameters.Add(param);

最佳實務檢查清單

  • ✅ **參數化查詢**:絕不字串拼接 SQL,所有輸入經 `SqlParameter`。
  • ✅ **輸入驗證**:前端 `RequiredFieldValidator` + `RangeValidator`,後端 `int.TryParse` + 業務規則驗證。
  • ✅ **交易控制**:多筆 INSERT 應包裹在 `TransactionScope` 或預存程序 `BEGIN TRAN / COMMIT / ROLLBACK` 中。
  • ✅ **錯誤處理**:`TRY...CATCH` (SQL) / `try-catch` (C#) 記錄詳細錯誤上下文。
  • ✅ **連線池**:`using` 區塊確保連線釋放回池。
  • ✅ **命名一致性**:參數名稱 `@PARAM_NAME` 與 SP 定義完全一致 (區分大小寫視資料庫排序規則)。

總結

預存程序 + `SqlParameter` 陣列是 ASP.NET Web Forms 時代批次寫入多組結構化資料的標準做法。現代架構建議改用 **Entity Framework Core / Dapper** 搭配 **Table-Valued Parameter** 或 **批次 INSERT (VALUES (...), (...), ...)**,結合 **MediatR / CQRS** 模式實現關注點分離,但在維護既有系統時,理解參數對應、型別轉換、交易邊界與錯誤處理的完整流程仍是核心基本功。

copyright © 萬盛學電腦網 all rights reserved