Has anyone had succes in accessing a stored procedure from a SqlDataProvider passing Table-Valued Parameters?
I'm pretty sure it can be done and my problem is between my keyboard and my chair.
I declared a user defined type and stored procedure as such:
CREATE TYPE string_list_tbltype AS TABLE (s varchar(max) NOT NULL )
create procedure dnn_jefftest
@PortalID int,
@fromlist string_list_tbltype READONLY,
@howmany int
as
set rowcount @howmany
select * from dnn_myMessages
where PortalID = @PortalID and MessageSender in (select s from @fromlist)
set rowcount 0
Then I have an implementation of:
public override IDataReader GetMessageList(int PortalID, int HowMany, List<string> FromList)
{
return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("jefftest"), PortalID, FromList, HowMany);
}
SqlHelper throws an execption: Parameter count does not match Parameter Value count
I'm sure that I'm not formulating the parameters correctly, anyone have a suggestion / answer?
I really don't want to use dynamic SQL for this, there are performance & security implications that I'd prefer not to have to deal with.
Thanks!