SQL 在 PowerShell 中处理 System.DBNull
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22285149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dealing with System.DBNull in PowerShell
提问by Cookie Monster
EDIT: As of PowerShell 7 Preview 2, -not [System.DBNull]::Valueevaluates to $true, thanks to Joel Sallowvia pull request 9794
编辑：从 PowerShell 7 Preview 2 开始，-not [System.DBNull]::Value评估为$true，感谢Joel Sallow通过拉取请求 9794
Spending more time pulling SQL data in PowerShell. Running into issues with [System.DBNull]::Value and how PowerShell behaves with this during comparisons.
花费更多时间在 PowerShell 中提取 SQL 数据。遇到 [System.DBNull]::Value 问题以及 PowerShell 在比较期间如何处理此问题。
Here's an example of the behavior I see, along with workarounds
这是我看到的行为示例以及解决方法
#DBNull values don't evaluate like Null...
if([System.DBNull]::Value){"I would not expect this to display"}
# The text displays.
if([string][System.DBNull]::Value){"This won't display, but is not intuitive"}
# The text does not display.
#DBNull does not let you use certain comparison operators
10 -gt [System.DBNull]::Value
# Could not compare "10" to "". Error: "Cannot convert value "" to type "System.Int32". Error: "Object cannot be cast from DBNull to other types.""
[System.DBNull]::Value -gt 10
# Cannot compare "" because it is not IComparable.
#No real workaround. Must use test for null workaround in conjunction to avoid comparison altogether:
[string][System.DBNull]::Value -and [System.DBNull]::Value -gt 10
#Example scenario with a function that uses Invoke-Sqlcmd2 to pull data
Get-XXXXServer | Where-Object{$_.VCNumCPUs -gt 8}
#Error for every line where VCNumCPU has DBNull value
#workaround
Get-XXXXServer | Where-Object{[string]$_.VCNumCPUs -and $_.VCNumCPUs -gt 8}
Am I missing anything, or is there no 'simple' workaround for this that would let folks with little experience use PowerShell comparisons as expected?
我是否遗漏了任何东西，或者是否没有“简单”的解决方法可以让经验不足的人按预期使用 PowerShell 比较？
I submitted a suggestion on Connectand have a temporary workaround from Dave Wyattthat converts datarows to psobjects with dbnulls converted to nulls, but this adds a bit of overhead. Seems like something that should be handled under the covers, given the existing 'loose' behavior of PowerShell?
我在 Connect 上提交了一个建议，并从 Dave Wyatt那里获得了一个临时解决方法，将数据行转换为 psobjects，将 dbnulls 转换为 nulls，但这会增加一些开销。考虑到 PowerShell 现有的“松散”行为，似乎应该在幕后处理？
Any tips, or have I exhausted my options for now?
任何提示，或者我现在已经用尽了我的选择？
Thanks!
谢谢！
回答by Ansgar Wiechers
I think you're taking a wrong approach here. As documented, the DBNullclass represents a non-existing value, so comparisons like -gtor -ltdon't make any sense. A value that doesn't exist is neither greater nor less than any given value. The Valuefield has an Equals()method, though, which allows you to check if a value is or isn't DBNull:
我认为你在这里采取了错误的方法。正如所记录的那样，DBNull该类表示一个不存在的值，因此比较像-gt或-lt没有任何意义。不存在的值既不大于也不小于任何给定值。但是，该Value字段有一个Equals()方法，它允许您检查值是否为DBNull：
PS C:> ([DBNull]::Value).Equals(23)
False
PS C:> ([DBNull]::Value).Equals([DBNull]::Value)
True回答by Chrissy LeMaire
Simplest way is $var -isnot [DBNull].
最简单的方法是$var -isnot [DBNull]。
I've tested this in my own scripts and it works as expected.
我已经在我自己的脚本中对此进行了测试，并且它按预期工作。
回答by Bacon Bits
What I usually end up doing is this:
我通常最终做的是这样的：
[String]::IsNullOrWhiteSpace($Val.ToString())
Or this:
或这个：
[String]::IsNullOrEmpty($Val.ToString())
Or this:
或这个：
$Val.ToString() -eq [String]::Empty
This often works just fine since [System.DBNull]::Value.ToString()returns an empty string, so both [String]::IsNullOrWhiteSpace([System.DBNull]::Value)and [System.DBNull]::Value.ToString() -eq [String]::Emptyevaluate to True.
这往往工作得很好，因为[System.DBNull]::Value.ToString()返回一个空字符串，这样既[String]::IsNullOrWhiteSpace([System.DBNull]::Value)和[System.DBNull]::Value.ToString() -eq [String]::Empty评估为True。
Obviously, these are notlogically equivalent since your data may legitimately have empty strings, or may be a data type that doesn't make sense as an empty string (such as an integer). However, since you often want to treat DBNulls the exact same way as empty strings and whitespace-only strings, it can be useful if you know your data well enough.
显然，这些在逻辑上并不等价，因为您的数据可能合法地具有空字符串，或者可能是作为空字符串（例如整数）没有意义的数据类型。但是，由于您通常希望以与空字符串和仅包含空格的字符串完全相同的方式处理 DBNull，因此如果您对数据足够了解，它会很有用。
If you actually want to know if the value is a DBNull, of course, then use [DBNull]::Value.Equals($Value).
如果您确实想知道该值是否为 DBNull，当然，请使用[DBNull]::Value.Equals($Value).
回答by Vladimir Winer
if( %youfunctetc%.GetType().Name -eq 'DBNull')
{}
else {}
回答by Eric Weintraub
When dealing with SQL data in PS I include this function and call when needed:
在 PS 中处理 SQL 数据时，我包含此函数并在需要时调用：
function Check-IsNullWithSQLDBNullSupport ($var) {
if ($var -eq [System.DBNull]::Value -or $var -eq $null) {
return $true
} else {
return $false
}
}
Can be used like this:
可以这样使用：
if (Check-IsNullWithSQLDBNullSupport -var $VarToBeTested) {
write-output "Is Null"
}
回答by Sudhi
some-command | where FieldOfInterest -is DBNull Seems to work for me. DBNull is a 'type' and the -is operator is checking if the value on the left is of a given 'type'.
一些命令 | 其中 FieldOfInterest -is DBNull 似乎对我有用。DBNull 是一个“类型”，并且 -is 运算符正在检查左侧的值是否属于给定的“类型”。
You could also use the oppsite some-command | where FieldOfInterest -isnot DBNull
你也可以使用 oppsite some-command | 其中 FieldOfInterest -isnot DBNull
回答by Steven
It seems I only ever comment on old posts, but I think the link to discussion with Dave Wyatt is broken above, through re-googling I found it here.
似乎我只对旧帖子发表评论，但我认为与 Dave Wyatt 讨论的链接在上面已断开，通过重新谷歌搜索我在这里找到了它。
The code I'm working on at the moment is not performance sensitive, but I do need to compare the return data to reset properties on another differently typed target object.
我目前正在处理的代码对性能不敏感，但我确实需要比较返回数据以重置另一个不同类型目标对象的属性。
So typically convenient PowerShell like:
所以通常方便的 PowerShell 如：
If( $SrcObject.Property ) { $TargObject.Property = $SrcObject.Property }
This doesn't work with a [DBNull]
这不适用于 [DBNull]
Ordinarily I'd take the time to look/dev then use the fastest code regardless of the need or complexity but I gotta get a rev1 out ASAP. Before I even realized the [DBNull] issue I was flipping the objects to [PSCustomObject] using an easy | Select $Props
通常，无论需要或复杂性如何，我都会花时间查看/开发然后使用最快的代码，但我必须尽快获得 rev1。在我意识到 [DBNull] 问题之前，我使用一个简单的方法将对象翻转到 [PSCustomObject]| Select $Props
$Props was a typed out array of column names. But that doesn't change the type on the sub-property, so the comparison still fails!
$Props 是一个键入的列名数组。但这并没有改变子属性上的类型，所以比较仍然失败！
Given I was already down the path Dave was suggesting, I went a little more kludge.
鉴于我已经走在戴夫建议的道路上，我更加努力了。
$Props = ( $SQLData.Tables[0].Rows[0] | Get-Member -MemberType Properties ).Name
$Rows = $SQLData.Tables[0].Rows | Select $Props
ForEach( $RowObject in $Rows )
{
ForEach($Prop in $Props )
{
# Maybe: [String]::Empty below?
If( $RowObject.$Prop -is [DBNull] ) { $RowObject.$Prop = "" }
} #End Inner Loop.
} #End Outer Loop.
Note: This is a little psuedo, because the prod code has the rows buried in a dictionary, but it should be enough to convey the approach. Also, the above isn't fully tested because it was translated from working code.
注意：这有点伪，因为 prod 代码将行埋在字典中，但它应该足以传达方法。此外，上述内容并未经过全面测试，因为它是从工作代码翻译而来的。
I don't know why Get-Member doesn't return other properties like RowError, RowState etc... but this does work so long as you don't mind turning [DBNull]'s in to empty strings. And, Get-Member is a little more reusable, no typing out the props...
我不知道为什么 Get-Member 不返回其他属性，如 RowError、RowState 等……但只要您不介意将 [DBNull] 转换为空字符串，这确实有效。而且，Get-Member 的可重用性更高一点，无需输入道具...
Obviously this isn't much different than some of the casting mentioned earlier, but I'm probably not alone in wanting to park some complexity in helper functions, so "main" looks a little cleaner. Moreover, an empty string should satisfy most comparisons later on, especially considering the type conversion stuff going on the background.
显然，这与前面提到的一些转换没有太大不同，但我可能并不是唯一一个想要在辅助函数中放置一些复杂性的人，所以“main”看起来更简洁一些。此外，空字符串应该满足以后的大多数比较，特别是考虑到后台进行的类型转换。
I know this is a comment not a question, but If I've got anything wrong, please let me know. I did stumble on this while working an active project. Thanks!
我知道这是一个评论而不是一个问题，但如果我有任何错误，请告诉我。我在一个活跃的项目中偶然发现了这一点。谢谢！
