[ASP]自訂GridView(二.彈性讀取、修改、刪除)－莊老麥的生活手札

在上一篇文章[ASP]自訂GridVieew(連接)，我們提到了自訂方式

這一篇要來紀錄的是更為了彈性調整的刪除與修改的範例

事前準備：

環境: VS2012

資料庫: SQL Server 2008

與上一篇不同的是這邊為了介紹彈性的資料讀取與資料刪除修改

增加了一個欄位以及設定了主鍵

資料表:

CREATE TABLE candidate(
CNo Int,
CName Nvarchar(max),
CAddress Nvarchar(max),
CGender Bit,
Primary Key(CNo)
)

資料:

INSERT INTO candidate VALUES(1,'TOM','Taiwan','True');
INSERT INTO candidate VALUES(2,'Kally','USA','False');
INSERT INTO candidate VALUES(3,'John','UK','False');
INSERT INTO candidate VALUES(4,'Stanley','Japan','True');

由於要提高彈性，所以這一次取得DataSet並由GridView.Bind()方式，不藉由AutoGenerateColumns=true去產生資料欄位

1.先設定需要擁有的資料欄位

此GridView包含指令部分(四個按鈕)以及欄位部分
指令藉由CommandName去設定執行指令，並設定指令事件(RowCancelingEdit、RowCancelingEdit、RowDeleting、RowUpdating)

欄位的部分'<%#Bind("欄位名稱") %>' 去連結DataSet資料欄位,至於資料呈現位置依據控制項去調整

CNo由於是主鍵故在此也設定Readonly

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
                <Columns>
                    <%--指令--%>
                    <asp:TemplateField>
                        <EditItemTemplate>
                            <asp:Button ID="update_btn" runat="server" Text="更新" CommandName="Update" /><br />
                            <asp:Button ID="cancel_btn" runat="server" Text="取消" CommandName="Cancel" />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Button ID="edit_btn" runat="server" Text="編輯" CommandName="Edit" /><br />
                            <asp:Button ID="delete_btn" runat="server" Text="刪除" CommandName="Delete" OnClientClick="return confirm('確定要刪除此筆資料嗎?');" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <%--欄位--%>
                    <asp:BoundField HeaderText="CNo" DataField="CNo" ReadOnly="true" />
                    <asp:TemplateField HeaderText="CName">
                        <ItemTemplate>
                            <asp:Label ID="lb_Nmae" runat="server" Text='<%#Bind("CName") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="tbx_Name" runat="server" Text='<%#Bind("CName") %>'></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="CAddress">
                        <ItemTemplate>
                            <asp:Label ID="lb_Address" runat="server" Text='<%#Bind("CAddress") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="tbx_Address" runat="server" Text='<%#Bind("CAddress") %>'></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="CGender">
                        <EditItemTemplate>
                            <asp:DropDownList ID="dll_MbGender" runat="server" SelectedValue='<%#Bind("CGender") %>'>
                                <asp:ListItem Value="False">Female</asp:ListItem>
                                <asp:ListItem Value="True">Male</asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:DropDownList ID="dll_MbGender" runat="server" Enabled="false" SelectedValue='<%#Bind("CGender") %>'>
                                <asp:ListItem Value="False">Female</asp:ListItem>
                                <asp:ListItem Value="True">Male</asp:ListItem>
                            </asp:DropDownList>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

2.此篇省略與資料庫連結部分，直接提修改刪除的程式碼，由於會使用到所以請於前一篇去做參考

//點擊編輯按鈕
   protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        GVgetData();
    } 
//點擊取消按鈕
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        GVgetData();
    }

 //點擊更新按鈕
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        String updateString = "UPDATE candidate SET CName=@CName,CAddress=@CAddress,CGender=@CGender WHERE CNo=@CNo";
        SqlConnection cn = new SqlConnection(connectString);
        SqlCommand cmd = new SqlCommand(updateString, cn);
        cmd.Parameters.Add("@CNo", SqlDbType.Int);
        cmd.Parameters.Add("@CName", SqlDbType.NChar);
        cmd.Parameters.Add("@CAddress", SqlDbType.NChar);
        cmd.Parameters.Add("@CGender", SqlDbType.NChar);
        cmd.Parameters["@CNo"].Value = GridView1.Rows[e.RowIndex].Cells[1].Text;
        cmd.Parameters["@CName"].Value = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("tbx_Name")).Text;
        cmd.Parameters["@CAddress"].Value = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("tbx_Address")).Text;
        cmd.Parameters["@CGender"].Value = ((DropDownList)GridView1.Rows[e.RowIndex].FindControl("dll_MbGender")).SelectedValue;
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
        GridView1.EditIndex = -1;
        GVgetData();
    }
    //點擊刪除按鈕
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        String deleteString = "DELETE FROM candidate WHERE CNo=@CNo";
        SqlConnection cn = new SqlConnection(connectString);
        SqlCommand cmd = new SqlCommand(deleteString, cn);
        cmd.Parameters.Add("@CNo", SqlDbType.Int);
        cmd.Parameters["@CNo"].Value = int.Parse(GridView1.Rows[e.RowIndex].Cells[1].Text);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
        GVgetData();
    }

輸出結果: