Stored Procedure with Table Value Parameter is not getting the data

Vote count:

0

I have a very simple stored procedure with a table value parameter that when it is executed, even though I've made sure the DataTable on the C# side does have data, it seems that the data in the parameter is not reaching the sproc.

To illustrate:

CREATE TYPE [dbo].[StudentParam] AS TABLE(
    [ID] [int] NULL,
    [LastName] [nvarchar](max) NULL,
    [FirstMidName] [nvarchar](max) NULL,
    [EnrollmentDate] [datetime] NULL
) 

CREATE PROCEDURE [dbo].[MySproc]
    @StudentParam StudentParam READONLY
AS
BEGIN
    SELECT count(*) FROM @StudentParam
END

On the C# side:

var student = new DataTable("Student");
student.Columns.Add("StudentId");
student.Columns.Add("LastName");
student.Columns.Add("FirstMidName");
student.Columns.Add("EnrollmentDate");

for (int i = 0; i < 10; i++)
{
   var studentRow = student.NewRow();
   studentRow["StudentId"] = (i + 1) * -1;
   studentRow["FirstMidName"] = "Student";
   studentRow["LastName"] = i.ToString();
   studentRow["EnrollmentDate"] = DateTime.Now;

   student.Rows.Add(studentRow);
}

using (var dr = cmd.ExecuteReader())
{
    while (dr.Read())
    {
        Debug.WriteLine(string.Format("Count: {0}", dr[0].ToString()));
    }
}

In this dumb example, instead of returning the expected 10 the DataReader always returns 0.

What am I missing?

Thank you.

asked 30 secs ago

Sergio Romero

1,453