EF6学习笔记十一：调用、自动生成存储过程

要专业系统地学习EF前往《你必须掌握的Entity Framework 6.x与Core 2.0》这本书的作者（汪鹏，Jeffcky）的博客：https://www.cnblogs.com/CreateMyself/

前面说到EF中的原始查询，就是写SQL语句执行

那么还有存储过程的调用也是通过那几个方法来的

调用查询数据的存储过程使用：ctx.Database.SqlQuery<T>() 或者 ctx.DbSet<T>.SqlQuery()

调用Insert、Update、Delete操作的存储过程使用：ExceuteSqlCommand()或者ExceuteSqlCommandAsync()

调用存储过程

我们先手动地添加一个存储过程：select * from tb_products

1create procedure GetProducts
2as
3begin
4set nocount on;
5select * from tb_Products;
6set nocount off;
7end
8go

View Code

紧接着调用它，可以

1var res = ctx.Database.SqlQuery<Product>("dbo.GetProducts");
2Console.WriteLine(JsonConvert.SerializeObject(res,set));

View Code

然后再创建一个存储过程：select id,name from tb_products

1create procedure GetProducts2
2as
3begin
4select id,[name] from tb_Products
5end
6go

View Code

接收类型的属性数量和返回数据集的字段数量不一致，报错。得自己另外定义类型去接收

1var res = ctx.Database.SqlQuery<Product>("dbo.getproducts2");
2Console.WriteLine(JsonConvert.SerializeObject(res));
3//  未经处理的异常:  System.Data.Entity.Core.EntityCommandExecutionException: The data reader is incompatible with the specified 'CodeFirstNamespace.Product'. A member of the type, 'Price', does not have a corresponding column in the data reader with the same name.

View Code

来看看带参数的存储过程，在方法中该怎么写

1-- 传递参数，name
2create procedure GetProductsByName
3(
4@name as nvarchar(50)
5)
6as
7begin
8select *from tb_Products where [Name] =@name;
9end
10go
11
12--  执行
13exec GetProductsByName '砖头'
14go

View Code

1// 一个参数name
2var parameter = new SqlParameter("@name","水泥");
3var res = ctx.Database.SqlQuery<Product>("dbo.getproductsByName @name",parameter);
4Console.WriteLine(JsonConvert.SerializeObject(res));
5//  [{"Order":null,"Name":"水泥","Price":50.00,"Unit":"袋","FK_Order_Id":"469b82be-8139-4e67-b566-5b2b5f6d838d","Id":"d951e96d-a581-4f87-a567-bedb4c24eca3","AddTime":"2019-01-15T10:28:00.653"}]

View Code

来看看多个参数的存储过程

1--  两个参数，id,price
2create procedure GetProducts3
3(
4@id as nvarchar(36), 
5@price as decimal(18,2)
6)
7as
8begin 
9select * from tb_Products where id =@id and Price = @price
10end
11go

View Code

1//  两个参数
2var parameterList = new List<SqlParameter>
3     {
4            new SqlParameter(){ ParameterName="@id",SqlDbType = System.Data.SqlDbType.NVarChar,Value ="6495f22b-f1ef-4bd2-b81e-c49eaf6e2f21"},
5            new SqlParameter(){ ParameterName="@price",SqlDbType=System.Data.SqlDbType.Decimal,Value=5}
6       };
7var parameterArr = parameterList.ToArray();
8var res = ctx.Database.SqlQuery<Product>("dbo.getproducts3 @id,@price",parameterArr);
9Console.WriteLine(JsonConvert.SerializeObject(res));
10//[{"Order":null,"Name":"苹果","Price":5.00,"Unit":"斤","FK_Order_Id":"e18757db-1db8-4f7f-b702-79138709b304","Id":"6495f22b-f1ef-4bd2-b81e-c49eaf6e2f21","AddTime":"2019-01-15T10:35:03.36"}]
11// 简直没有问题

View Code

上面的都是执行的查询操作，来看看添加操作的存储过程，我们使用ExcuteSqlCommand()方法

1--  添加数据
2create procedure AddProduct
3(
4@name as nvarchar(50),
5@price as decimal(18,2),
6@unit as nvarchar(10)
7)
8as
9begin
10insert into tb_Products values(newid(),@name,@price,'469b82be-8139-4e67-b566-5b2b5f6d838d',getdate(),@unit)
11end
12go

View Code

1var parameterList = new List<SqlParameter>
2{
3    new SqlParameter(){ ParameterName="@name",SqlDbType = System.Data.SqlDbType.NVarChar,Value ="花生"},
4    new SqlParameter(){ ParameterName="@price",SqlDbType = System.Data.SqlDbType.Decimal,Value = 4.4},
5    new SqlParameter(){ ParameterName ="@unit",SqlDbType = System.Data.SqlDbType.NVarChar,Value="斤"}
6};
7var paraArr = parameterList.ToArray();
8var res = ctx.Database.ExecuteSqlCommand("dbo.addproduct @name,@price,@unit", paraArr);
9Console.WriteLine(res);  //  result : 1

View Code

EF自动生成存储过程

上面的存储过程都是手动添加，现在我们在OnModelCreating方法中写配置，让它自动添加存储过程

modelBuilder.Entity<Order>().MapToStoredProcedures();

View Code

这样他会给你生成三个存储过程

1public partial class jinshantest4 : DbMigration
2    {
3        public override void Up()
4        {
5            CreateStoredProcedure(
6                "dbo.Order_Insert",
7                p => new
8                    {
9                        Id = p.String(maxLength: 128),
10                        OrderNO = p.String(),
11                        Description = p.String(),
12                        AddTime = p.DateTime(),
13                    },
14                body:
15                    @"INSERT [dbo].[tb_Orders]([Id], [OrderNO], [Description], [AddTime])
16                      VALUES (@Id, @OrderNO, @Description, @AddTime)"
17            );
18            
19            CreateStoredProcedure(
20                "dbo.Order_Update",
21                p => new
22                    {
23                        Id = p.String(maxLength: 128),
24                        OrderNO = p.String(),
25                        Description = p.String(),
26                        AddTime = p.DateTime(),
27                    },
28                body:
29                    @"UPDATE [dbo].[tb_Orders]
30                      SET [OrderNO] = @OrderNO, [Description] = @Description, [AddTime] = @AddTime
31                      WHERE ([Id] = @Id)"
32            );
33            
34            CreateStoredProcedure(
35                "dbo.Order_Delete",
36                p => new
37                    {
38                        Id = p.String(maxLength: 128),
39                    },
40                body:
41                    @"DELETE [dbo].[tb_Orders]
42                      WHERE ([Id] = @Id)"
43            );
44            
45        }
46        
47        public override void Down()
48        {
49            DropStoredProcedure("dbo.Order_Delete");
50            DropStoredProcedure("dbo.Order_Update");
51            DropStoredProcedure("dbo.addOrder");
52        }
53    }

View Code

也可以对它进行详细配置，比如我指定insert存储过程的名称为“orderAdd”

modelBuilder.Entity<Order>().MapToStoredProcedures(x => x.Insert(a => a.HasName("addOrder")));

View Code

跟新后，可以看到存储过程都添加成功了

最后来调用一个EF为我们创建的存储过程

1//  添加一个订单
2var parameterList = new List<SqlParameter>
3{
4    new SqlParameter{ ParameterName="@Id",SqlDbType = System.Data.SqlDbType.NVarChar,Value = Guid.NewGuid().ToString()},
5    new SqlParameter{ ParameterName="@OrderNO",SqlDbType = System.Data.SqlDbType.NVarChar,Value ="order435435"},
6    new SqlParameter{ ParameterName="@Description",SqlDbType = System.Data.SqlDbType.NVarChar,Value="description4364537ryrtey"},
7    new SqlParameter{ ParameterName="@AddTime",SqlDbType = System.Data.SqlDbType.DateTime,Value =DateTime.Now}
8};
9var paraArr = parameterList.ToArray();
10var res = ctx.Database.ExecuteSqlCommand("dbo.addOrder @Id,@OrderNO,@Description,@AddTime",paraArr);
11Console.WriteLine(res);  //  result:1

View Code

可以的

EF6学习笔记十一：调用、自动生成存储过程

调用存储过程

EF自动生成存储过程

评论区

相关推荐