我对实体框架6非常陌生，我想在我的项目中实现存储过程。我有一个存储过程如下:
ALTER PROCEDURE [dbo].[insert_department]
@Name [varchar](100)
AS
BEGIN
INSERT [dbo].[Departments]([Name])
VALUES (@Name)
DECLARE @DeptId int
SELECT @DeptId = [DeptId]
FROM [dbo].[Departments]
WHERE @@ROWCOUNT > 0 AND [DeptId] = SCOPE_IDENTITY()
SELECT t0.[DeptId]
FROM [dbo].[Departments] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[DeptId] = @DeptId
END
系类:
public class Department
{
public int DepartmentId { get; set; }
public string Name { get; set; }
}
modelBuilder
.Entity<Department>()
.MapToStoredProcedures(s =>
s.Update(u => u.HasName("modify_department")
.Parameter(b => b.Department, "department_id")
.Parameter(b => b.Name, "department_name"))
.Delete(d => d.HasName("delete_department")
.Parameter(b => b.DepartmentId, "department_id"))
.Insert(i => i.HasName("insert_department")
.Parameter(b => b.Name, "department_name")));
protected void btnSave_Click(object sender, EventArgs e)
{
string department = txtDepartment.text.trim();
// here I want to call the stored procedure to insert values
}
我的问题是:如何调用存储过程并将参数传递给它?
在MYsql中创建Procedure。
delimiter //
create procedure SP_Dasboarddata(fromdate date, todate date)
begin
select count(Id) as count,date,status,sum(amount) as amount from
details
where (Emidate between fromdate and todate)
group by date ,status;
END;
//
创建包含存储过程返回结果集值的类
[Table("SP_reslutclass")]
public class SP_reslutclass
{
[Key]
public int emicount { get; set; }
public DateTime Emidate { get; set; }
public int ? Emistatus { get; set; }
public int emiamount { get; set; }
}
在Dbcontext中添加Class
public class ABCDbContext:DbContext
{
public ABCDbContext(DbContextOptions<ABCDbContext> options)
: base(options)
{
}
public DbSet<SP_reslutclass> SP_reslutclass { get; set; }
}
在存储库中调用实体
var counts = _Dbcontext.SP_reslutclass.FromSql("call SP_Dasboarddata
('2019-12-03','2019-12-31')").ToList();
它为我工作在代码第一。它返回一个匹配视图模型属性的列表(StudentChapterCompletionViewModel)
var studentIdParameter = new SqlParameter
{
ParameterName = "studentId",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.BigInt,
Value = studentId
};
var results = Context.Database.SqlQuery<StudentChapterCompletionViewModel>(
"exec dbo.sp_StudentComplettion @studentId",
studentIdParameter
).ToList();
已更新
Context是继承DbContext类的实例，如下所示。
public class ApplicationDbContext : DbContext
{
public DbSet<City> City { get; set; }
}
var Context = new ApplicationDbContext();
看一下这个链接，它展示了如何将EF 6与存储过程映射为插入、更新和删除:http://msdn.microsoft.com/en-us/data/dn468673
除了
下面是一个从Code First调用存储过程的好例子:
假设你必须执行一个带有单个参数的存储过程，并且该存储过程返回一组与实体状态匹配的数据，因此我们将有:
var countryIso = "AR"; //Argentina
var statesFromArgentina = context.Countries.SqlQuery(
"dbo.GetStatesFromCountry @p0", countryIso
);
现在假设我们想要执行另一个带有两个参数的存储过程:
var countryIso = "AR"; //Argentina
var stateIso = "RN"; //Río Negro
var citiesFromRioNegro = context.States.SqlQuery(
"dbo.GetCitiesFromState @p0, @p1", countryIso, stateIso
);
注意，我们对参数使用基于索引的命名。这是因为实体框架将这些参数包装为DbParameter对象，以避免任何SQL注入问题。
希望这个例子能有所帮助!
在MYsql中创建Procedure。
delimiter //
create procedure SP_Dasboarddata(fromdate date, todate date)
begin
select count(Id) as count,date,status,sum(amount) as amount from
details
where (Emidate between fromdate and todate)
group by date ,status;
END;
//
创建包含存储过程返回结果集值的类
[Table("SP_reslutclass")]
public class SP_reslutclass
{
[Key]
public int emicount { get; set; }
public DateTime Emidate { get; set; }
public int ? Emistatus { get; set; }
public int emiamount { get; set; }
}
在Dbcontext中添加Class
public class ABCDbContext:DbContext
{
public ABCDbContext(DbContextOptions<ABCDbContext> options)
: base(options)
{
}
public DbSet<SP_reslutclass> SP_reslutclass { get; set; }
}
在存储库中调用实体
var counts = _Dbcontext.SP_reslutclass.FromSql("call SP_Dasboarddata
('2019-12-03','2019-12-31')").ToList();
