增删改查代码
using System;
using System.Collections;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.Configuration;
using System.Xml.Linq;
public partial class charu : System.Web.UI.Page
{
private string connectionString =
WebConfigurationManager.ConnectionStrings["jxManagerConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
//下面代码屏蔽了，下拉
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
依然存在
/* SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("select * from Authors", con);
SqlDataReader reader ;
con.Open();
reader = cmd.ExecuteReader();
reader.Read();
this.lstAuthor.DataSource = reader;
this.lstAuthor.DataTextField = "au_lname";
this.lstAuthor.DataValueField = "au_id";
this.lstAuthor.DataBind();
reader.Close();
con.Close();
lstAuthor_SelectedIndexChanged1(sender, e); */
FillAuthorList();
}
}
/* protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection conn = UtilitySqlClass.OperateDataBase.ReturnConn();
try
{
conn.Open();
this.lstAuthor.Items.Add("");
string strSQL = "select CompanyType from CompanyType";
SqlCommand com = new SqlCommand(strSQL, conn);
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
this.lstAuthor.Items.Add(dr["CompanyType"].ToString());
}
}
catch (Exception err)
{
lb1Status.Text = "Error reading list of names.";
lb1Status.Text += err.Message;
}
finally
{
conn.Close();
}
}
}
**/
private void FillAuthorList()
{
lstAuthor.Items.Clear();
string selectSQL = "SELECT au_lname,au_fname,au_id FROM Authors";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
ListItem newItem = new ListItem();
newItem.Text = reader["au_lname"] + "," + reader["au_fname"];
newItem.Value = reader["au_id"].ToString();
lstAuthor.Items.Add(newItem);
}
reader.Close();
}
catch (Exception err)
{
lb1Status.Text= "Error reading list of names.";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
}
}
// private string connectionString =
//
WebConfigurationManager.ConnectionStrings["jxManagerConnectionString"].ConnectionString;
/*
* protected void lstAuthor_SelectedIndexChanged(object sender, EventArgs e)
{
string selectSQL;
selectSQL = "SELECT * FROM Authors ";
selectSQL += "WHERE au_id'" + lstAuthor.SelectedItem.Value + "'";
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
reader.Read();
TextBox1.Text = reader["au_id"].ToString();
TextBox2.Text = reader["au_fname"].ToString();
TextBox3.Text = reader["au_lname"].ToString();
TextBox4.Text = reader["phone"].ToString();
TextBox5.Text = reader["adress"].ToString();
TextBox6.Text = reader["city"].ToString();
TextBox7.Text = reader["state"].ToString();
// CheckBox1.Checked = (bool)reader["contract"];
reader.Close();
lb1Status.Text = "";
}
catch (Exception err)
{
lb1Status.Text = "Error getting author.";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
}
}
* */
protected void lstAuthor_SelectedIndexChanged1(object sender, EventArgs e)
{
/* string selectSQL;
selectSQL = "SELECT * FROM Authors ";
selectSQL += "WHERE au_id'" + lstAuthor.SelectedItem.Value + "'";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
Label10.Text = "数据库连接，打开正常～";
reader = cmd.ExecuteReader();
reader.Read();
TextBox1.Text = reader["@au_id"].ToString();
TextBox2.Text = reader["@au_fname"].ToString();
TextBox3.Text = reader["@au_lname"].ToString();
TextBox4.Text = reader["@phone"].ToString();
TextBox5.Text = reader["@adress"].ToString();
TextBox6.Text = reader["@city"].ToString();
TextBox7.Text = reader["@state"].ToString();
CheckBox1.Checked = (bool)reader["contract"];
reader.Close();
lb1Status.Text = "";
}
catch (Exception err)
{
lb1Status.Text = "Error getting author.";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
Label11.Text = "数据库已经关闭～";
} **/
string selectSQL;
selectSQL = "SELECT * FROM Authors ";
selectSQL += "WHERE au_id='" + lstAuthor.SelectedItem.Value + "'";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
reader.Read();
TextBox1.Text = reader["au_id"].ToString();
TextBox2.Text = reader["au_fname"].ToString();
TextBox3.Text = reader["au_lname"].ToString();
TextBox4.Text = reader["phone"].ToString();
TextBox5.Text = reader["address"].ToString();
TextBox6.Text = reader["city"].ToString();
TextBox7.Text = reader["state"].ToString();
// txtZip.Text = reader["zip"].ToString();
CheckBox1.Checked = (bool)reader["contract"];
reader.Close();
lb1Status.Text = "";
}
catch (Exception err)
{
lb1Status.Text = "Error getting author. ";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
}
}
//创建新表数据，清空格子
protected void CreateNew_Click(object sender, EventArgs e)
{
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
TextBox7.Text = "";
CheckBox1.Checked = false;
lb1Status.Text = "Click Insert New to add the completed record.";
}
//向表中插入数据
protected void InsertNew_Click(object sender, EventArgs e)
{
if (TextBox1.Text == "" || TextBox2.Text == "" || TextBox3.Text == "")
{
lb1Status.Text = "Records require an Id ,first name,an last name.";
return;
}
string insertSQL;
insertSQL = "INSERT INTO Authors ( ";
insertSQL += "au_id,au_fname,au_lname,";
insertSQL += "phone,address,city,state, contract)";
/* insertSQL += "VALUES ( '";
insertSQL += TextBox1.Text +"','";
insertSQL += TextBox2.Text + "','";
insertSQL += TextBox3.Text + "','";
insertSQL += TextBox4.Text + "','";
insertSQL += TextBox5.Text + "','";
insertSQL += TextBox6.Text + "','";
insertSQL += TextBox7.Text + " ') "; **/
// insertSQL += Convert.ToInt16(CheckBox1.Checked) + " ') ";
insertSQL += "VALUES ("; //存储过程添加替换
insertSQL += "@au_id,@au_fname,@au_lname,";
insertSQL += "@phone,@address,@city,@state,@contract)";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(insertSQL, con);
cmd.Parameters.AddWithValue("@au_id", TextBox1.Text);
cmd.Parameters.AddWithValue("@au_fname", TextBox2.Text);
cmd.Parameters.AddWithValue("@au_lname", TextBox3.Text);
cmd.Parameters.AddWithValue("@phone", TextBox4.Text);
cmd.Parameters.AddWithValue("@address", TextBox5.Text);
cmd.Parameters.AddWithValue("@city", TextBox6.Text);
cmd.Parameters.AddWithValue("@state", TextBox7.Text);
cmd.Parameters.AddWithValue("@contract",Convert.ToInt16 (CheckBox1.Checked));
int added = 0;
try
{
con.Open();
Label10.Text = "数据库连接，打开正常～";
added = cmd.ExecuteNonQuery();
lb1Status.Text = added.ToString() + "records inserted.";
}
catch (Exception err)
{
lb1Status.Text = "Error inserting record.";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
Label11.Text = "数据库已经关闭～";
}
if (added > 0)
{
FillAuthorList();
}
}
//更新数据表
protected void Update_Click(object sender, EventArgs e)
{
/* string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_fnamem=@au_fname,au_lname=@au_lname,";
updateSQL += "phone=@phone,address=@address,city=@city,state=@state,";
// updateSQL += "contrac=@contract";
updateSQL += "WHERE au_id=@au_id_original";
//au_id=@au_id_original,
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
//添加参数
// cmd.Parameters.AddWithValue("@au_id", TextBox1.Text);
cmd.Parameters.AddWithValue("@au_fname", TextBox2.Text);
cmd.Parameters.AddWithValue("@au_lname", TextBox3.Text);
cmd.Parameters.AddWithValue("@phone", TextBox4.Text);
cmd.Parameters.AddWithValue("@address", TextBox5.Text);
cmd.Parameters.AddWithValue("@city", TextBox6.Text);
cmd.Parameters.AddWithValue("@state", TextBox7.Text);
// cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(CheckBox1.Checked));
cmd.Parameters.AddWithValue("@au_id_original",
lstAuthor.SelectedItem.Value);
int updated = 0;
try
{
con.Open();
Label10.Text = "数据库连接，打开正常～";
updated = cmd.ExecuteNonQuery();
lb1Status.Text = updated.ToString() + " record updated.";
}
catch (Exception err)
{
lb1Status.Text = "Error updating author.";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
Label11.Text = "数据库已经关闭～";
}
if (updated > 0)
{
FillAuthorList();
} */
string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_fname=@au_fname, au_lname=@au_lname, ";
updateSQL += "phone=@phone, address=@address, city=@city, state=@state, ";
updateSQL += " contract=@contract ";
updateSQL += "WHERE au_id=@au_id_original";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
cmd.Parameters.AddWithValue("@au_fname", TextBox2.Text);
cmd.Parameters.AddWithValue("@au_Lname", TextBox3.Text);
cmd.Parameters.AddWithValue("@phone", TextBox4.Text);
cmd.Parameters.AddWithValue("@address", TextBox5.Text);
cmd.Parameters.AddWithValue("@city", TextBox6.Text);
cmd.Parameters.AddWithValue("@state", TextBox7.Text);
//cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", CheckBox1.Checked);
cmd.Parameters.AddWithValue("@au_id_original", lstAuthor.SelectedItem.Value);
int updated = 0;
try
{
con.Open();
updated = cmd.ExecuteNonQuery();
lb1Status.Text = updated.ToString() + " record updated.";
}
catch (Exception err)
{
lb1Status.Text = "Error updating author. ";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
}
if (updated > 0)
{
FillAuthorList();
}
}
//删除表中数据
protected void Delete_Click(object sender, EventArgs e)
{
/* string deleteSQL;
deleteSQL = "DELETE FROM Authors";
deleteSQL += "WHERE au_id=@au_id";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(deleteSQL, con);
cmd.Parameters.AddWithValue("@au_id", lstAuthor.SelectedItem.Value);
int deleted = 0;
try
{
con.Open();
Label10.Text = "数据库连接，打开正常～";
deleted = cmd.ExecuteNonQuery();
lb1Status.Text = "Record deleted.";
}
catch (Exception err)
{
lb1Status.Text = "Error deleting author.";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
Label11.Text = "数据库已经关闭～";
}
if (deleted > 0)
{
FillAuthorList();
} */
string deleteSQL;
deleteSQL = "DELETE FROM Authors ";
deleteSQL += "WHERE au_id=@au_id";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(deleteSQL, con);
cmd.Parameters.AddWithValue("@au_id ", lstAuthor.SelectedItem.Value);
int deleted = 0;
try
{
con.Open();
deleted = cmd.ExecuteNonQuery();
lb1Status.Text = "Record deleted.";
}
catch (Exception err)
{
lb1Status.Text = "Error deleting author. ";
lb1Status.Text += err.Message;
}
finally
{
con.Close();
}
if (deleted > 0)
{
FillAuthorList();
}
}
}
浙公网安备 33021202002483