莊老麥的生活手札

在上一篇文章[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();
 }

輸出結果:

     

編輯:

 

刪除: