logo标签列表

存储过程中的表值参数和实体框架4.0

sql-serversql-server-2008stored-proceduresentity-framework-4table-valued-parameters
8
我有一个存储过程在 SQL Server 2008 中叫做“GetPrices”，其中包含一个名为“StoreIDs”的表值参数。
这是我创建的该TVP的类型：
CREATE TYPE integer_list_tbltype AS TABLE (n int)

我想从我的实体框架中调用SP。但是当我尝试将存储过程添加到EDM时，会出现以下错误：
“GetPrices”函数在参数索引2处具有数据类型为“表格类型”的参数“StoreIDs”，该类型不受支持。该函数已被排除。
有没有绕过此问题的方法？ 有什么想法吗？
Fabio
4个回答
2

我认为在这种情况下传递CSV字符串是最好的解决方案。 我想提出一种更简单的方法来拆分CSV字符串，而无需创建表格和函数，使用CTE：

declare @separator char(1);
set @separator = ',';

;with baseCte as
(select left(@ValueList, charindex(@separator, @ValueList) - 1) as Value,
substring(@ValueList, charindex(@separator, @ValueList) + 1, len(@ValueList)) 
as rest
union all
select left(rest, charindex(@separator, rest) - 1) as Value, 
substring(rest, charindex(@separator, rest) + 1, len(rest)) from baseCte
where len(rest) > 1
)
select Value from baseCte
OPTION (MAXRECURSION 0);
1

由于无法使用表参数，请尝试传递CSV字符串并让存储过程将其拆分为行。

在SQL Server中，有很多方法可以拆分字符串。本文介绍了几乎每种方法的优缺点：

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

您需要创建一个拆分函数。以下是如何使用拆分函数：

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

我更喜欢使用数字表格的方法在TSQL中拆分字符串但在SQL Server中有许多拆分字符串的方法，请参考上面的链接，其中介绍了每种方法的优缺点。

要使数字表格方法起作用，您需要进行一次表设置，创建一个包含从1到10,000行的表Numbers：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

一旦设置好Numbers表，创建此分割函数：

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(   ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO

现在您可以轻松地将CSV字符串拆分为表格并进行连接，或者按照您的需要使用它：

CREATE PROCEDURE YourProcedure
(
    @CSV_Param   varchar(1000)
)
AS

--just an example of what you can do
UPDATE t
    SET Col1=...
    FROM dbo.FN_ListToTable(',',@CSV_Param) dt
        INNER JOIN TBL_USERS                 t ON  CAST(dt.value AS INT)=t.id

GO
1

您可以使用 ObjectContext.Connection 属性使用 ADO.NET来创建和使用表值参数。这可能不是最理想的选择，但如果您想要同时使用这个很棒的 SQL Server 2008 功能和 EF，这似乎是您唯一的选择。

然后，您可以选择扩展部分生成的对象上下文，使用方法来处理所有低级别的 ADO.NET 工作。就像这样：

public partial class FriendsOnBoardEntities : ObjectContext
{
    public IList<int> GetPrices(int n)
    {
        // 'low-level' ado.net stuff here.
        // Use SqlParameters, SqlCommand and what not...
    }
}
0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接