如何传递表值参数 [英] How to pass a table-value parameter

问题描述

我正在尝试将表值参数传递给存储过程，但我不断收到异常(见下文).

I am trying to pass a table-value parameter to a stored procedure, but I keep getting an exception (see below).

SqlCommand c = new SqlCommand("getPermittedUsers", myConn) { CommandType = CommandType.StoredProcedure };

c.Parameters.AddWithValue("@intNotifyingUserId", notifyingUserId);
c.Parameters.AddWithValue("@tSelectedPdfIds", sharedPdfs).SqlDbType = SqlDbType.Structured;

SqlDataReader dr = c.ExecuteReader();

类型在服务器上定义如下:

The type is defined on the server like this:

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

我尝试将 sharedPdfs 作为 List<int> 和 IQueryable<int> 传递，但不断收到以下异常:

I have tried passing sharedPdfs as a List<int>, and IQueryable<int>, but keep getting the following exception:

对象必须实现 IConvertible.

有人知道我做错了什么吗?该文档暗示我应该能够将列表作为 TVP 传递，但没有给出任何示例.

Anyone know what I am doing wrong? The documentation implies that I should be able to pass a list as a TVP but doesn't give any examples.

谢谢.

推荐答案

您可以将参数传递为 DataTable、IEnumerable 或 DbDataReader.

You can pass the parameter as a DataTable, IEnumerable<SqlDataRecord>, or DbDataReader.

这篇关于如何传递表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！