.net - Classic ADO.NET - How to Pass UDT To Stored Procedure?

Question

I have this SQL Server 2008 UDT:

CREATE TYPE [dbo].[IdentityType] AS TABLE(
    [Id] [int] NOT NULL
)

Pretty simple. Basically allows me to hold onto a list of id's.

I have this stored procedure:

CREATE PROCEDURE [dbo].[Scoring_ScoreMultipleLocations]
    @LocationIds [IdentityType] READONLY,
    @DoRanking BIT = 0
AS
BEGIN
   -- irrelevant code.
END

Entity Framework 4.0 does not support executing stored procedures that take user defined table types as a parameter, so i'm reverting to classic ADO.NET. The only other option is passing a comma-seperated string.

Anyway, i found this article, but it's a little hard to follow.

I don't understand what this line of code is doing:

DataTable dt = preparedatatable();

They don't even provide that method, so i have no idea what i'm supposed to do here.

This is my method signature:

internal static void ScoreLocations(List<int> locationIds, bool doRanking)
{
   // how do i create DataTable??
}

So i have a list of int and need to pump that into the UDT.

Can anyone help me out/point me to a clear article on how to achieve this?

Answer 1

This article might be a bit more help.

Essentially, you'll create a new DataTable that matches the schema, then pass it as a parameter.

The code of preparedatatable() probably would look something like:

var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
return dt;

After which, you'd have to add your locationIds:

foreach(var id in locationIds)
{
    var row = dt.NewRow();
    row["Id"] = id;
    dt.Rows.Add(row);
}

Then assign dt as a parameter:

var param = cmd.Parameters.AddWithValue("@LocationIDs", dt);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.IdentityType";