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

发布: 2007-6-30 18:56 | 作者: admin | 来源: | 查看: 20次 | 进入软件测试论坛讨论

     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>