存储过程的创建及应用（实例讲解）

一、添加存储过程如下： 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 } 24