要专业系统地学习 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
- create procedure GetProducts
- as
- begin
- set nocount on;
- select * from tb_Products;
- set nocount off;
- end
- go
- View Code
紧接着调用它, 可以
- var res = ctx.Database.SqlQuery<Product>("dbo.GetProducts");
- Console.WriteLine(JsonConvert.SerializeObject(res,set));
- View Code
然后再创建一个存储过程: select id,name from tb_products
- create procedure GetProducts2
- as
- begin
- select id,[name] from tb_Products
- end
- go
- View Code
接收类型的属性数量和返回数据集的字段数量不一致, 报错. 得自己另外定义类型去接收
- var res = ctx.Database.SqlQuery<Product>("dbo.getproducts2");
- Console.WriteLine(JsonConvert.SerializeObject(res));
- // 未经处理的异常: 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
来看看带参数的存储过程, 在方法中该怎么写
-- 传递参数, name
- create procedure GetProductsByName
- (
- @name as nvarchar(50)
- )
- as
- begin
- select *from tb_Products where [Name] =@name;
- end
- go
-- 执行
- exec GetProductsByName '砖头'
- go
- View Code
- // 一个参数 name
- var parameter = new SqlParameter("@name","水泥");
- var res = ctx.Database.SqlQuery<Product>("dbo.getproductsByName @name",parameter);
- Console.WriteLine(JsonConvert.SerializeObject(res));
- // [{
- "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
来看看多个参数的存储过程
-- 两个参数, id,price
- create procedure GetProducts3
- (
- @id as nvarchar(36),
- @price as decimal(18,2)
- )
- as
- begin
- select * from tb_Products where id =@id and Price = @price
- end
- go
- View Code
- // 两个参数
- var parameterList = new List<SqlParameter>
- {
- new SqlParameter(){ ParameterName="@id",SqlDbType = System.Data.SqlDbType.NVarChar,Value ="6495f22b-f1ef-4bd2-b81e-c49eaf6e2f21"},
- new SqlParameter(){ ParameterName="@price",SqlDbType=System.Data.SqlDbType.Decimal,Value=5}
- };
- var parameterArr = parameterList.ToArray();
- var res = ctx.Database.SqlQuery<Product>("dbo.getproducts3 @id,@price",parameterArr);
- Console.WriteLine(JsonConvert.SerializeObject(res));
- //[{"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"}]
- // 简直没有问题
- View Code
上面的都是执行的查询操作, 来看看添加操作的存储过程, 我们使用 ExcuteSqlCommand()方法
-- 添加数据
- create procedure AddProduct
- (
- @name as nvarchar(50),
- @price as decimal(18,2),
- @unit as nvarchar(10)
- )
- as
- begin
- insert into tb_Products values(newid(),@name,@price,'469b82be-8139-4e67-b566-5b2b5f6d838d',getdate(),@unit)
- end
- go
- View Code
- var parameterList = new List<SqlParameter>
- {
- new SqlParameter(){ ParameterName="@name",SqlDbType = System.Data.SqlDbType.NVarChar,Value ="花生"},
- new SqlParameter(){ ParameterName="@price",SqlDbType = System.Data.SqlDbType.Decimal,Value = 4.4},
- new SqlParameter(){ ParameterName ="@unit",SqlDbType = System.Data.SqlDbType.NVarChar,Value="斤"}
- };
- var paraArr = parameterList.ToArray();
- var res = ctx.Database.ExecuteSqlCommand("dbo.addproduct @name,@price,@unit", paraArr);
- Console.WriteLine(res); // result : 1
- View Code
EF 自动生成存储过程
上面的存储过程都是手动添加, 现在我们在 OnModelCreating 方法中写配置, 让它自动添加存储过程
- modelBuilder.Entity<Order>().MapToStoredProcedures();
- View Code
这样他会给你生成三个存储过程
- public partial class jinshantest4 : DbMigration
- {
- public override void Up()
- {
- CreateStoredProcedure(
- "dbo.Order_Insert",
- p => new
- {
- Id = p.String(maxLength: 128),
- OrderNO = p.String(),
- Description = p.String(),
- AddTime = p.DateTime(),
- },
- body:
- @"INSERT [dbo].[tb_Orders]([Id], [OrderNO], [Description], [AddTime])
- VALUES (@Id, @OrderNO, @Description, @AddTime)"
- );
- CreateStoredProcedure(
- "dbo.Order_Update",
- p => new
- {
- Id = p.String(maxLength: 128),
- OrderNO = p.String(),
- Description = p.String(),
- AddTime = p.DateTime(),
- },
- body:
- @"UPDATE [dbo].[tb_Orders]
- SET [OrderNO] = @OrderNO, [Description] = @Description, [AddTime] = @AddTime
- WHERE ([Id] = @Id)"
- );
- CreateStoredProcedure(
- "dbo.Order_Delete",
- p => new
- {
- Id = p.String(maxLength: 128),
- },
- body:
- @"DELETE [dbo].[tb_Orders]
- WHERE ([Id] = @Id)"
- );
- }
- public override void Down()
- {
- DropStoredProcedure("dbo.Order_Delete");
- DropStoredProcedure("dbo.Order_Update");
- DropStoredProcedure("dbo.addOrder");
- }
- }
- View Code
也可以对它进行详细配置, 比如我指定 insert 存储过程的名称为 "orderAdd"
- modelBuilder.Entity<Order>().MapToStoredProcedures(x => x.Insert(a => a.HasName("addOrder")));
- View Code
跟新后, 可以看到存储过程都添加 成功了
最后来调用一个 EF 为我们创建的存储过程
- // 添加一个订单
- var parameterList = new List<SqlParameter>
- {
- new SqlParameter{ ParameterName="@Id",SqlDbType = System.Data.SqlDbType.NVarChar,Value = Guid.NewGuid().ToString()},
- new SqlParameter{ ParameterName="@OrderNO",SqlDbType = System.Data.SqlDbType.NVarChar,Value ="order435435"},
- new SqlParameter{ ParameterName="@Description",SqlDbType = System.Data.SqlDbType.NVarChar,Value="description4364537ryrtey"},
- new SqlParameter{ ParameterName="@AddTime",SqlDbType = System.Data.SqlDbType.DateTime,Value =DateTime.Now}
- };
- var paraArr = parameterList.ToArray();
- var res = ctx.Database.ExecuteSqlCommand("dbo.addOrder @Id,@OrderNO,@Description,@AddTime",paraArr);
- Console.WriteLine(res); // result:1
- View Code
可以的
来源: https://www.cnblogs.com/jinshan-go/p/10276593.html