C# Passing Table Valued Parameter to SqlCommand not Working

Question

I'm trying to run a test case, and this won't even work... What am I doing wrong here?

Here's the SQL:

CREATE TABLE 
    Playground.Test (saved DateTime)
GO
CREATE TYPE
    Playground.DateTimeTable AS TABLE
    ([time] DATETIME);
GO
CREATE PROCEDURE
    Playground.InsertDate
    @dt Playground.DateTimeTable READONLY
AS
    BEGIN
        INSERT INTO Playground.Test (saved) 
        SELECT [time] 
        FROM @dt
    END
GO

And code to connect and execute the procedure:

const String connString = 
    "server = SERVER; database = DB; UID = myUserID; pwd = myPassword;";
static void Main(string[] args)
{
    SqlCommand command =
        new SqlCommand(
            "EXEC Playground.InsertDate",
            new SqlConnection(connString));

    DataTable table = new DataTable("DateTimeTable");
    table.Columns.Add("[time]", typeof(DateTime));
    table.Rows.Add(DateTime.Parse("10/27/2004"));

    SqlParameter tvp = command.Parameters.AddWithValue("@dt", table);
    tvp.SqlDbType = SqlDbType.Structured;
    tvp.TypeName = "Playground.DateTimeTable";

    command.Connection.Open();
    int affected = command.ExecuteNonQuery();
    command.Connection.Close();

    Console.WriteLine(affected);
    Console.ReadKey();
}

I'm not getting any errors. Just 0 rows affected.

This works in SQL Server, though:

DECLARE @dt Playground.DateTimeTable
INSERT INTO @dt VALUES ('2004-10-27')
EXEC Playground.InsertDate @dt

What am I supposed to be doing here?

Answer 1

You are not setting your SqlCommand object to be a stored procedure. You should do a couple of things:

1. Remove the EXEC prefix from the string ~(it's not needed)

2. Set command to be a stored procedure:

   command.CommandType = CommandType.StoredProcedure;
   

3. Not sure how the square braces around the DataTable column names will affect this either, but I suspect it's better with them removed.