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

I am very new to Entity Framework 6 and I want to implement stored procedures in my project. I have a stored procedure as follows:

我对Entity Framework 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 class:

系类:

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
}

My problem is: how can I call the stored procedure and pass parameters into it?

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

15 个解决方案

#1

You can call a stored procedure in your DbContext class as follows.

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

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

Here is an example on how to call a stored procedure with input and output parameters and that it returns a FormularioVentasTO (YourEntityType)

这里有一个如何调用带有输入和输出参数的存储过程的示例，它返回公式oventasto (YourEntityType)

    public FormularioVentasTO GetFormularioVentas(string idUsuario, string idFormulario)
    {
        FormularioVentasTO formVentas;
        string sqlQuery;
        SqlParameter []sqlParams;

        formVentas = null;

        try
        {
            sqlQuery = "sp_ConsultarSolicitud @idUsuario, @idSolicitud, codError";

            sqlParams = new SqlParameter[]
            {
                new SqlParameter { ParameterName = "@idUsuario",  Value =idUsuario , Direction = System.Data.ParameterDirection.Input},
                new SqlParameter { ParameterName = "@idSolicitud",  Value =idFormulario, Direction = System.Data.ParameterDirection.Input },
                new SqlParameter { ParameterName = "@codError",  Value =-99, Direction = System.Data.ParameterDirection.Output },
            };

            using (PortalFinandinaPriv dbContext = new PortalFinandinaPriv())
            {
                formVentas = dbContext.Database.SqlQuery<FormularioVentasTO>(sqlQuery, sqlParams).SingleOrDefault();
            }
        }
        catch (Exception ex)
        {
            //handle, Log or throw exception 
        }

        return formVentas;
    }

If your stored procedure returns multiple result sets as in your sample code, then you can see this helpful article on MSDN

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

Stored Procedures with Multiple Result Sets

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

#2

All you have to do is create an object that has the same property names as the results returned by the stored procedure. For the following stored procedure:

您只需创建一个对象，该对象的属性名称与存储过程返回的结果相同。对于以下存储过程:

    CREATE PROCEDURE [dbo].[GetResultsForCampaign]  
    @ClientId int   
    AS
    BEGIN
    SET NOCOUNT ON;

    SELECT AgeGroup, Gender, Payout
    FROM IntegrationResult
    WHERE ClientId = @ClientId
    END

create a class that looks like:

创建一个类:

    public class ResultForCampaign
    {
        public string AgeGroup { get; set; }

        public string Gender { get; set; }

        public decimal Payout { get; set; }
    }

and then call the procedure by doing the following:

然后按以下步骤调用:

    using(var context = new DatabaseContext())
    {
            var clientIdParameter = new SqlParameter("@ClientId", 4);

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

The result will contain a list of ResultForCampaign objects. You can call SqlQuery using as many parameters as needed.

结果将包含一个ResultForCampaign对象的列表。您可以使用所需的任意多个参数调用SqlQuery。

#3

I solved it with ExecuteSqlCommand

我用ExecuteSqlCommand解决了它

Put your own method like mine in DbContext as your own instances:

将您自己的方法(如我的方法)放在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);
}

so you can have a method in your code-behind like this :

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

[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)
{
    int resault;
    try
    {
        using (DBContex reposit = new DBContex())
        {
            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;
    }
}

this is my SP :

这是我的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

hope helped you

希望帮助你

#4

Using your example, here are two ways to accomplish this:

通过你的例子，有两种方法可以达到这个目的:

1 - Use Stored procedure mapping

Note that this code will work with or without mapping. If you turn off mapping on the entity, EF will generate an insert + select statement.

注意，该代码可以使用映射，也可以不使用映射。如果关闭实体上的映射，EF将生成一个insert + select语句。

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 - Call the stored procedure directly

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;
     }
}

I recommend using the first approach, as you can work with the department object directly and not have to create a bunch of SqlParameter objects.

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

#5

You are using MapToStoredProcedures() which indicates that you are mapping your entities to stored procedures, when doing this you need to let go of the fact that there is a stored procedure and use the context as normal. Something like this (written into the browser so not tested)

您正在使用maptostoredprocedure()，它表明您正在将实体映射到存储过程，在这样做时，您需要放弃存储过程的事实，并像往常一样使用上下文。类似这样的东西(写进浏览器，所以没有经过测试)

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

If all you really trying to do is call a stored procedure directly then use SqlQuery

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

#6

You can now also use a convention I created which enables invoking stored procedures (including stored procedures returning multiple resultsets), TVFs and scalar UDFs natively from EF.

您现在还可以使用我创建的一个约定，该约定允许从EF调用存储过程(包括存储过程返回多个resultset)、TVFs和标量UDFs。

Until Entity Framework 6.1 was released store functions (i.e. Table Valued Functions and Stored Procedures) could be used in EF only when doing Database First. There were some workarounds which made it possible to invoke store functions in Code First apps but you still could not use TVFs in Linq queries which was one of the biggest limitations. In EF 6.1 the mapping API was made public which (along with some additional tweaks) made it possible to use store functions in your Code First apps.

在实体框架6.1发布之前，只能在做数据库时在EF中使用存储函数(即表值函数和存储过程)。有一些解决方案使在代码第一的应用程序中调用存储功能成为可能，但是在Linq查询中仍然不能使用TVFs，这是最大的限制之一。在EF 6.1中，映射API被公开，这使得在代码第一应用程序中使用存储功能成为可能。

Read more

阅读更多

I pushed quite hard for the past two weeks and here it is – the beta version of the convention that enables using store functions (i.e. stored procedures, table valued functions etc.) in applications that use Code First approach and Entity Framework 6.1.1 (or newer). I am more than happy with the fixes and new features that are included in this release.

在过去的两周里，我做了很大的努力，这就是——在使用代码优先方法和实体框架6.1.1(或更新)的应用程序中，允许使用存储函数(例如存储过程、表值函数等)的beta版本。我对这个版本中包含的修复和新特性非常满意。

Read more.

阅读更多。

#7

This works for me by pulling back data from a stored procedure while passing in a parameter.

这对我来说很有用，它可以在传递参数时从存储过程中提取数据。

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

_db is the dbContext

_db是dbContext

#8

Take a look to this link that shows how works the mapping of EF 6 with Stored Procedures to make an Insert, Update and Delete: http://msdn.microsoft.com/en-us/data/dn468673

请看这个链接，它展示了如何使用存储过程来进行插入、更新和删除:http://msdn.microsoft.com/en-us/data/dn468673

Addition

除了

Here is a great example to call a stored procedure from Code First:

下面是一个很好的例子，首先从代码调用存储过程:

Lets say you have to execute an Stored Procedure with a single parameter, and that Stored Procedure returns a set of data that match with the Entity States, so we will have this:

假设您必须使用单个参数执行存储过程，并且该存储过程返回与实体状态匹配的一组数据，因此我们将拥有以下内容:

var countryIso = "AR"; //Argentina

var statesFromArgentina = context.Countries.SqlQuery(
                                      "dbo.GetStatesFromCountry @p0", countryIso
                                                    );

Now lets say that we whant to execute another stored procedure with two parameters:

现在，让我们假设我们想执行另一个包含两个参数的存储过程:

var countryIso = "AR"; //Argentina
var stateIso = "RN"; //Río Negro

var citiesFromRioNegro = context.States.SqlQuery(
                            "dbo.GetCitiesFromState @p0, @p1", countryIso, stateIso
                          );

Notice that we are using index-based naming for parameters. This is because Entity Framework will wrap these parameters up as DbParameter objects fro you to avoid any SQL injection issues.

注意，我们正在使用基于索引的参数命名。这是因为实体框架将这些参数包装为DbParameter对象，以避免任何SQL注入问题。

Hope this example helps!

希望这个例子帮助!

#9

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

        YourDbContext.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter", xparams);
        var ReturnValue = ((SqlParameter)params[2]).Value;  

#10

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();
}

#11

It work for me at code first. It return a list with matching property of view model(StudentChapterCompletionViewModel)

这对我来说首先是代码。它返回一个具有视图模型匹配属性的列表(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();

#12

Mindless passenger has a project that allows for multiple results sets to be returned from a stored proc using entity framework. One of his examples below....

没有头脑的乘客有一个项目，允许使用实体框架从存储的proc返回多个结果集。他的一个例子,下面....

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>();
}

#13

if you wanna pass table params into stored procedure, you must necessary set TypeName property for your table params.

如果要将表参数传递到存储过程，必须为表参数设置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();

#14

This is what EF (DB first) generates in the DbContext class:

这是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);
}

#15

When EDMX create this time if you select stored procedured in table select option then just call store procedured using procedured name...

当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.