sql - Dealing with System.DBNull in PowerShell

Question

EDIT: As of PowerShell 7 Preview 2, -not [System.DBNull]::Value evaluates to $true, thanks to Joel Sallow via pull request 9794

Spending more time pulling SQL data in PowerShell. Running into issues with [System.DBNull]::Value and how PowerShell behaves with this during comparisons.

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?

I submitted a suggestion on Connect and have a temporary workaround from Dave Wyatt that 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?

Any tips, or have I exhausted my options for now?

Answer 1

I think you're taking a wrong approach here. As documented, the DBNull class represents a non-existing value, so comparisons like -gt or -lt don't make any sense. A value that doesn't exist is neither greater nor less than any given value. The Value field has an Equals() method, though, which allows you to check if a value is or isn't DBNull:

PS C:> ([DBNull]::Value).Equals(23)
False
PS C:> ([DBNull]::Value).Equals([DBNull]::Value)
True