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