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

Question

The c# code below is self explanatory. I want to pass a DataTable as a table valued parameter to a stored procedure in SQL Server 2008 through WebMatrix.Data.Database.Query but in the last line of the c# code below(i.e. appDatabase.Query) an exception is thrown

"The table type parameter '0' must have a 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 the Database i have created a User defined table type using this bit of TSQL

CREATE TYPE dbo.RoleIdsList AS TABLE (RoleId int)

it executed fine and shows up under Types/User-Defined Table Type in SQL Server Management Studio

This is the stored proc(Only adding the relevant portion to show that i have assigned the right type to the @roleIdsList parameter and also marked it as READONLY as is required for Table Valued parameters)

ALTER PROCEDURE [dbo].[sp_GetUserListForRoles] 
    -- Add the parameters for the stored procedure here
    @roleIdsList dbo.RoleIdsList READONLY

I have done a good deal of search but could not find the solutions that deals with WebMatrix.Data.Database... All the solutions i found involved SqlCommand and SqlParameter. I have used WebMatrix.Data.Database through out my app and need to be consistent.

PLEASE NOTE: i do not use the WebMatrix IDE. I just use this namespace in my controllers for DB interaction in my MVC4 app.

Eagerly awaiting for some guidance from someone having expertise in this. Thanks a lot in advance.

Answer 1

According to the documentation you must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. The WebMatrix.Data.Database helper doesn't provide an API for specifying any aspect of a parameter except its value. It hasn't been designed for use with stored procedures - it was initially designed for use with plain SQL only. Therefore you have no other option but to use plain ADO.NET in this instance so you can set the required properties of the SqlParameter yourself.