Passing array of values to SQL stored procedure in .net compact framework

Question

Im working on a windows app (windows CE) and I need to send an array of ID's to stored procedure (Stored in SQL server 2012).

Im working on .net compact framework 3.5 and C#.

I checked the approaches like passing the list as XML attribute and table valued parameter but both give me the same error in compact framework.

Invalid Enumeration Type SqlDbType.XML Invalid Enumeration Type SqlDbType.Structured

Havent found anything that says these are unsupported in compact framework but they dont work too.

What other approach can i folow.

Answer 1

If you need to pass only ID's then maybe this function will help you:

ALTER FUNCTION [dbo].[fn_SplitIds]
(
    @text nvarchar(max)
)
RETURNS @Ids TABLE
(
    Id int UNIQUE
) 
AS
BEGIN

declare     @iStart int,
            @iPos int
if substring( @text, 1, 1 ) = ',' 
begin
    set     @iStart = 2
    insert into @Ids
    values( null )
end
else 
    set     @iStart = 1
while 1=1
begin
    set     @iPos = charindex( ',', @text, @iStart )
    if @iPos = 0
            set     @iPos = len( @text )+1
    if @iPos - @iStart > 0                  
            insert into @Ids
            values  ( cast(substring( @text, @iStart, @iPos-@iStart ) as int))
    else
            insert into @Ids
            values( null )
    set     @iStart = @iPos+1
    if @iStart > len( @text ) 
            break
end

RETURN
END

we use it in our project to support some legacy stuff and it works fine. Just pass integers separated by commas ','.

You do not need to create function.