将用户定义的表传递给 SqlCommand (不执行存储过程)

Question

我试图使用一个用户定义的表与一个简单的SqlCommand(不执行存储过程)。
我得到消息Must declare the scalar variable，尽管参数已经定义。

代码(假设accountIds在别处定义，因此dtAccountIds有一些值):

var sqlCommand = new SqlCommand(); 
sqlCommand.CommandText =
@"SELECT * INTO #RecordsToLog
FROM Accounts
WHERE [AccountId] IN (@AccountIds)";
var dtAccountIds = new DataTable();
dtAccountIds.Columns.Add("id", typeof(int));
accountIds.ToList().ForEach(id => dtAccountIds.Rows.Add(id));
sqlCommand.Parameters.AddWithValue("@AccountIds", dtAccountIds).TypeName = "dbo.IdsTable";
sqlCommand.ExecuteNonQuery();

用户定义的表是这样创建的:

CREATE TYPE [dbo].[IdsTable] AS TABLE([id] [int] NULL)

我该怎么做来解决这个问题?

Answer 1

首先，你的SQL代码是错误的。table参数是一个表变量，因此需要一个完整的子查询IN (SELECT id FROM @AccountIds)。

你为什么要插入一个临时表，我不知道，你没有给我们任何上下文。

接下来，你不应该使用AddWithValue，相反，声明确切的类型SqlDbType.Structured，以及Direction和TypeName

另外，我希望您没有缓存连接对象。必须处理连接和命令对象。

const string query = @"
SELECT *
INTO #RecordsToLog
FROM Accounts
WHERE [AccountId] IN
(SELECT id FROM @AccountIds)
";
using (var sqlCommand = new SqlCommand(query, connection))
{
var dtAccountIds = new DataTable { Columns = { { "id", typeof(int) } } };
foreach (var id in accountIds)
dtAccountIds.Rows.Add(id);
sqlCommand.Parameters.Add(new SqlParameter("@AccountIds", SqlDbType.Structured)
{
Value = dtAccountIds,
TypeName = "dbo.IdsTable",
Direction = ParameterDirection.Input
});
sqlCommand.ExecuteNonQuery();
}

Answer 2

必须配置为SqlDbType.Structured。避免使用AddWithValue，让我建议这样标记:

SqlParameter param = new SqlParameter("@AccountIds", SqlDbType.Structured, 0)
{
Value = dtAccountIds
}

根据MS-DOCS about SqlDbType Enum:

结构化

=一种特殊的数据类型，用于指定表值参数中包含的结构化数据。