问题描述

我有这个 SQL Server 2008 UDT:

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

很简单.基本上可以让我保留一个 id 列表.

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

我有这个存储过程:

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

Entity Framework 4.0 不支持执行将用户定义的表类型作为参数的存储过程，因此我将恢复到经典的 ADO.NET.唯一的其他选项是传递逗号分隔的字符串.

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();

DataTable dt = preparedatatable();

他们甚至不提供那种方法，所以我不知道我应该在这里做什么.

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

这是我的方法签名:

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

所以我有一个 int 列表，需要将其泵入 UDT.

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?

推荐答案

这篇文章 可能会有更多帮助.

基本上，您将创建一个与架构匹配的新 DataTable，然后将其作为参数传递.

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

preparedatatable() 的代码可能类似于:

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

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

之后，您必须添加您的 locationId:

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);
}

然后将dt赋值为参数:

Then assign dt as a parameter:

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

这篇关于经典 ADO.NET - 如何将 UDT 传递给存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！