经典的ADO.NET - 如何将UDT传递给存储过程？

Question

我有一个SQL Server 2008用户定义类型（UDT）：

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

很简单。基本上允许我持有一个ID列表。 我有这个存储过程：

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

Entity Framework 4.0不支持执行以用户定义表类型作为参数的存储过程，所以我回到了传统的ADO.NET。唯一的其他选择是传递一个逗号分隔的字符串。

无论如何，我找到了this篇文章，但是有点难懂。

我不明白这行代码在做什么：

DataTable dt = preparedatatable();

他们甚至没有提供这个方法，所以我不知道我应该在这里做什么。

这是我的方法签名：

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

所以我有一个int列表，需要将其输入到UDT中。

有人能帮我吗/指向一篇清晰的文章来实现这一点吗？

- RPM1984

Answer 1

这篇文章可能会更有帮助。

基本上，您需要创建一个与模式匹配的新DataTable，然后将其作为参数传递。

preparedatatable()函数的代码可能看起来像：

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

接下来，您需要添加您的位置ID：

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

然后将dt指定为一个参数：

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

- Chris Shaffer

---

啊，好的，那很有道理。现在正在尝试，敬请关注...（暂时加一分）。 - RPM1984

工作正常，但出现某种原因导致它需要超过10秒才能完成，最终SQL Profiler显示“错误-2-中止”。生成的SQL看起来没问题 - 在SSMS中运行时只需<1秒。因此，我现在尝试使用这种方法：https://dev59.com/xk3Sa4cB1Zd3GeqPyMyn 不管怎样，还是谢谢。 - RPM1984

我已经使用过这个，而没有花费10秒钟; 不确定是什么原因。无论如何，如果参数只需要是整数列表，那么你最好使用CSV列表和分割函数。当您尝试为每行发送多个关联值（例如，类型中的多个列）时，TVP真的非常有帮助，而CSV列表则需要辅助分隔符（例如，一个基于管道的数据的CSV列表）。 - Chris Shaffer