c# - How to pass Table Valued Parameter to a stored procedure through Webmatrix.Data.Database.Query -

c# - How to pass Table Valued Parameter to a stored procedure through Webmatrix.Data.Database.Query -

the c# code below self explanatory. want pass datatable table valued parameter stored procedure in sql server 2008 through webmatrix.data.database.query in lastly line of c# code below(i.e. appdatabase.query) exception thrown

"the table type parameter '0' must have valid type name."

public jsonresult getuserlistforroles(int[] roleids) { using (database appdatabase = database.open("defaultconnection")) { datatable roleidstable = new datatable(); roleidstable.columns.add("roleid", typeof(int)); foreach(int role in roleids) { roleidstable.rows.add(role); } list<usernameidmodel> usernameidmodellist = new list<usernameidmodel>(); var usernameidlist = appdatabase.query("exec dbo.sp_getuserlistforroles @roleidslist=@0", roleidstable);

in database have created user defined table type using bit of tsql

create type dbo.roleidslist table (roleid int)

it executed fine , shows under types/user-defined table type in sql server management studio

this stored proc(only adding relevant portion show have assigned right type @roleidslist parameter , marked readonly required table valued parameters)

alter procedure [dbo].[sp_getuserlistforroles] -- add together parameters stored procedure here @roleidslist dbo.roleidslist readonly

i have done deal of search not find solutions deals webmatrix.data.database... solutions found involved sqlcommand , sqlparameter. have used webmatrix.data.database through out app , need consistent.

please note: not utilize webmatrix ide. utilize namespace in controllers db interaction in mvc4 app.

eagerly awaiting guidance having expertise in this. lot in advance.

according the documentation must specify type name table-valued parameter using typename property of sqlparameter. webmatrix.data.database helper doesn't provide api specifying aspect of parameter except value. hasn't been designed utilize stored procedures - designed utilize plain sql only. hence have no other alternative utilize plain ado.net in instance can set required properties of sqlparameter yourself.

c# sql-server-2008 webmatrix table-valued-parameters