一、添加存储过程如下:
1 create procedure Addemp
2 @emp_login varchar(50),
3 @password varchar(50),
4 @emp_name varchar(50),
5 @duty varchar(50),
6 @email varchar(50),
7 @mobile_phone varchar(50),
8 @work_phone varchar(50),
9 @jb int
10 as
11 insert into emp
12 (
13 emp_login,
14 password,
15 emp_name,
16 duty,
17 email,
18 mobile_phone,
19 work_phone,
20 jb
21 )
22
23 values
24 (
25 @emp_login,
26 @password,
27 @emp_name,
28
29 @duty,
30 @email,
31 @mobile_phone,
32 @work_phone,
33 @jb
34 )
35 go
36
37 添加代码生成
38 private void Btn_ok_Click(object sender, System.EventArgs e)
39 {
40 if(Page.IsValid)
41 {
42 SqlCommand cm=new SqlCommand("AddEmp",cn);//调用存储过程
43
44 cm.CommandType=CommandType.StoredProcedure;//类型转换
45 参数的调用
46 cm.Parameters.Add(new SqlParameter("@Emp_login",SqlDbType.VarChar,50));
47 cm.Parameters.Add(new SqlParameter("@password",SqlDbType.VarChar,50));
48 cm.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,50));
49 cm.Parameters.Add(new SqlParameter("@duty",SqlDbType.VarChar,50));
50 cm.Parameters.Add(new SqlParameter("@Email",SqlDbType.VarChar,50));
51 cm.Parameters.Add(new SqlParameter("@mobile_tell",SqlDbType.VarChar,50));
52 cm.Parameters.Add(new SqlParameter("@work_tell",SqlDbType.VarChar,50));
53 cm.Parameters.Add(new SqlParameter("@jb",SqlDbType.Int,4));
54 参数赋值
55 cm.Parameters["@Emp_login"].Value=Tbx_id.Text;
56 cm.Parameters["@password"].Value=Tbx_id.Text;
57 cm.Parameters["@name"].Value=Tbx_name.Text;
58 cm.Parameters["@duty"].Value=duty.SelectedItem.Value;
59 cm.Parameters["@Email"].Value=Tbx_Email.Text;
60 cm.Parameters["@mobile_tell"].Value=mobile_tell.Text;
61 cm.Parameters["@work_tell"].Value=work_tell.Text;
62 cm.Parameters["@jb"].Value=jb.SelectedItem.Value;
63 数据更新命令的执行
64 cm.Connection.Open();
65 try
66 {
67 cm.ExecuteNonQuery();//不返回值
68 Response.Redirect("Emp.aspx");
69
70 }
71 catch(SqlException)
72 {
73 Lbl_note.Text="添加失败";
74 Lbl_note.Style["color"]="red";
75 }
76 cm.Connection.Close();
77 }
78 }
79
80 private void Btn_cancel_Click(object sender, System.EventArgs e)
81 {
82 Page.Response.Redirect("addemp.aspx");
83 }
84 private void Cv_id_ServerValidate(object source, System.Web.UI.WebControls.ServerValidateEventArgs args)
85 {
86 cn.Open();
87 SqlCommand cm=new SqlCommand("select * from Emp where emp_login=@emp_login",cn);
88 cm.Parameters.Add("@emp_login",SqlDbType.Char,10);
89 cm.Parameters["@emp_login"].Value=Tbx_id.Text;
90 SqlDataReader dr=cm.ExecuteReader();
91 if(dr.Read())
92 {
93 args.IsValid=false;
94 }
95 else
96 {
97 args.IsValid=true;
98 }
99 cn.Close();
100 }
二、数据更新
1、存储过程如下:
1 create procedure editemp
2 @duty varchar(50),
3 @email varchar(50),
4 @mobile_phone varchar(50),
5 @work_phone varchar(50),
6 @emp_id int
7 as
8 update emp
9
10 set
11 duty=@duty,
12 email=@email,
13 mobile_phone=@mobile_phone,
14 work_phone=@work_phone
15 where
16 emp_id=@emp_id
17 go
2、执行代码
1 private void Dgd_user_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
2 {
3 SqlCommand cm=new SqlCommand("EditEmp",cn);//调用存储过程
4 cm.CommandType=CommandType.StoredProcedure;//类型转换
5 cm.Parameters.Add(new SqlParameter("@duty",SqlDbType.VarChar,50));
6 cm.Parameters.Add(new SqlParameter("@Email",SqlDbType.VarChar,50));
7 cm.Parameters.Add(new SqlParameter("@mobile_tell",SqlDbType.VarChar,50));
8 cm.Parameters.Add(new SqlParameter("@work_tell",SqlDbType.VarChar,50));
9 cm.Parameters.Add(new SqlParameter("@Emp_id",SqlDbType.Int,4));
10
11 string colvalue=((TextBox)e.Item.Cells[4].Controls[0]).Text;
12 cm.Parameters["@duty"].Value=colvalue;
13
14 colvalue=((TextBox)e.Item.Cells[5].Controls[0]).Text;
15 cm.Parameters["@Email"].Value=colvalue;
16
17 colvalue=((TextBox)e.Item.Cells[6].Controls[0]).Text;
18 cm.Parameters["@mobile_tell"].Value=colvalue;
19
20 colvalue=((TextBox)e.Item.Cells[7].Controls[0]).Text;
21 cm.Parameters["@work_tell"].Value=colvalue;
22
23 cm.Parameters["@Emp_id"].Value=Dgd_user.DataKeys[(int)e.Item.ItemIndex];
24 cm.Connection.Open();
25 try
26 {
27 cm.ExecuteNonQuery();
28 Lbl_note.Text="编辑成功";
29 Dgd_user.EditItemIndex=-1;
30 }
31 catch(SqlException)
32 {
33 Lbl_note.Text="编辑失败";
34 Lbl_note.Style["color"]="red";
35 }
36 cm.Connection.Close();
37 BindGrid();
38 }
三、数据删除
1、存储过程
1 create procedure deleteemp
2 @emp_id int
3 as
4 delete
5 from
6 emp
7 where
8 emp_id=@emp_id
9 go
2、执行代码
1 private void Dgd_user_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
2 {
3 SqlCommand cm=new SqlCommand("deleteEmp",cn);
4 cm.CommandType=CommandType.StoredProcedure;
5
6 cm.Parameters.Add(new SqlParameter("@Emp_id",SqlDbType.Int,4));
7 cm.Parameters["@Emp_id"].Value=Dgd_user.DataKeys[(int)e.Item.ItemIndex];
8
9 cm.Connection.Open();
10 try
11 {
12 cm.ExecuteNonQuery();
13 Lbl_note.Text="删除成功";
14
15 }
16 catch(SqlException)
17 {
18 Lbl_note.Text="删除失败";
19 Lbl_note.Style["color"]="red";
20 }
21 cm.Connection.Close();
22 BindGrid();
23 }