通过实体框架将数组/表传递给存储过程

I am using Entity Framework with C# to make a Silverlight application. I have written some stored procedures which perform database intensive operations and I need to call them from Entity Framework after passing some parameters. In one of the steps, the users select a list of items for which they would need more details. This list of items (in the form of an array of integer IDs) need to be passed to the stored procedure to retrieve more information about those IDs. How do I pass this parameter to the stored procedure?

我正在使用c#的实体框架来制作Silverlight应用程序。我已经编写了一些执行数据库密集型操作的存储过程，我需要在传递一些参数后从实体框架中调用它们。在其中一个步骤中，用户选择需要更多细节的项目列表。需要将项目列表(以整数id数组的形式)传递给存储过程，以检索关于这些id的更多信息。如何将此参数传递给存储过程?

3 个解决方案

#1

You can't pass table-valued parameters to SQL with the Entity Framework.

不能使用实体框架将表值参数传递给SQL。

What you can do is create a delimited string like "1|2|3|4" and create a Split function in SQL that will return a table.

您可以创建一个带分隔符的字符串，如“1|2|3|4”，并在SQL中创建一个分割函数，该函数将返回一个表。

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
Id int identity(1,1),
Data nvarchar(100)
) 
AS  
BEGIN 
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
    Insert Into @RtnValue (data)
    Select 
        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Then if you need to do something like select all items from a table based on what is in the delimited string passed to your proc:

然后，如果您需要做一些事情，比如从一个表中选择所有的项，这些项都是基于已传递给您的proc的分隔字符串中的内容:

SELECT * FROM SomeTable WHERE Id IN (SELECT Id FROM dbo.Split(@DelStr, '|'))

#2

If you are using SQL Server, which I assume is the case, you can create use a table valued parameter to do what you wish. Using a table valued parameter prevents you from parsing an input parameter within the stored procedure and removes the threat of a SQL injection attack by eliminating the use of dynamic SQL.

如果您正在使用SQL Server(我假设是这种情况)，您可以创建一个表值参数来实现您的愿望。使用表值参数可以防止您在存储过程中解析输入参数，并通过消除使用动态SQL来消除SQL注入攻击的威胁。

Here is a great blog article that covers how to do what you wish to do.

这里有一篇很棒的博客文章，介绍了如何做你想做的事。

Using Table-Valued Parameters in SQL Server 2008 and C#

在SQL Server 2008和c#中使用表值参数

#3

-2

Is this what you were looking for?

这就是你要找的吗?

#1