你的位置:首页 > ASP.net教程

[ASP.net教程]asp.net 对数据库表增加,删除,编辑更新修改


 1 using System; 2 using System.Collections.Generic; 3 using System.Configuration; 4 using System.Data; 5 using System.Data.SqlClient; 6 using System.Linq; 7 using System.Web; 8 using System.Web.UI; 9 using System.Web.UI.WebControls; 10  11 public partial class _Default : System.Web.UI.Page 12 { 13   private string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; 14  15   SqlHelper helper = new SqlHelper(); 16   protected void Page_Load(object sender, EventArgs e) 17   { 18     if (!Page.IsPostBack) 19     { 20       using (SqlConnection conn = new SqlConnection(constr)) 21       { 22    23         SqlDataAdapter ad = new SqlDataAdapter("select * from tbuser", conn); 24         SqlCommandBuilder builder = new SqlCommandBuilder(ad); 25         DataTable dt = new DataTable(); 26          ad.Fill(dt); 27         this.GridView1.DataSource = dt; 28         this.DataBind(); 29       } 30     } 31   } 32  33   private void userlistbind() 34   { 35     GridView1.DataSource = helper.SelectSqlReturnDataset("select id, username as '用户名',birthday as '生日', '部门号'=(select departmentname from tbdepartment where tbdepartment.departmentid=tbUser.departmentid) from tbUser").Tables[0]; 36   } 37   protected void btnAdd_Click(object sender, EventArgs e) 38   { 39  40     string sql = "insert into tbUser(username,birthday,departmentid) values('" + txtUserName.Text + "','" + txtBirthday.Text + "'," + ddlDepartment.SelectedValue.ToString() + ")"; 41     int count = helper.ExecuteReturnInt(sql, null, System.Data.CommandType.Text); 42     if (count != 0) 43     { 44       userlistbind(); 45       Response.Write("<script>alert('数据添加成功!')</script>"); 46       txtBirthday.Text = ""; 47       txtUserName.Text = ""; 48        49       GridView1.DataBind(); 50     } 51   } 52   protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) 53   { 54     55     56     57   } 58   protected void bind() 59   { 60     SqlConnection myconn = new SqlConnection(constr); 61     myconn.Open(); 62     string sql = "select id,username 用户名,birthday 生日,departmentid 部门号 from tbUser"; 63     SqlDataAdapter myda = new SqlDataAdapter(sql, myconn); 64     DataSet myds = new DataSet(); 65     myda.Fill(myds); 66     GridView1.DataSource = myds; 67     GridView1.DataKeyNames = new string[] { "id" }; 68     GridView1.DataBind(); 69     myda.Dispose(); 70     myds.Dispose(); 71     myconn.Close(); 72   } 73  74   75  76  77   protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) 78   { 79     GridView1.EditIndex = e.NewEditIndex; 80     this.bind(); 81   } 82   protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) 83   { 84     85     int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString()); 86     string name = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString(); 87     string birthday = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[4].Controls[0])).Text.ToString(); 88     string department = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[5].Controls[0])).Text.ToString(); 89     string sql = "update tbUser set username='" + name + "',birthday='"+birthday+"',departmentid="+department+" where id=" + id + ""; 90     SqlConnection myconn = new SqlConnection(constr); 91     myconn.Open(); 92     SqlCommand mycmd = new SqlCommand(sql, myconn); 93     mycmd.ExecuteNonQuery(); 94     mycmd.Dispose(); 95     myconn.Close(); 96     GridView1.EditIndex = -1; 97     this.bind(); 98   } 99   protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)100   {101     GridView1.EditIndex = -1;102     this.bind();103   }104   protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)105   {106     int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());107     string sql = "delete from tbUser where id=" + id + "";108     SqlConnection myconn = new SqlConnection(constr);109     myconn.Open();110     SqlCommand mycmd = new SqlCommand(sql, myconn);111     mycmd.ExecuteNonQuery();112     mycmd.Dispose();113     myconn.Close();114     GridView1.EditIndex = -1;115     this.bind();116   }117   protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)118   {119 120     if (e.Row.RowType == DataControlRowType.DataRow)121     {122       ((LinkButton)e.Row.Cells[0].Controls[0]).Attributes.Add("onclick", "return confirm('确定要删除吗')");123     }124   }125 }

web.config

<configuration>
<connectionStrings>
<add name="constr" connectionString="server=.\sqlexpress;database=db2016;uid=sa;pwd=123;" />
<add name="db2016ConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=db2016;Persist Security Info=True;User ID=sa;Password=123"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>

</configuration>

 

 

GridView的datakeyname属性 设为id

 

 

数据库表

create table tbUser

(
id int primary key identity(1,1),
username nvarchar(20),
userpass varbinary(128),
birthday datetime,
departmentid int foreign key references tbdepartment(departmentid)
)