SQLServer的增删改查

实例一:查询 using System; using System.Collections; using System.Configuration; using System.Data; 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.Xml.Linq; using System.Data.SqlClient; namespace ADONET { public partial class ReadData : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"server=serverName;database=student;uid=sa;pwd=users"; string sql = "select * from student"; conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader dr = cmd.ExecuteReader(); gvData.DataSource = dr; gvData.DataBind(); } } } ******************************************************************************************************************************************************************************** 实例二:添加 using System; using System.Collections; using System.Configuration; using System.Data; 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.Xml.Linq; using System.Data.SqlClient; namespace ADONET { public partial class Add : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if(!Page.IsPostBack) { string[] sexArray = new string[2]; sexArray[0] = "男"; sexArray[1] = "女"; rdbtnSex.DataSource = sexArray; rdbtnSex.DataBind(); rdbtnSex.Items[0].Selected = true; string[] cityArray = new string[5]; cityArray[0] = "北京"; cityArray[1] = "上海"; cityArray[2] = "广州"; cityArray[3] = "天津"; cityArray[4] = "深圳"; ddl.DataSource = cityArray; ddl.DataBind(); ddl.Items[0].Selected = true; } } protected void btnOk_Click(object sender, EventArgs e) { string name = txtName.Text.ToString(); string sex = rdbtnSex.SelectedValue; string city = ddl.SelectedValue; SqlConnection conn =new SqlConnection(); conn.ConnectionString=@"server=serverName;database=student;uid=sa;pwd=users"; string sql = "Insert into student(name,sex,city) values('"+name+"','"+sex+"','"+city+"')"; conn.Open(); SqlCommand cmd = new SqlCommand(sql,conn); if (cmd.ExecuteNonQuery() == -1) { Response.Write("添加失败"); } else { Response.Write("添加成功"); } conn.Close(); conn.Dispose(); } } } ******************************************************************************************************************************************************************************** 实例三:删除 using System; using System.Collections; using System.Configuration; using System.Data; 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.Xml.Linq; using System.Data.SqlClient; namespace ADONET { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if(!Page.IsPostBack) { BindToddlID(); } } public void BindToddlID() { SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"server=serverName;database=student;uid=sa;pwd=users"; conn.Open(); string sql = "select id from student order by id asc"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader dr = cmd.ExecuteReader(); ddlID.DataSource = dr; ddlID.DataTextField = "id"; ddlID.DataValueField = "id"; ddlID.DataBind(); ddlID.Items[0].Selected = true; conn.Close(); conn.Dispose(); } protected void btnDelete_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"server=serverName;database=student;uid=sa;pwd=users"; conn.Open(); string id=ddlID.SelectedValue; string sql = "delete from student where id="+id; SqlCommand cmd = new SqlCommand(sql, conn); if (cmd.ExecuteNonQuery() == -1) { Response.Write("删除失败"); } else { Response.Write("删除成功"); } conn.Close(); conn.Dispose(); BindToddlID(); } protected void btnUpdate_Click(object sender, EventArgs e) { string id = ddlID.SelectedValue; Response.Redirect("Update.aspx?id="+id); } } } ******************************************************************************************************************************************************************************** 实例四:修改update using System; using System.Collections; using System.Configuration; using System.Data; 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.Xml.Linq; using System.Data.SqlClient; namespace ADONET { public partial class Update : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string ID = string.Empty; if (Request.QueryString["id"] == null) { Response.Redirect("Delete.aspx"); } else { ID = Request.QueryString["id"].ToString(); } SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"server=serverName;database=student;uid=sa;pwd=users"; conn.Open(); string sql = "select name,sex,city from student where id="+ID; string name = string .Empty; string sex =string.Empty; string city =string.Empty; SqlCommand cmd = new SqlCommand(sql,conn); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { name = dr["name"].ToString(); sex = dr["sex"].ToString(); city = dr["city"].ToString(); } else { Response.Write("ID无效"); Response.End(); } if(!Page.IsPostBack) { txtName.Text = name; string[] sexArray = new string[2]; sexArray[0] = "男"; sexArray[1] = "女"; hfID.Value = ID; rdbtnSex.DataSource = sexArray; rdbtnSex.DataBind(); foreach(ListItem item in rdbtnSex.Items) { if(item.Value==sex) { item.Selected = true; } } string[] cityArray = new string[5]; cityArray[0] = "北京"; cityArray[1] = "上海"; cityArray[2] = "天津"; cityArray[3] = "深圳"; cityArray[4] = "广州"; ddl.DataSource = cityArray; ddl.DataBind(); for (int index = 0; index < ddl.Items.Count;index++ ) { if(ddl.Items[index].Value==city) { ddl.Items[index].Selected = true; } } } } protected void btnOK_Click(object sender, EventArgs e) { string ID=hfID.Value.ToString(); string name = txtName.Text.ToString(); string sex = rdbtnSex.SelectedValue.ToString(); string city = ddl.SelectedValue.ToString(); SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"Server=BEIHAI-E6A42B31;database=student;uid=sa;pwd=users"; conn.Open(); string sql = "Update student set name='"+name+"',sex='"+sex+"',city='"+city+"'where id="+ID; SqlCommand cmd=new SqlCommand(sql,conn); if (cmd.ExecuteNonQuery() == -1) { Response.Write("更新失败"); } else { Response.Write("更新成功"); } conn.Close(); conn.Dispose(); } } }

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28311102/viewspace-747907/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28311102/viewspace-747907/