EF执行存储过程

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;

View Code

 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 }

View Code

二、执行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

View Code

 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 }

View Code

三、执行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

View Code

 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 }

View Code

四、执行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

View Code

 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 }

View Code

五、执行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

View Code

 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 }

View Code