我对实体框架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
}
我的问题是:如何调用存储过程并将参数传递给它?
您所要做的就是创建一个对象，该对象具有与存储过程返回的结果相同的属性名。对于以下存储过程:
CREATE PROCEDURE [dbo].[GetResultsForCampaign]
@ClientId int
AS
BEGIN
SET NOCOUNT ON;
SELECT AgeGroup, Gender, Payout
FROM IntegrationResult
WHERE ClientId = @ClientId
END
创建一个类，如下所示:
public class ResultForCampaign
{
public string AgeGroup { get; set; }
public string Gender { get; set; }
public decimal Payout { get; set; }
}
然后调用该过程，执行以下操作:
using(var context = new DatabaseContext())
{
var clientIdParameter = new SqlParameter("@ClientId", 4);
var result = context.Database
.SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
.ToList();
}
结果将包含ResultForCampaign对象的列表。您可以根据需要使用任意多的参数调用SqlQuery。
在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();
如果你想把表参数传递到存储过程中，你必须为你的表参数设置TypeName属性。
SqlParameter codesParam = new SqlParameter(CODES_PARAM, SqlDbType.Structured);
SqlParameter factoriesParam = new SqlParameter(FACTORIES_PARAM, SqlDbType.Structured);
codesParam.Value = tbCodes;
codesParam.TypeName = "[dbo].[MES_CodesType]";
factoriesParam.Value = tbfactories;
factoriesParam.TypeName = "[dbo].[MES_FactoriesType]";
var list = _context.Database.SqlQuery<MESGoodsRemain>($"{SP_NAME} {CODES_PARAM}, {FACTORIES_PARAM}"
, new SqlParameter[] {
codesParam,
factoriesParam
}
).ToList();
public IList<Models.StandardRecipeDetail> GetRequisitionDetailBySearchCriteria(Guid subGroupItemId, Guid groupItemId)
{
var query = this.UnitOfWork.Context.Database.SqlQuery<Models.StandardRecipeDetail>("SP_GetRequisitionDetailBySearchCriteria @SubGroupItemId,@GroupItemId",
new System.Data.SqlClient.SqlParameter("@SubGroupItemId", subGroupItemId),
new System.Data.SqlClient.SqlParameter("@GroupItemId", groupItemId));
return query.ToList();
}
