Powershell 和 SQL 参数。如果为空字符串，则传递 DBNull

Powershell and SQL parameters. If empty string, pass DBNull

提问by Tommy Jakobsen

I got this parameter:

我得到了这个参数：

$objDbCmd.Parameters.Add("@telephone", [System.Data.SqlDbType]::VarChar, 18) | Out-Null;
$objDbCmd.Parameters["@telephone"].Value = $objUser.Telephone;

Where the string $objUser.Telephonecan be empty. If it's empty, how can I convert it to [DBNull]::Value?

字符串$objUser.Telephone可以为空的地方。如果它是空的，我该如何将其转换为[DBNull]::Value？

I tried:

我试过：

if ([string]:IsNullOrEmpty($objUser.Telephone)) { $objUser.Telephone = [DBNull]::Value };

But that gives me the error:

但这给了我错误：

Exception calling "ExecuteNonQuery" with "0" argument(s): "Failed to convert parameter value from a ResultPropertyValueCollection to a String."

使用“0”参数调用“ExecuteNonQuery”的异常：“无法将参数值从 ResultPropertyValueCollection 转换为字符串。”

And if I convert it to a string, it inserts an empty string "", and not DBNull.

如果我将其转换为字符串，它会插入一个空字符串""，而不是DBNull.

How can this be accomplished?

如何做到这一点？

Thanks.

谢谢。

回答by Josh

In PowerShell, you can treat null/empty strings as a boolean.

在 PowerShell 中，您可以将空/空字符串视为布尔值。

$x = $null
if ($x) { 'this wont print' }

$x = ""
if ($x) { 'this wont print' }

$x = "blah"
if ($x) { 'this will' }

So.... having said that you can do:

所以......话虽如此，你可以这样做：

$Parameter.Value = $(if ($x) { $x } else { [DBNull]::Value })

But I'd much rather wrap this up in a function like:

但我更愿意将其封装在一个函数中，例如：

function CatchNull([String]$x) {
   if ($x) { $x } else { [DBNull]::Value }
}

回答by Jon

I don't know about powershell, but in C# I would do something like this:

我不知道 powershell，但在 C# 中，我会做这样的事情：

if ([string]::IsNullOrEmpty($objUser.Telephone))
{
 $objDbCmd.Parameters["@telephone"].Value = [DBNull]::Value;
}
else
{
 $objDbCmd.Parameters["@telephone"].Value = $objUser.Telephone;
}

回答by suhakar

Always append +"" at the end of db values...

始终在 db 值的末尾附加 +""...

$command.Parameters["@EmployeeType"].Value= $ADResult.EmployeeType + ""

$command.Parameters["@EmployeeType"].Value= $ADResult.EmployeeType + ""

回答by mklement0

Many years later, let me clarify:

多年以后，让我澄清一下：

Josh's answershows a helpful simplification for testing strings for emptiness (relying on PowerShell's implicitto-Boolean conversion^[1]), but it is unrelated to Tommy's (the OP's) problem.

Josh 的回答显示了测试字符串是否为空的有用简化（依赖于 PowerShell 的隐式到布尔转换^[1]），但它与 Tommy（OP）的问题无关。

Instead, the error message

相反，错误消息

"Failed to convert parameter value from a ResultPropertyValueCollection to a String."

“无法将参数值从 ResultPropertyValueCollection 转换为字符串。”

implies that it is the non-null case that caused the problem,because $objDbCmd.Parameters["@telephone"].Valueexpects either a stringvalue or [DBNull]::Value, whereas $objUser.Telephoneis of type [ResultPropertyValueCollection], i.e. a collectionof values.

意味着它是导致问题的非空情况，因为$objDbCmd.Parameters["@telephone"].Value需要一个字符串值或[DBNull]::Value，而$objUser.Telephone是类型[ResultPropertyValueCollection]，即值的集合。

Thus, in the non-null case, a stringvalue must be assigned, which must be derived from the collection; one option is to take the firstcollection element's value, another would be to join all values with a separator to form a single string, using, e.g., [string]::Join(';', $objUser.Telephone)or, if joining the elements with spacesis acceptable (not a good idea with multiple phone numbers), simply with "$($objUser.Telephone)".^[2]

因此，在非空情况下，必须分配一个字符串值，该值必须从集合中派生；一个选项是取第一个集合元素的值，另一个选项是使用分隔符连接所有值以形成单个字符串，例如，[string]::Join(';', $objUser.Telephone)或者，如果使用空格连接元素是可以接受的（对于多个电话号码不是一个好主意)，只需使用"$($objUser.Telephone)". ^[2]

Detecting an empty collection via [string]:IsNullOrEmpty()actually worked, despite the type mismatch, due to how PowerShell implicitly stringifies collections when passing a value to a [string]typed method parameter.^[2]

[string]:IsNullOrEmpty()尽管类型不匹配，但由于 PowerShell 在将值传递给[string]类型化方法参数时隐式地对集合进行字符串化，因此通过实际工作检测空集合。^[2]

Similarly, using implicitto-Boolean conversion works as expected with collections too: an empty collection evaluates to $false, a non-empty one to $true(as long as there are either at least two elements or the only element by itself would be considered $true^[1])

类似地，使用隐式到布尔转换对集合也按预期工作：空集合计算为$false，非空集合计算为$true（只要至少有两个元素或唯一的元素本身将被视为$true^[1])

Therefore, one solution is to use the firsttelephone number entry:

因此，一种解决方案是使用第一个电话号码条目：

$objDbCmd.Parameters["@telephone"].Value = if ($objUser.Telephone) {
    $objUser.Telephone[0].ToString()  # use first entry
  } else {
    [DBNull]::Value
  }

^{Note: If $objUser.Telephone[0]directly returns a [string], you can omit the .ToString()call.}

^{注意：如果$objUser.Telephone[0]直接返回 a [string]，可以省略.ToString()调用。}

In PowerShell v7+you can alternatively shorten the statement via a ternary conditional:

在PowerShell v7+ 中，您也可以通过三元条件来缩短语句：

$objDbCmd.Parameters["@telephone"].Value =
  $objUser.Telephone ? $objUser.Telephone[0].ToString() : [DBNull]::Value

---

---

^{[1] For a comprehensive summary of PowerShell's automatic to-Boolean conversions, see the bottom section of this answer.}

^{[1] 有关 PowerShell 自动到布尔转换的全面摘要，请参阅此答案的底部部分。}

^{[2] When implicitlyconverting a collection to a string, PowerShell joins the stringified elements of a collection with a single space as the separator by default; you can override the separator with the automatic $OFSvariable, but that is rarely done in practice; e.g., array 'foo', 'bar'is converted to 'foo bar'; note that this conversion does notapply when you call the collection's .ToString()method explicitly, but it does apply inside expandable (interpolating) strings, e.g., "$array".}

^{[2] PowerShell 将集合隐式转换为字符串时，默认以单个空格作为分隔符连接集合的字符串化元素；您可以使用自动$OFS变量覆盖分隔符，但在实践中很少这样做；例如，数组'foo', 'bar'被转换为'foo bar'; 注意，此转换也不会当你调用集合的应用.ToString()方法明确，但它确实适用内扩张（插值）的字符串，例如"$array"。}