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

2024-07-10 12:57:27

字体：大中小

来源：转载

供稿：网友

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

</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 [email protected],[email protected],[email protected],[email protected] where [email protected]_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 [email protected]_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)

{

}

}

}

上一篇：ASP.NET中使用Server.Transfer()方法在页间传值

下一篇：.Net的Outofmemory异常及大内存使用