Udt as SQL Server SP parameter coming through empty

Question

I've got a bit of a problem that I just cannot fathom. I'm passing a user defined table type as a parameter from C# to a SQL Server stored procedure and always end up with 0 rows in the table once it reaches the SP. The .NET code is Framework 4.7, the SQL Server is hosted in Azure.

The SP definition is

CREATE PROCEDURE [dbo].[sp_ImportData]
    @ImportData udt_ImportData READONLY
AS
    INSERT INTO dbo.DEBUG ([Key], [Value])
         SELECT 'IMPORTDATA',
                (SELECT COUNT(1)
                   FROM @ImportData);

    ...
RETURN 0

The udt is defined as

CREATE TYPE [dbo].[udt_ImportData] AS TABLE
(
    RecordId INT,
    RecordName NVARCHAR(100)
);

The code I'm calling the SP with is

var table = new DataTable();
table.Columns.Add("RecordId", typeof(int));
table.Columns.Add("RecordName", typeof(string));

foreach (DataRow row in data.Rows)
{
    table.Rows.Add(row["RecordId"], row["RecordName"]);
}

using (var connection = new SqlConnection(_connectionString))
{
    var command = new SqlCommand("sp_ImportData", connection);
    SqlParameter tvpParam = command.Parameters.AddWithValue("@ImportData", table);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.udt_ImportData";

    connection.Open();
    command.ExecuteNonQuery();
}

Whilst debugging, I've checked that there actually are data rows in the table variable before it's attached as a parameter value. I've also inserted some debugging code into the SP to log out the number of rows in the @ImportData table once it's passed in. This always comes out as zero. I've tried using and not using the @ symbol in the parameter name, I've tried renaming the parameter (in case it was a reserved word that I wasn't aware of). I've tried adding and removing the "dbo." prefix to the udt name in C# but none of these have worked.

If anyone could help, I'd be very appreciative; this is driving me mad! I'm sure it's something quite small that I'm not seeing!

Answer 1

As I wrote in my comment - you didn't specify the command type in your c# code. The default value of the CommandType property is CommandType.Text - that fits inline sql but not stored procedures, so you need to specify CommandType.StoredProcedure. While we are at it, you don't have to specify the parameter's type name, and you really should avoid using AddWithValue.
One more thing - SqlCommand also implements the IDisposable interface, so it also should be used inside a using statement:

using (var connection = new SqlConnection(_connectionString))
{
    using(var command = new SqlCommand("sp_ImportData", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@ImportData", SqlDbType.Structured).Value = table;
        connection.Open();
        command.ExecuteNonQuery();
    }
}