ASP.NET对数据库的基本操作——插入，删除，修改（下）
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace WebApplication1
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlForm Form1;
protected System.Web.UI.WebControls.Repeater Repeater1;
protected System.Web.UI.WebControls.TextBox tbName;
protected System.Web.UI.WebControls.TextBox tbAge;
protected System.Web.UI.WebControls.TextBox tbTelephone;
protected System.Web.UI.WebControls.TextBox tbAddress;
protected System.Web.UI.WebControls.DropDownList ddlEducation;
protected System.Web.UI.HtmlControls.HtmlTextArea taExperience;
protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
protected System.Web.UI.WebControls.Button btnUpdate;
protected System.Web.UI.WebControls.Button btnDelete;
protected System.Web.UI.WebControls.Button btnClose;
protected System.Web.UI.WebControls.DropDownList ddlSex;
protected System.Web.UI.WebControls.Label lbMessage;
protected System.Web.UI.WebControls.Button btnAdd;
public void Binder()//显示数据绑定函数
{
string Connstring = "database=test;password=;user=sa;server=.";
string Sqlstring = "SELECT * FROM UserInformation ORDER BY Name";
SqlConnection MyConn = new SqlConnection(Connstring);
SqlDataAdapter MyDa = new SqlDataAdapter(Sqlstring,MyConn);
DataSet MyDs = new DataSet();
MyDa.Fill(MyDs,"UserInformation");
Repeater1.DataSource=MyDs;
Repeater1.DataBind();
}
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
this.tbName.Text="";
this.tbAddress.Text="";
this.taExperience.Value="";
this.ddlEducation.SelectedItem.Text="";
this.ddlSex.SelectedItem.Text="";
this.tbTelephone.Text="";
this.tbTelephone.Text="";
}
Binder();//绑定数据
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
this.btnClose.Click += new System.EventHandler(this.btnClose_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void btnAdd_Click(object sender, System.EventArgs e)
{ //增加一个用户
string Connstring = "database=test;password=;user=sa;server=.";
string Sqlstring = "SELECT * FROM UserInformation WHERE Name='"+this.tbName.Text+"'";
SqlConnection MyConn = new SqlConnection(Connstring);
SqlDataAdapter MyDa = new SqlDataAdapter(Sqlstring,MyConn);
DataSet MyDs = new DataSet();
MyDa.Fill(MyDs,"UserInformation");
if(MyDs.Tables["UserInformation"].Rows.Count>0)
{
this.lbMessage.Text="<font color=red>"+"该人已经存在,请换一个名字吧!"+"</font>";
}
else
{
if(Page.IsValid)
{
string Name = this.tbName.Text;
string Sex = this.ddlSex.SelectedItem.Text;
string Age = this.tbAge.Text;
string Telephone = this.tbTelephone.Text;
string Address = this.tbAddress.Text;
string Education = this.ddlEducation.SelectedItem.Text;
string Experience = this.taExperience.Value;
string Conn = "database=test;password=;user=sa;server=.";
string SqlAdd =string.Format( "INSERT INTO UserInformation VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",Name,Sex,Age,Education,Telephone,Address,Experience);
SqlConnection MyConnection = new SqlConnection(Conn);
SqlDataAdapter MyDataAdapter = new SqlDataAdapter(SqlAdd,MyConnection);
DataSet MyDataSet = new DataSet();
MyDataAdapter.Fill(MyDataSet,"UserInformation");
Binder();
}
}
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{ //修改用户信息
string Connstring = "database=test;password=;user=sa;server=.";
string Sqlstring = "SELECT * FROM UserInformation WHERE Name='"+this.tbName.Text+"'";
SqlConnection MyConn = new SqlConnection(Connstring);
SqlDataAdapter MyDa = new SqlDataAdapter(Sqlstring,MyConn);
DataSet MyDs = new DataSet();
MyDa.Fill(MyDs,"UserInformation");
if(MyDs.Tables["UserInformation"].Rows.Count>0)
{
if(Page.IsValid)
{
string Name = this.tbName.Text;
string Sex = this.ddlSex.SelectedItem.Text;
string Age = this.tbAge.Text;
string Telephone = this.tbTelephone.Text;
string Address = this.tbAddress.Text;
string Education = this.ddlEducation.SelectedItem.Text;
string Experience = this.taExperience.Value;
string Conn = "database=test;password=;user=sa;server=.";
string SqlUpdate = "UPDATE UserInformation SET Name='"+Name+"',Sex='"+Sex+"',Age='"+Age+"',Telephone='"+Telephone+"',Address='"+Address+"',Education='"+Education+"',Experience='"+Experience+"'WHERE Name='"+Name+"'";
SqlConnection MyConnection = new SqlConnection(Conn);
SqlDataAdapter MyDataAdapter = new SqlDataAdapter(SqlUpdate,MyConnection);
DataSet MyDataSet = new DataSet();
MyDataAdapter.Fill(MyDataSet,"UserInformation");
Binder();
}
}
else
{
this.lbMessage.Text="<font color=red>"+"该人不存在!"+"</font>";
}
}
private void btnDelete_Click(object sender, System.EventArgs e)
{//删除用户
string Connstring = "database=test;password=;user=sa;server=.";
string Sqlstring = "SELECT * FROM UserInformation WHERE Name='"+this.tbName.Text+"'";
SqlConnection MyConn = new SqlConnection(Connstring);
SqlDataAdapter MyDa = new SqlDataAdapter(Sqlstring,MyConn);
DataSet MyDs = new DataSet();
MyDa.Fill(MyDs,"UserInformation");
if(MyDs.Tables["UserInformation"].Rows.Count>0)
{
if(Page.IsValid)
{
string Name = this.tbName.Text;
string Sex = this.ddlSex.SelectedItem.Text;
string Age = this.tbAge.Text;
string Telephone = this.tbTelephone.Text;
string Address = this.tbAddress.Text;
string Education = this.ddlEducation.SelectedItem.Text;
string Experience = this.taExperience.Value;
string Conn = "database=test;password=;user=sa;server=.";
string SqlDelete = "DELETE FROM UserInformation WHERE Name='"+this.tbName.Text+"'";
SqlConnection MyConnection = new SqlConnection(Conn);
SqlDataAdapter MyDataAdapter = new SqlDataAdapter(SqlDelete,MyConnection);
DataSet MyDataSet = new DataSet();
MyDataAdapter.Fill(MyDataSet,"UserInformation");
Binder();
}
}
else
{
this.lbMessage.Text="<font color=red>"+"该人不存在!"+"</font>";
}
}
private void btnClose_Click(object sender, System.EventArgs e)
{//关闭当前窗口
this.btnClose.Attributes.Add("Onclick","window.close();");
}
}
}
在进行上面的编码之前还应该建立一个数据库，建立数据库的代码如下：
create database test;
CREATE TABLE UserInformation(
Name varchar(20) NOT NULL,
Sex char(2),
Age int,
Education varchar(50),
Telephone char(11),
Address varchar(50),
Experience TEXT,
)
