通过实体框架将数组/表传递给存储过程 [英] Passing an array/table to stored procedure through Entity Framework
问题描述
您不能将表值参数传递给SQL,框架。
您可以做的是创建一个分隔符,如1 | 2 | 3 | 4
和在SQL中创建一个Split函数,返回一个表。
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue表
(
Id int identity(1,1),
数据nvarchar(100)
)
AS
BEGIN
声明@Cnt int
Set @Cnt = 1
While(Charindex (@ SplitOn,@ RowData)> 0)
开始
插入到@RtnValue(数据)
选择
数据= ltrim(rtrim(Substring(@ RowData,1,Charindex (@ SplitOn,@ RowData)-1)))
Set @RowData = Substring(@ RowData,Charindex(@ SplitOn,@ RowData)+ 1,len(@RowData))
设置@Cnt = @Cnt + 1
结束
插入到@RtnValue(数据)
选择Data = ltrim(rtrim(@RowData))
返回
END
然后,如果您需要根据传递给proc的分隔字符串中的所有项目从表中选择所有项目:
SELECT * FROM SomeTable WHERE Id IN(SELECT Id FROM dbo.Split(@DelStr,'|'))
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?
You can't pass table-valued parameters to SQL with the Entity Framework.
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.
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:
SELECT * FROM SomeTable WHERE Id IN (SELECT Id FROM dbo.Split(@DelStr, '|'))
这篇关于通过实体框架将数组/表传递给存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!