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