ASP.NET 2.0 GridView 控制項進階應用：新增記錄與批次更新完整實作

GridView 新增記錄：FooterTemplate 實作空白輸入列

GridView 預設不支援在尾端顯示空白列供新增。透過 ShowFooter="True" 搭配 FooterTemplate 可實作內嵌新增介面。

ASPX 標記完整範例

<asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1" 
    DataKeyNames="CustomerID" AutoGenerateColumns="False" ShowFooter="True">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="CustomerIDLabel" Runat="server" Text='<%# Eval("CustomerID") %>' />
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="CustomerIDTextBox" Runat="server" />
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="CompanyNameLabel" Runat="server" Text='<%# Eval("CompanyName") %>' />
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="CompanyNameTextBox" Runat="server" />
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:DropDownList ID="ContactTitleDropDown" Runat="server" 
                    SelectedValue='<%# Bind("ContactTitle") %>' 
                    DataSourceID="SqlDataSource3" DataTextField="ContactTitle" DataValueField="ContactTitle" />
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ContactTitleDropDownList" Runat="server" 
                    DataSourceID="SqlDataSource2" DataTextField="ContactTitle" DataValueField="ContactTitle" />
                <asp:Button ID="Button1" Runat="server" Text="新增" OnClick="Button1_Click" />
                <asp:Button ID="CancelButton1" Runat="server" Text="取消" OnClick="CancelButton1_Click" />
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

後端事件處理

<script runat="server">
void CancelButton1_Click(object sender, EventArgs e)
{
    GridView1.ShowFooter = false; // 隱藏 Footer，取消新增
}

void AddButton1_Click(object sender, EventArgs e)
{
    GridView1.ShowFooter = true; // 顯示 Footer，進入新增模式
}

void Button1_Click(object sender, EventArgs e)
{
    // 從 FooterRow 取得使用者輸入值
    TextBox customerID = GridView1.FooterRow.FindControl("CustomerIDTextBox") as TextBox;
    TextBox companyName = GridView1.FooterRow.FindControl("CompanyNameTextBox") as TextBox;
    DropDownList contactTitle = GridView1.FooterRow.FindControl("ContactTitleDropDownList") as DropDownList;

    // 設定 SqlDataSource 參數並執行插入
    SqlDataSource1.InsertParameters["CustomerID"].DefaultValue = customerID.Text;
    SqlDataSource1.InsertParameters["CompanyName"].DefaultValue = companyName.Text;
    SqlDataSource1.InsertParameters["ContactTitle"].DefaultValue = contactTitle.SelectedValue;
    SqlDataSource1.Insert();
}
</script>

SqlDataSource 設定關鍵

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
    InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactTitle]) 
                   VALUES (@CustomerID, @CompanyName, @ContactTitle)"
    SelectCommand="SELECT TOP 5 [CustomerID], [CompanyName], [ContactTitle] FROM [Customers]"
    ConnectionString="...">
    <InsertParameters>
        <asp:Parameter Type="String" Name="CustomerID" />
        <asp:Parameter Type="String" Name="CompanyName" />
        <asp:Parameter Type="String" Name="ContactTitle" />
    </InsertParameters>
</asp:SqlDataSource>

GridView 批次更新：兩種實作策略比較

方法一：SqlDataSource 逐筆更新（簡單但效能較差）

void Button1_Click(object sender, EventArgs e)
{
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        GridViewRow row = GridView1.Rows[i];
        SqlDataSource1.UpdateParameters[0].DefaultValue = 
            ((TextBox)row.Cells[0].FindControl("TextBox2")).Text;
        SqlDataSource1.UpdateParameters[1].DefaultValue = 
            ((TextBox)row.Cells[1].FindControl("TextBox3")).Text;
        SqlDataSource1.UpdateParameters[2].DefaultValue = 
            GridView1.DataKeys[i].Value.ToString();
        SqlDataSource1.Update(); // 每筆建立連線、執行、關閉
    }
}

缺點：每筆資料獨立資料庫往返，大量資料時效能嚴重下降。

方法二：批次 SQL 字串組裝一次執行（高效能）

protected void Button1_Click(object sender, EventArgs e)
{
    StringBuilder query = new StringBuilder();
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        GridViewRow row = GridView1.Rows[i];
        string v1 = ((TextBox)row.Cells[0].FindControl("TextBox2")).Text.Replace("'", "''");
        string v2 = ((TextBox)row.Cells[1].FindControl("TextBox3")).Text.Replace("'", "''");
        string v3 = GridView1.DataKeys[i].Value.ToString();
        
        query.Append("UPDATE [Customers] SET [CompanyName] = '").Append(v1)
             .Append("', [ContactTitle] = '").Append(v2)
             .Append("' WHERE [CustomerID] = '").Append(v3).Append("';\n");
    }
    
    using (SqlConnection con = new SqlConnection(connStr))
    using (SqlCommand cmd = new SqlCommand(query.ToString(), con))
    {
        con.Open();
        cmd.ExecuteNonQuery(); // 單次資料庫往返執行所有 UPDATE
    }
}

優點：單一連線、單一指令批次執行，效能大幅提升。

注意：需防範 SQL Injection，實務上建議使用參數化查詢或 Table-Valued Parameters (TVP)。

連接字串管理（Web.config）

<connectionStrings>
    <add name="NorthwindConnectionString" 
         connectionString="Data Source=...;Initial Catalog=Northwind;User ID=sa;Password=xxx" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

程式碼讀取：ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString

現代化遷移建議

• ASP.NET Core 採用 Entity Framework Core + Razor Pages / Blazor
• 批次更新改用 BulkUpdate 擴充套件或原生 ExecuteSqlRaw
• GridView 替代方案：MatBlazor、Radzen、MudBlazor 等元件庫
• 連線字串改用 appsettings.json + IConfiguration