Passing an array/table to stored procedure through Entity Framework

Question

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?

Answer 1

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, '|'))

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

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#