将空列表或null值绑定到存储过程(.NET)的表值参数

Question

我创建了一个存储过程，它接受一个名为table valued parameter的参数，是一个只有一个int类型列的表。想法很简单，就是将id列表传递给存储过程，让它处理数据。但是，在没有数据要传入的情况下，我遇到了问题（当我有数据时，一切正常）。我将List<int>转换为IEnumerable<SqlDataRecord>，然后绑定到作为存储过程参数的table valued parameter。我尝试绑定一个空的List<SqlDataRecord>，结果出现以下错误：

System.ArgumentException: SqlDataRecord枚举中没有记录。要发送不带行的表值参数，请改用null引用。

然后我尝试绑定null值（我认为上述信息所说的就是这个），但那只会导致不同的错误消息：

System.NotSupportedException: 不支持参数"@MainItemIdList" 的DBNull值。表值参数不能是DBNull。

在存储过程声明中似乎无法将table valued parameter声明为可空。如何将空列表绑定到table valued parameter是正确的方法？

- Brian Jones

Answer 1

技巧在于: 根本不需要传递参数。表值参数的默认值是一个空表。

遗憾的是，异常消息并没有什么帮助。

- Daniel Cassidy

---

添加新答案或改进现有答案从来不会有坏处。这些信息仍然有参考价值，而其他人也会发现它们有用（这也是这个网站存在的原因！）。 +1 - Jason Down

如果使用普通的表适配器，有什么想法吗？TableAdapter 模板会创建一个与存储过程具有相同签名的 CLR 代理函数，并且它期望一个非空值。传递 null 或 DbNull.Value 会引发错误。 - Boris B.

这正是我需要做的 - 经过测试，它非常有效。 - Jim Evans

+1 大感谢 - 确认这种方法对于 SqlDataRecord / SqlMetaData 的 TVP 也适用。 - StuartLC

那么，我该如何更改代码以不传递参数？在我的情况下，我有5个TVP参数，每个参数都可能为空。我正在使用像这样的代码 BenefitsItemsLinkCollection itemsCollection = new BenefitsItemsLinkCollection(); itemsCollection.AddRange(items); itemsPar.Value = itemsCollection; itemsPar.TypeName = "siriusType_BenefitLinkedItems"; 对于空项目数组，我应该在这里更改什么？ - Naomi

同意异常信息。如果没有你的帮助，我甚至都不会尝试这个修复方法！ - Doug

Answer 2

我对“不传递参数”的声明有些困惑。对于Entity Framework ExecuteSqlCommandAsync()，解决方法如下：

  new SqlParameter("yourParameterName", SqlDbType.Structured)
  {
      Direction = ParameterDirection.Input,
      TypeName = "yourUdtType",
      Value = null
  };

这将把参数作为“默认”传递。

- Norbert Huurnink

---

不确定为什么对你有效，但这对我根本没有用。当使用这种方法时，我会得到与原始问题中完全相同的错误消息。 - Doug

Answer 3

没有传递值是可以的，但当我需要传递多个表值参数到存储过程时，这种方法就行不通了。我的解决办法是在查询字符串中指定一个值DEFAULT。例如：

string sqlQuery = "[dbo].[GetOrderData] @QueueId";

if (OrderIdList.Any())
{
    sqlQuery = sqlQuery + ", @OrderIdList";
}
else
{
    sqlQuery = sqlQuery + ", DEFAULT";
}

if (RegionIdList.Any())
{
    sqlQuery = sqlQuery + ", @RegionIdList";
}
else
{
    sqlQuery = sqlQuery + ", DEFAULT";
}

感谢http://www.sommarskog.se/arrays-in-sql-2008.html#Invoking提供了解决方案。

- Anthony

Answer 4

在传递一个空的 IEnumerable<int> 时，我会遇到错误，但是当我传递一个空的 List<int> 时，它可以正常工作。

- jausel

---

这对我没用。无论传递IEnumerable还是List，我都会得到相同的错误。 - Keith

Answer 5

混淆的原因在于当错误信息指出“使用空引用代替值”时，它指的是 C# 的 null，而不是 DBNull.Value。将参数设置为 C# 的 null 会传递参数的默认值，在表值参数的情况下，这个默认值是一个空表。将值设置为 DBNull.Value 会传递 SQL 的 NULL 值，这对于表值参数来说是无效的。

- Scott