如何在实体框架6中调用存储过程(代码优先)?

我对实体框架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

Department类:

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
}

我的问题是:如何调用存储过程并将参数传递给它?

551641 次浏览
小开

你正在使用MapToStoredProcedures()，这表明你正在将你的实体映射到存储过程中，当你这样做时，你需要放弃存在一个存储过程的事实，并正常使用context。 像这样(写进浏览器，所以没有测试)

using(MyContext context = new MyContext())
{
Department department = new Department()
{
Name = txtDepartment.text.trim()
};
context.Set<Department>().Add(department);
}

如果你真正想做的只是直接调用存储过程，那么使用SqlQuery

小开

你可以在DbContext类中调用存储过程，如下所示。

this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);

但是如果您的存储过程返回多个结果集作为示例代码，那么您可以在MSDN上看到这篇有用的文章

具有多个结果集的存储过程 .

小开

看一下这个链接，它展示了如何将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注入问题。

希望这个例子能有所帮助!

小开

我用ExecuteSqlCommand解决了它

把你自己的方法放在DbContext中作为你自己的实例:

public void addmessage(<yourEntity> _msg)
{
var date = new SqlParameter("@date", _msg.MDate);
var subject = new SqlParameter("@subject", _msg.MSubject);
var body = new SqlParameter("@body", _msg.MBody);
var fid = new SqlParameter("@fid", _msg.FID);
this.Database.ExecuteSqlCommand("exec messageinsert @Date , @Subject , @Body , @Fid", date,subject,body,fid);
}

所以你可以在你的后台代码中有一个这样的方法:

[WebMethod] //this method is static and i use web method because i call this method from client side
public static void AddMessage(string Date, string Subject, string Body, string Follower, string Department)
{
try
{
using (DBContext reposit = new DBContext())
{
msge <yourEntity> Newmsg = new msge();
Newmsg.MDate = Date;
Newmsg.MSubject = Subject.Trim();
Newmsg.MBody = Body.Trim();
Newmsg.FID= 5;
reposit.addmessage(Newmsg);
}
}
catch (Exception)
{
throw;
}
}

这是我的SP:

Create PROCEDURE dbo.MessageInsert


@Date nchar["size"],
@Subject nchar["size"],
@Body nchar["size"],
@Fid int
AS
insert into Msg (MDate,MSubject,MBody,FID) values (@Date,@Subject,@Body,@Fid)
RETURN

希望这对你有帮助。

小开

现在还可以使用我创建的约定，该约定允许从EF本机调用存储过程(包括返回多个结果集的存储过程)、tvf和标量udf。

在实体框架6.1发布之前，存储函数(即表值函数和存储过程)只能在数据库优先的情况下在EF中使用。有一些变通办法可以在Code First应用中调用存储函数，但你仍然不能在Linq查询中使用tvf，这是最大的限制之一。在EF 6.1中，映射API是公开的(以及一些额外的调整)，这使得在Code First应用中使用存储函数成为可能。

阅读更多

在过去的两周里，我一直在努力推广，现在终于有了这个约定的beta版本，它允许在使用代码优先方法和实体框架6.1.1(或更新版本)的应用程序中使用存储函数(即存储过程、表值函数等)。我对这个版本中包含的修复和新功能非常满意。

阅读更多。

小开

您所要做的就是创建一个对象，该对象具有与存储过程返回的结果相同的属性名。对于以下存储过程:

    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。

小开

盲目的乘客有一个项目，允许使用实体框架从存储过程返回多个结果集。下面是他的一个例子....

using (testentities te = new testentities())
{
//-------------------------------------------------------------
// Simple stored proc
//-------------------------------------------------------------
var parms1 = new testone() { inparm = "abcd" };
var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
var r1 = results1.ToList<TestOneResultSet>();
}
小开

用你的例子，这里有两种方法来实现这一点:

方法# 1:使用存储过程映射

请注意，这段代码可以使用映射，也可以不使用映射。如果在实体上关闭映射，EF将生成一个插入+选择语句。

protected void btnSave_Click(object sender, EventArgs e)
{
using (var db = DepartmentContext() )
{
var department = new Department();
        

department.Name = txtDepartment.text.trim();
        

db.Departments.add(department);
db.SaveChanges();
        

// EF will populate department.DepartmentId
int departmentID = department.DepartmentId;
}
}

方法# 2:直接调用存储过程

protected void btnSave_Click(object sender, EventArgs e)
{
using (var db = DepartmentContext() )
{
var name = new SqlParameter("@name", txtDepartment.text.trim());
        

//to get this to work, you will need to change your select inside dbo.insert_department to include name in the resultset
var department = db.Database.SqlQuery<Department>("dbo.insert_department @name", name).SingleOrDefault();


//alternately, you can invoke SqlQuery on the DbSet itself:
//var department = db.Departments.SqlQuery("dbo.insert_department @name", name).SingleOrDefault();
        

int departmentID = department.DepartmentId;
}
}

我建议使用第一种方法，因为你可以直接使用department对象，而不必创建一堆SqlParameter对象。

小开 
object[] xparams = {
new SqlParameter("@ParameterWithNumvalue", DBNull.Value),
new SqlParameter("@In_Parameter", "Value"),
new SqlParameter("@Out_Parameter", SqlDbType.Int) {Direction = ParameterDirection.Output}};


YourDbContext.Database.ExecuteSqlCommand("exec StoredProcedure_Name @ParameterWithNumvalue, @In_Parameter, @Out_Parameter", xparams);
var ReturnValue = ((SqlParameter)params[2]).Value;
小开

如果你想把表参数传递到存储过程中，你必须为你的表参数设置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();
小开

对我来说，这是通过在传递参数时从存储过程中拉回数据来实现的。

var param = new SqlParameter("@datetime", combinedTime);
var result =
_db.Database.SqlQuery<QAList>("dbo.GetQAListByDateTime @datetime", param).ToList();

_db是dbContext

小开

这是EF (DB first)在DbContext类中生成的:

public ObjectResult<int> Insert_Department(string department)
{
var departmentParameter = new ObjectParameter("department", department);


return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("insert_department", departmentParameter);
}
小开 
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();
}
小开

它为我工作在代码第一。它返回一个匹配视图模型属性的列表(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();
小开

当EDMX创建时，如果你在表选择选项中选择存储过程，那么只需使用过程名调用存储过程…

var num1 = 1;
var num2 = 2;


var result = context.proc_name(num1,num2).tolist();// list or single you get here.. using same thing you can call insert,update or delete procedured.
小开

我发现以代码优先的方式调用存储过程并不方便。

我更喜欢使用Dapper代替。

下面的代码是用实体框架编写的:

var clientIdParameter = new SqlParameter("@ClientId", 4);


var result = context.Database
.SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
.ToList();

下面的代码是用Dapper编写的:

return Database.Connection.Query<ResultForCampaign>(
"GetResultsForCampaign ",
new
{
ClientId = 4
},
commandType: CommandType.StoredProcedure);

我认为第二段代码更容易理解。

小开 
public static string ToSqlParamsString(this IDictionary<string, string> dict)
{
string result = string.Empty;
foreach (var kvp in dict)
{
result += $"@{kvp.Key}='{kvp.Value}',";
}
return result.Trim(',', ' ');
}


public static List<T> RunSproc<T>(string sprocName, IDictionary<string, string> parameters)
{
string command = $"exec {sprocName} {parameters.ToSqlParamsString()}";
return Context.Database.SqlQuery<T>(command).ToList();
}
小开

没什么…当您为代码创建dbcontext时，首先要进行初始化 命名空间下面的fluent API区域列出sp和使用它的另一个地方，你想。< / p > 

public partial class JobScheduleSmsEntities : DbContext
{
public JobScheduleSmsEntities()
: base("name=JobScheduleSmsEntities")
{
Database.SetInitializer<JobScheduleSmsEntities>(new CreateDatabaseIfNotExists<JobScheduleSmsEntities>());
}


public virtual DbSet<Customer> Customers { get; set; }
public virtual DbSet<ReachargeDetail> ReachargeDetails { get; set; }
public virtual DbSet<RoleMaster> RoleMasters { get; set; }


protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//modelBuilder.Types().Configure(t => t.MapToStoredProcedures());


//modelBuilder.Entity<RoleMaster>()
//     .HasMany(e => e.Customers)
//     .WithRequired(e => e.RoleMaster)
//     .HasForeignKey(e => e.RoleID)
//     .WillCascadeOnDelete(false);
}
public virtual List<Sp_CustomerDetails02> Sp_CustomerDetails()
{
//return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Sp_CustomerDetails02>("Sp_CustomerDetails");
//  this.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails");
using (JobScheduleSmsEntities db = new JobScheduleSmsEntities())
{
return db.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails").ToList();


}


}


}

｝

public partial class Sp_CustomerDetails02
{
public long? ID { get; set; }
public string Name { get; set; }
public string CustomerID { get; set; }
public long? CustID { get; set; }
public long? Customer_ID { get; set; }
public decimal? Amount { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public int? CountDay { get; set; }
public int? EndDateCountDay { get; set; }
public DateTime? RenewDate { get; set; }
public bool? IsSMS { get; set; }
public bool? IsActive { get; set; }
public string Contact { get; set; }
}
小开

首先使用MySql和实体框架代码

public class Vw_EMIcount
{
public int EmiCount { get; set; }
public string Satus { get; set; }
}


var result = context.Database.SqlQuery<Vw_EMIcount>("call EMIStatus('2018-3-01' ,'2019-05-30')").ToList();
小开

你可以将参数传递给sp_GetById，并在ToList()或FirstOrDefault();中获取结果

var param  = new SqlParameter("@id", 106);
var result = dbContext
.Database
.SqlQuery<Category>("dbo.sp_GetById @id", param)
.FirstOrDefault();
小开

在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();
小开

.NET Core 5.0没有FromSql，而是有FromSqlRaw

下面这些都对我有用。Account类在这里是c#中的实体，具有与数据库中完全相同的表和列名。

App配置类如下所示

class AppConfiguration
{
public AppConfiguration()
{
var configBuilder = new ConfigurationBuilder();
var path = Path.Combine(Directory.GetCurrentDirectory(), "appsettings.json");
configBuilder.AddJsonFile(path, false);
var root = configBuilder.Build();
var appSetting = root.GetSection("ConnectionStrings:DefaultConnection");
sqlConnectionString = appSetting.Value;
}


public string sqlConnectionString { get; set; }
}

DbContext类:

public class DatabaseContext : DbContext
{
public class OptionsBuild
{
public OptionsBuild()
{
setting = new AppConfiguration();
opsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
opsBuilder.UseSqlServer(setting.sqlConnectionString);
dbOptions = opsBuilder.Options;
}


public DbContextOptionsBuilder<DatabaseContext> opsBuilder { get; set; }
public DbContextOptions<DatabaseContext> dbOptions { get; set; }


private AppConfiguration setting { get; set; }
}


public static OptionsBuild ops = new OptionsBuild();


public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
{
//disable initializer
//  Database.SetInitializer<DatabaseContext>(null);
}


public DbSet<Account> Account { get; set; }
}

这段代码应该在你的数据访问层:

List<Account> accounts = new List<Account>();
var context = new DatabaseContext(DatabaseContext.ops.dbOptions);
accounts = await context.Account.ToListAsync();   //direct select from a table


var param = new SqlParameter("@FirstName", "Bill");
accounts = await context.Account.FromSqlRaw<Account>("exec Proc_Account_Select",
param).ToListAsync();            //procedure call with parameter
        

accounts = context.Account.FromSqlRaw("SELECT * FROM dbo.Account").ToList();  //raw query

提交答案