(ASP.NET)修改和删除DataGrid行——数据库访问

本程序涉及到数据库的添加，修改和删除操作。

懒得写了，把界面贴出来，照着界面画就可以了。本例数据库：SqlServer2000附带的pubs数据库，看一下连接字符串就很清楚了。如果要在本机器上运行，把uid和pwd改成你自己SQL登陆用户名和密码。

创建一个WEB页面，命名为：Add.aspx。

界面设计如图：

Add.aspx代码：

<%@ Page language="c#" Codebehind="Add.aspx.cs" AutoEventWireup="false" Inherits="TeachShow.Charpter7.AccessDataBase.Add" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >

<HTML>

<HEAD>

</HEAD>

<tr>

<td class="title" vAlign="top" width="429" colSpan="2" height="31">添加一个新的发行者</td>

</tr>

<tr>

<td vAlign="top" width="79" height="23">发行者ID：</td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td vAlign="top" align="center" width="429" colSpan="2" height="24"><asp:linkbutton id="LinkButton1" runat="server">提交到数据库</asp:linkbutton></td>

</tr>

<tr>

<asp:DataGrid id="DataGrid1" runat="server" Height="120px" CssClass="general" Width="428px">

<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="更新" CancelText="取消" EditText="编辑">

</asp:EditCommandColumn>

<asp:ButtonColumn Text="删除" CommandName="Delete"></asp:ButtonColumn>

</Columns>

</asp:DataGrid></td>

</tr>

</table>

</center>

</div>

</form>

</body>

</HTML>

Add.asp.cs代码：

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace TeachShow.Charpter7.AccessDataBase

{

/// <summary>

/// Add 的摘要说明。

/// </summary>

public class Add : System.Web.UI.Page

{

protected System.Web.UI.WebControls.TextBox TextBox1;

protected System.Web.UI.WebControls.TextBox TextBox2;

protected System.Web.UI.WebControls.TextBox TextBox3;

protected System.Web.UI.WebControls.TextBox TextBox4;

protected System.Web.UI.WebControls.LinkButton LinkButton1;

protected System.Web.UI.WebControls.DataGrid DataGrid1;

protected System.Web.UI.WebControls.TextBox TextBox5;

private void Page_Load(object sender, System.EventArgs e)

{

// 在此处放置用户代码以初始化页面

if(!this.IsPostBack)

{

this.BindGrid();

}

#region Web 窗体设计器生成的代码

override protected void OnInit(EventArgs e)

{

// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。

InitializeComponent();

base.OnInit(e);

}

/// <summary>

/// 设计器支持所需的方法 - 不要使用代码编辑器修改

/// 此方法的内容。

/// </summary>

private void InitializeComponent()

{

this.LinkButton1.Click += new System.EventHandler(this.LinkButton1_Click);

this.DataGrid1.Disposed += new System.EventHandler(this.DataGrid1_Disposed);

this.DataGrid1.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_CancelCommand);

this.DataGrid1.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_EditCommand);

this.DataGrid1.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_UpdateCommand);

this.DataGrid1.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_DeleteCommand);

this.DataGrid1.SelectedIndexChanged += new System.EventHandler(this.DataGrid1_SelectedIndexChanged);

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

private void LinkButton1_Click(object sender, System.EventArgs e)

{

AddPublisher();

}

/// <summary>

/// 添加

/// </summary>

private void AddPublisher()

{

string sql="insert into publishers(pub_id,pub_name,city,state,country) values(@pubid,@pubname,@city,@state,@country)";

SqlConnection con=new SqlConnection("server=accp-lzh;uid=sa;pwd=sasa;database=pubs");

SqlCommand cmd=new SqlCommand(sql,con);

cmd.Parameters.Add(new SqlParameter("@pubid",SqlDbType.Char,4));

cmd.Parameters["@pubid"].Value=this.TextBox1.Text;

cmd.Parameters.Add(new SqlParameter("@pubname",SqlDbType.VarChar ,40));

cmd.Parameters["@pubname"].Value=this.TextBox2.Text;

cmd.Parameters.Add(new SqlParameter("@city",SqlDbType.Char,20));

cmd.Parameters["@city"].Value=this.TextBox3.Text;

cmd.Parameters.Add(new SqlParameter("@state",SqlDbType.Char,2));

cmd.Parameters["@state"].Value=this.TextBox4.Text;

cmd.Parameters.Add(new SqlParameter("@country",SqlDbType.VarChar ,30));

cmd.Parameters["@country"].Value=this.TextBox5.Text;

cmd.Connection.Open();

cmd.ExecuteNonQuery();

cmd.Connection.Close();

this.TextBox1.Text="";

this.TextBox2.Text="";

this.TextBox3.Text="";

this.TextBox4.Text="";

this.TextBox5.Text="";

this.BindGrid();

}

private void BindGrid()

{

SqlConnection con=new SqlConnection("server=accp-lzh;uid=sa;pwd=sasa;database=pubs");

SqlDataAdapter mysqlcom=new SqlDataAdapter("select * from publishers where pub_id like '99%'",con);

DataSet myds=new DataSet();

mysqlcom.Fill(myds,"publishers");

this.DataGrid1.DataSource=myds.Tables["publishers"].DefaultView ;

this.DataGrid1.DataBind();

}

/// <summary>

/// 点击编辑时触发事件

/// </summary>

/// <param name="source"></param>

/// <param name="e"></param>

private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

this.DataGrid1.EditItemIndex=(int)e.Item.ItemIndex;

this.BindGrid();

}

/// <summary>

/// 点击取消时触发该事件。

/// </summary>

/// <param name="source"></param>

/// <param name="e"></param>

private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

this.DataGrid1.Columns[0].HeaderText="已取消";

this.DataGrid1.EditItemIndex=-1;

this.BindGrid();

}

private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)

{

}

/// <summary>

/// 点击更新时触发该事件。

/// </summary>

/// <param name="source"></param>

/// <param name="e"></param>

private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

SqlConnection con=new SqlConnection("server=accp-lzh;uid=sa;pwd=sasa;database=pubs");

SqlCommand selectCmd=con.CreateCommand();

selectCmd.CommandType=CommandType.Text;

selectCmd.CommandText="select * from publishers where pub_id like '99%'";

SqlDataAdapter sqlAdatper=new SqlDataAdapter();

sqlAdatper.SelectCommand=selectCmd;

DataSet ds=new DataSet();

con.Open();

sqlAdatper.Fill(ds,"publishers");

con.Close();

SqlCommand updateCmd=con.CreateCommand();

updateCmd.CommandText="update publishers set pub_name=@pubname,city=@city,state=@state,country=@country where pub_id=@pub_id";

SqlParameter PubNamePar=new SqlParameter("@pubname",SqlDbType.VarChar,40,"pub_name");

updateCmd.Parameters.Add(PubNamePar);

SqlParameter CityPar=new SqlParameter("@city",SqlDbType.VarChar,20,"city");

updateCmd.Parameters.Add(CityPar);

SqlParameter StatePar=new SqlParameter("@state",SqlDbType.Char,2,"state");

updateCmd.Parameters.Add(StatePar);

SqlParameter CountryPar=new SqlParameter("@country",SqlDbType.VarChar,30,"country");

updateCmd.Parameters.Add(CountryPar);

SqlParameter PubIDPar=new SqlParameter("@pub_id",SqlDbType.Char,4,"pub_id");

PubIDPar.SourceVersion=DataRowVersion.Original;

updateCmd.Parameters.Add(PubIDPar);

sqlAdatper.UpdateCommand=updateCmd;

DataTable table=ds.Tables["publishers"];

table.PrimaryKey=new DataColumn[]

{

table.Columns["pub_id"]

};

DataRow row=table.Rows.Find(((TextBox)(e.Item.Cells[2].Controls[0])).Text);

row["pub_name"]=((TextBox)(e.Item.Cells[3].Controls[0])).Text;

row["city"]=((TextBox)(e.Item.Cells[4].Controls[0])).Text;

row["state"]=((TextBox)(e.Item.Cells[5].Controls[0])).Text;

row["country"]=((TextBox)(e.Item.Cells[6].Controls[0])).Text;

con.Open();

sqlAdatper.Update(table);

con.Close();

this.DataGrid1.EditItemIndex=-1;

this.BindGrid();

}

/// <summary>

/// 点击删除时触发该事件

/// </summary>

/// <param name="source"></param>

/// <param name="e"></param>

private void DataGrid1_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

SqlConnection con=new SqlConnection("server=accp-lzh;uid=sa;pwd=sasa;database=pubs");

SqlCommand selectCmd=con.CreateCommand();

selectCmd.CommandText="select * from publishers where pub_id like '99%'";

SqlCommand deleteCmd=con.CreateCommand();

deleteCmd.CommandText="delete from publishers where pub_id=@pub_id";

SqlParameter PubIdPar=new SqlParameter("@pub_id",SqlDbType.Char,4,"pub_id");

PubIdPar.SourceVersion=DataRowVersion.Original;

deleteCmd.Parameters.Add(PubIdPar);

SqlDataAdapter sqlAdapter=new SqlDataAdapter();

sqlAdapter.SelectCommand=selectCmd;

sqlAdapter.DeleteCommand=deleteCmd;

DataSet ds=new DataSet();

con.Open();

sqlAdapter.Fill(ds,"publishers");

DataTable table=new DataTable();

table=ds.Tables["publishers"];

table.PrimaryKey=new DataColumn[]//定义主键，便于查找

{

table.Columns["pub_id"]

};

DataRow row=table.Rows.Find(e.Item.Cells[2].Text);

row.Delete();

sqlAdapter.Update(table);

con.Close();

this.DataGrid1.EditItemIndex=-1;

this.BindGrid();

}

private void DataGrid1_Disposed(object sender, System.EventArgs e)

{

}

下面是修改时的截图: