摘要：
1//EF执行存储过程与执行Sql语句非常相似。2//插入、删除和更新操作通过ExecuteSqlCommand（）执行，3//选择操作通过SqlQuery（）执行1。执行插入存储过程（无返回值）1CREATEPROCEDURE[dbo][proc_AddSysUser01]@NameNVARCHAR（50），@PhoneNVARCHA
1 //EF执行存储过程与执行Sql语句非常类似， 2 //insert、delete、update操作通过ExecuteSqlCommand()执行， 3 //select操作通过SqlQuery<Sys_User>()执行
一、执行insert存储过程（无返回值）
1 CREATE PROCEDURE [dbo].[proc_AddSysUser01] @Name NVARCHAR(50), @Phone NVARCHAR(50) 2 AS BEGIN 3 --SET NOCOUNT ON; 4 5 -- Insert statements for procedure here 6 INSERT INTO Sys_User VALUES(@Name, @Phone, '耶路撒冷', GETDATE()); 7 END;
1 public ActionResult ExecuteInsertProc(string name, string phone) 2 { 3 using(NHibernateContext context = new NHibernateContext()) 4 { 5 SqlParameter pp_name = new SqlParameter("@Name", name); 6 SqlParameter pp_phone = new SqlParameter("@Phone", phone); 7 int count = context.Database.ExecuteSqlCommand("exec [proc_AddSysUser01] @Name,@Phone", pp_name, pp_phone); 8 context.SaveChanges(); 9 } 10 return View("Index"); 11 }
二、执行insert存储过程（out参数返回主键）
1 CREATE PROCEDURE [dbo].[proc_AddSysUser02] 2 @Name nvarchar(50), 3 @Phone nvarchar(50), 4 @Id int output 5 AS 6 BEGIN 7 --SET NOCOUNT ON; 8 9 -- Insert statements for procedure here 10 insert into Sys_User 11 values 12 (@Name, @Phone, '安曼酒店', GETDATE()); 13 select @Id=SCOPE_IDENTITY(); 14 END
1 public ActionResult ExecuteInsertProc (string name, string phone) { 2 using (NHibernateContext context = new NHibernateContext ()) { 3 SqlParameter pp_id = new SqlParameter ("@Id", SqlDbType.Int); 4 pp_id.Direction = ParameterDirection.Output; 5 SqlParameter pp_name = new SqlParameter ("@Name", name); 6 SqlParameter pp_phone = new SqlParameter ("@Phone", phone); 7 //count值为1，out参数需要放在最后 8 int count = context.Database.ExecuteSqlCommand ("exec [proc_AddSysUser02] @Name,@Phone,@Id out", pp_id, pp_name, pp_phone); 9 //id值为10010 10 int id = int.Parse (pp_id.Value.ToString ()); 11 context.SaveChanges (); 12 } 13 return View ("Index"); 14 }
三、执行delete存储过程
1 CREATE PROCEDURE [dbo].[proc_DeleteSysUser] 2 @Id int, 3 @Name nvarchar(50) 4 AS 5 BEGIN 6 --SET NOCOUNT ON; 7 8 -- Insert statements for procedure here 9 delete from Sys_User where Id>@Id and Name like '%'+@Name+'%' 10 END
1 public ActionResult ExecuteDeleteProc (int id, string name) { 2 using (NHibernateContext context = new NHibernateContext ()) { 3 SqlParameter pp_id = new SqlParameter ("@Id", id); 4 SqlParameter pp_name = new SqlParameter ("@Name", name); 5 //count值为2 6 int count = context.Database.ExecuteSqlCommand ("exec [proc_DeleteSysUser] @Id,@Name", pp_id, pp_name); 7 context.SaveChanges (); 8 } 9 return View ("Index"); 10 }
四、执行update存储过程
1 CREATE PROCEDURE [dbo].[proc_UpdateSysUser] 2 @Id int, 3 @Name nvarchar(50), 4 @Phone nvarchar(50) 5 AS 6 BEGIN 7 --SET NOCOUNT ON; 8 9 -- Insert statements for procedure here 10 update Sys_User set Phone=@Phone where Id>@Id and Name like '%'+@Name+'%' 11 END
1 public ActionResult ExecuteUpdateProc (int id, string name, string phone) { 2 using (NHibernateContext context = new NHibernateContext ()) { 3 SqlParameter pp_id = new SqlParameter ("@Id", id); 4 SqlParameter pp_name = new SqlParameter ("@Name", name); 5 SqlParameter pp_phone = new SqlParameter ("@Phone", phone); 6 //count值为2 7 int count = context.Database.ExecuteSqlCommand ("exec [proc_UpdateSysUser] @Id,@Name,@Phone", pp_id, pp_name, pp_phone); 8 context.SaveChanges (); 9 } 10 return View ("Index"); 11 }
五、执行select存储过程
1 CREATE PROCEDURE [dbo].[proc_GetSysUser] 2 @Id int, 3 @Name nvarchar(50) 4 AS 5 BEGIN 6 SET NOCOUNT ON; 7 8 -- Insert statements for procedure here 9 select * 10 from Sys_User 11 where Id<@Id and Name like '%'+@Name+'%' 12 END
1 public ActionResult ExecuteSelectProc (int id, string name) { 2 using (NHibernateContext context = new NHibernateContext ()) { 3 SqlParameter pp_id = new SqlParameter ("@Id", id); 4 SqlParameter pp_name = new SqlParameter ("@Name", name); 5 //userList.Count值为96 6 List<Sys_User> userList = context.Database.SqlQuery<Sys_User> ("exec [proc_GetSysUser] @Id,@Name", pp_id, pp_name).Cast<Sys_User> ().ToList (); 7 context.SaveChanges (); 8 } 9 return View ("Index"); 10 }