极光下载站 - 打造齐全的绿色软件家园

极光下载站

当前位置: 首页 技术开发 NET专区 (ASP.NET)修改和删除DataGrid行——数据库访问

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

时间:2006/7/19 6:07:42 作者:佚名 人气:467

(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>

<title>Add</title>

<LINK href="../../Style.css" type="text/css" rel="stylesheet">

<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">

<meta content="C#" name="CODE_LANGUAGE">

<meta content="JavaScript" name="vs_defaultClientScript">

<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">

</HEAD>

<body MS_POSITIONING="GridLayout">

<form id="Form1" method="post" runat="server">

<div align="center">

<center>

<table class="smallBlack" height="318" cellSpacing="0" cellPadding="0" width="429" border="0">

<tr>

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

</tr>

<tr>

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

<td vAlign="top" width="350" height="23"><asp:textbox id="TextBox1" runat="server" Height="18px" CssClass="smallRed"></asp:textbox><FONT face="宋体">(以99打头,共4位数字)</FONT></td>

</tr>

<tr>

<td vAlign="top" width="79" height="23"><FONT face="宋体">姓名:</FONT></td>

<td vAlign="top" width="350" height="23"><asp:textbox id="TextBox2" runat="server" Height="18px" CssClass="smallRed"></asp:textbox></td>

</tr>

<tr>

<td vAlign="top" width="79" height="23"><FONT face="宋体">城市:</FONT></td>

<td vAlign="top" width="350" height="23"><asp:textbox id="TextBox3" runat="server" Height="18px" CssClass="smallRed"></asp:textbox></td>

</tr>

<tr>

<td vAlign="top" width="79" height="23"><FONT face="宋体">省份:</FONT></td>

<td vAlign="top" width="350" height="23"><asp:textbox id="TextBox4" runat="server" Height="18px" CssClass="smallRed"></asp:textbox><FONT face="宋体">(2个字符)</FONT></td>

</tr>

<tr>

<td vAlign="top" width="79" height="24"><FONT face="宋体">国家:</FONT></td>

<td vAlign="top" width="350" height="24"><asp:textbox id="TextBox5" runat="server" Height="18px" CssClass="smallRed"></asp:textbox></td>

</tr>

<tr>

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

</tr>

<tr>

<td width="429" height="147" valign="top" colspan="2">

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

<ItemStyle Width="50px"></ItemStyle>

<Columns>

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

<HeaderStyle Width="60px"></HeaderStyle>

</asp:EditCommandColumn>

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

</Columns>

</asp:DataGrid></td>

</tr>



</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)

{



}

}
<
标签
分享到:

公众号