实体框架存储过程表值参数 [英] Entity Framework Stored Procedure Table Value Parameter

问题描述

我试图调用接受一个表值参数的存储过程。我知道，这是不直接支持实体框架，但还没有从我知道你可以使用掉的ObjectContext的ExecuteStoreQuery命令去做。我有一个通用的实体框架库在那里我有以下ExecuteStoredProcecdure方式：

I'm trying to call a stored procedure that accepts a table value parameter. I know that this isn't directly supported in Entity Framework yet but from what I understand you can do it using the ExecuteStoreQuery command off of the ObjectContext. I have a generic entity framework repository where I have the following ExecuteStoredProcecdure method:

public IEnumerable<T> ExecuteStoredProcedure<T>(string procedureName, params object[] parameters)
{
    StringBuilder command = new StringBuilder();
    command.Append("EXEC ");
    command.Append(procedureName);
    command.Append(" ");

    // Add a placeholder for each parameter passed in
    for (int i = 0; i < parameters.Length; i++)
    {
        if (i > 0)
            command.Append(",");

        command.Append("{" + i + "}");
    }

    return this.context.ExecuteStoreQuery<T>(command.ToString(), parameters);
}

命令字符串结束这样的：

The command string ends up like this:

EXEC someStoredProcedureName {0},{1},{2},{3},{4},{5},{6},{7}

我试图运行，接受一个表值参数的存储过程这个方法，它打破。我读到这里 中的参数需要的是类型的SqlParameter和的表值参数需要有SqlDbType设置为结构化。所以，我这样做，我得到一个错误，说明：

I tried to run this method on a stored procedure that accepts a table valued parameter and it breaks. I read here that the parameters needed to be of type SqlParameter and the table valued parameter needs to have the SqlDbType set to Structured. So I did this and I get an error stating:

The table type parameter p6 must have a valid type name

所以，我的SqlParameter.TypeName设置为我在数据库上创建和用户定义类型的名称然后当我运行查询，我得到以下真正有用的错误：

So, I set the SqlParameter.TypeName to the name of the user defined type I created on the database and then when I run the query I get the following truly helpful error:

Incorrect syntax near '0'.

我可以得到查询运行，如果我恢复到ADO.NET和并执行数据读取器，但我希望得到它使用数据方面的工作。

I can get the query to run if I revert back to ADO.NET and and execute a data reader but I was hoping to get it to work using the data context.

有没有办法通过使用ExecuteStoreQuery表值参数？此外，我实际使用实体框架code首先和铸造的DbContext到ObjectContext的，可获取ExecuteStoreQuery方法。这是必要的，或我能做到这一点对DbConext呢？

Is there a way to pass a table value parameter using ExecuteStoreQuery? Also, I am actually using Entity Framework Code First and casting the DbContext to an ObjectContext to get the ExecuteStoreQuery method available. Is this necessary or can I do this against the DbConext as well?

推荐答案

更新

我已经添加了此有关的NuGet包的支持 - <一个href=\"https://github.com/Fodsuk/EntityFrameworkExtras#nuget\">https://github.com/Fodsuk/EntityFrameworkExtras#nuget （EF4，EF5，EF6）

i've added support for this on Nuget Package - https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4,EF5,EF6)

查看 GitHub的为code范例库。

check out the GitHub repository for code examples.

稍微偏离的问题，但没有人试图通过用户定义的表走进一家商店PROC不太实用。玩弄尼克的例子，其他职位＃1后，我想出了这一点：

Slightly off question, but none the less useful for people trying to pass User Defined Tables into a store proc. After playing around with Nick's example and other Stackoverflow posts, i came up with this:

  class Program
{
    static void Main(string[] args)
    {
        var entities = new NewBusinessEntities();

        var dt = new DataTable();
        dt.Columns.Add("WarningCode");
        dt.Columns.Add("StatusID");
        dt.Columns.Add("DecisionID");
        dt.Columns.Add("Criticality");

        dt.Rows.Add("EO01", 9, 4, 0);
        dt.Rows.Add("EO00", 9, 4, 0);
        dt.Rows.Add("EO02", 9, 4, 0);

        var caseId = new SqlParameter("caseid", SqlDbType.Int);
        caseId.Value = 1;

        var userId = new SqlParameter("userid", SqlDbType.UniqueIdentifier);
        userId.Value = Guid.Parse("846454D9-DE72-4EF4-ABE2-16EC3710EA0F");

        var warnings = new SqlParameter("warnings", SqlDbType.Structured);
        warnings.Value= dt;
        warnings.TypeName = "dbo.udt_Warnings";

        entities.ExecuteStoreProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);

    }
}

public static class ObjectContextExt
{
    public static void ExecuteStoreProcedure(this ObjectContext context, string storeProcName, params object[] parameters)
    {
        string command = "EXEC " + storeProcName + " @caseid, @userid, @warnings";

        context.ExecuteStoreCommand(command, parameters);
    }

}

和商店PROC看起来如下...

and the Store proc looks like the following...

ALTER PROCEDURE [dbo].[usp_RaiseWarnings_rs]
(
     @CaseID int 
    ,@UserID uniqueidentifier = '846454D9-DE72-4EF4-ABE2-16EC3710EA0F' --Admin
    ,@Warnings dbo.udt_Warnings READONLY
)
AS

和用户定义的表如下所示...

and the User Defined Table looks like the following...

CREATE TYPE [dbo].[udt_Warnings] AS TABLE(
[WarningCode] [nvarchar](5) NULL,
[StatusID] [int] NULL,
[DecisionID] [int] NULL,
[Criticality] [int] NULL DEFAULT ((0))

）

限制，我发现包括：

1. 您传递到ExecuteStoreCommand的参数必须是为了
   在您的商店PROC参数


2. 您必须每一列到您的用户自定义表通过，即使他们有默认值。因此，似乎我不能对我的UDT一个IDENTITY（1,1）NOT NULL列

这篇关于实体框架存储过程表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！