頁面包含多組相同結構的輸入欄位 (如三階段觀影標準、三階段作業批閱標準),需一次性寫入資料庫。使用預存程序 + 參數陣列可確保效能、安全性 (防 SQL Injection) 與可維護性。
insert_Update_PROJECT_STANDARDCREATE 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
<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>
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;
當參數超過 20+ 或結構重複時,SQL Server 2008+ 支援 **Table-Valued Parameter**,將資料表型別當參數傳遞:
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
);
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
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);
預存程序 + `SqlParameter` 陣列是 ASP.NET Web Forms 時代批次寫入多組結構化資料的標準做法。現代架構建議改用 **Entity Framework Core / Dapper** 搭配 **Table-Valued Parameter** 或 **批次 INSERT (VALUES (...), (...), ...)**,結合 **MediatR / CQRS** 模式實現關注點分離,但在維護既有系統時,理解參數對應、型別轉換、交易邊界與錯誤處理的完整流程仍是核心基本功。