利用DataGrid编辑、修改、删除记录

下面的这个例子提供了利用DataGrid编辑、修改、删除记录的方法，数据库字段名称和类型如下。

数据库aa中表 people：People.sql

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[People]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[People] GO CREATE TABLE [dbo].[People] ( [pkID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [char] (100) COLLATE Chinese_PRC_CI_AS NULL , [LastName] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO 

EditDataGridCS.aspx

  <%@ Page Language="c#" debug="true"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> //make first sql String sql = ""; String strCnn = "Data Source=.;Initial Catalog=aa;User Id=sa;Password=;"; //create a datasource function public ICollection CreateDataSource () { SqlConnection conn = new SqlConnection(strCnn); SqlDataAdapter db_sqladaptor = new SqlDataAdapter(sql,conn); DataSet ds = new DataSet(); db_sqladaptor.Fill(ds,"MyDataResult"); DataView myView = ds.Tables["MyDataResult"].DefaultView; return myView; } //do page load public void Page_Load(Object sender, EventArgs e) { strCnn = "Data Source=.;Initial Catalog=aa;User Id=sa;Password=;"; if (!IsPostBack) { sql = "Select * FROM People"; People.DataSource = CreateDataSource(); People.DataBind(); } } public void Page_Grid(Object sender, DataGridPageChangedEventArgs e) { sql = "Select * FROM People"; // Set CurrentPageIndex to the page the user clicked. People.CurrentPageIndex = e.NewPageIndex; // Rebind the data. People.DataSource = CreateDataSource(); People.DataBind(); } public void People_Edit(Object sender, DataGridCommandEventArgs e) { sql = "Select * FROM People"; People.EditItemIndex = e.Item.ItemIndex; People.DataSource = CreateDataSource(); People.DataBind(); } public void People_Cancel(Object sender, DataGridCommandEventArgs e) { sql = "Select * FROM People"; People.EditItemIndex = -1; People.DataSource = CreateDataSource(); People.DataBind(); } public void People_Update(Object sender, DataGridCommandEventArgs e) { string FirstName = ((TextBox)e.Item.Cells[1].Controls[1]).Text; string LastName = ((TextBox)e.Item.Cells[2].Controls[1]).Text; SqlConnection connUpdate = new SqlConnection(strCnn); connUpdate.Open(); String sql_edit = "UPDATE People " + "SET FirstName = '" + FirstName.Replace("'","''")+ "'," + "LastName = '" + LastName.Replace("'","''")+ "'" + " WHERE pkID = " + e.Item.Cells[0].Text; SqlCommand sqlCommandUpdate = new SqlCommand(sql_edit,connUpdate); sqlCommandUpdate.ExecuteNonQuery(); connUpdate.Close(); sql = "Select * FROM People"; People.EditItemIndex = -1; People.DataSource = CreateDataSource(); People.DataBind(); } public void People_Delete(Object sender, DataGridCommandEventArgs e) { SqlConnection connDel = new SqlConnection(strCnn); connDel.Open(); String sql_Del = "DELETE FROM People " + " WHERE pkID = " + e.Item.Cells[0].Text; SqlCommand sqlCommandDel = new SqlCommand(sql_Del,connDel); sqlCommandDel.ExecuteNonQuery(); connDel.Close(); sql = "Select * FROM People"; People.EditItemIndex = -1; People.DataSource = CreateDataSource(); People.DataBind(); } </script> <font face="arial" size="3"> <b>Edit People</b> </font> <br> <form runat="server"> <asp:DataGrid id="People" runat="server" BorderColor="green" Width="640" PageSize="5" AllowPaging="true" OnPageIndexChanged="Page_Grid" BorderWidth="1" CellPadding="3" AutoGenerateColumns="false" ShowHeader="true" Visible="true" OnEditCommand="People_Edit" OnCancelCommand="People_Cancel" OnUpdateCommand="People_Update" OnDeleteCommand="People_Delete"> <HeaderStyle BorderColor="White" BackColor="black" ForeColor="White" Font-Bold="True" Font-Name="Arial" Font-Size="9" HorizontalAlign="Center"/> <ItemStyle BorderColor="" BackColor="#FFFFF0" ForeColor="Black" Font-Name="Arial" Font-Size="8" Font-Bold="False" HorizontalAlign="Center"/> <EditItemStyle BorderColor="" BackColor="#FFFFF0" ForeColor="Black" Font-Name="Arial" Font-Size="7" Font-Bold="False" HorizontalAlign="Center"/> <PagerStyle Mode="NumericPages" Font-Size="8"/> <Columns> <asp:BoundColumn HeaderText="ID" ReadOnly="true" DataField="pkID"/> <asp:TemplateColumn> <HeaderTemplate> <b> First Name </b> </HeaderTemplate> <ItemTemplate> <asp:Label Text='<%# DataBinder.Eval(Container.DataItem, "FirstName").ToString().Trim() %>' runat="server"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="FirstName" Text=' <%# DataBinder.Eval(Container.DataItem, "FirstName").ToString().Trim() %>' runat="server" Width="100%"/> </EditItemTemplate> </asp:TemplateColumn> <asp:TemplateColumn> <HeaderTemplate> <b> Last Name </b> </HeaderTemplate> <ItemTemplate> <asp:Label Width="200" Text='<%# DataBinder.Eval(Container.DataItem, "LastName").ToString().Trim() %>' runat="server"/> </ItemTemplate> <EditItemTemplate> <asp:TextBox id="LastName" Text='<br> <%# DataBinder.Eval(Container.DataItem, "LastName").ToString().Trim() %>' runat="server" Width="100%"/> </EditItemTemplate> </asp:TemplateColumn> <asp:EditCommandColumn ButtonType="LinkButton" CancelText="Cancel" EditText="Edit" UpdateText="Update" /> <asp:ButtonColumn Text= "Delete" CommandName="Delete"></asp:ButtonColumn> </Columns> </asp:DataGrid> </form>