CODEWITHSUNDEEP
c#sqlsql-server
Zath
Zath

Reputation: 557

DataTable and Table-Valued Parameter returning no rows

I have a user defined table and a stored procedure:

 CREATE TYPE [dbo].[int_list_tbltype] AS TABLE(
[ID] INT  NULL
 )
 GO

 CREATE PROCEDURE [dbo].[GetList]

 @Status dbo.int_list_tbltype READONLY

 SELECT v.Status, v.Item
 FROM myView v  
 INNER JOIN @Status tvp ON v.Status = tvp.ID

And the C# code:

        DataTable dt = new DataTable();
        SqlDataAdapter sqlAdtp = new SqlDataAdapter();            
        SqlConnection conn = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand("GetList", conn);

        //Create a table just for testing
        DataTable _dt = new DataTable();
        _dt.Columns.Add("ID", typeof(int));
        _dt.Rows.Add(1);
        _dt.Rows.Add(2);

        SqlParameter param = new SqlParameter();
        param.ParameterName = "@Status";
        param.SqlDbType = System.Data.SqlDbType.Structured;
        param.Value = _dt;
        param.TypeName = "dbo.int_list_tbltype";
        cmd.Parameters.Add(param); 

        sqlAdtp.SelectCommand = cmd;
        sqlAdtp.Fill(dt);

I have tested the sql in a seperate query window adding values to the TVP and it brings back a couple hundred rows as expected.
But when I try to pass the parameter into the stored procedure, I am getting back an empty datatable.

Can anyone see where it's failing?

Thanks!

Upvotes: 1

Views: 539

Answers (1)

Zath
Zath

Reputation: 557

And of course taking the time to post a question, the answer pops out at me.

I forgot this one simple line:

 cmd.CommandType = CommandType.StoredProcedure;

Upvotes: 3

Related Questions