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

Simplest way is $var -isnot [DBNull].

I've tested this in my own scripts and it works as expected.

Answer 2

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

Answer 3

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]::Empty evaluate to True.

Obviously, these are not logically 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.

If you actually want to know if the value is a DBNull, of course, then use [DBNull]::Value.Equals($Value).

Answer 4

if( %youfunctetc%.GetType().Name -eq 'DBNull')
{}
else {}

Answer 5

When dealing with SQL data in PS I include this function and call when needed:

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"
}

Answer 6

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'.

You could also use the oppsite some-command | where FieldOfInterest -isnot DBNull

Answer 7

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.

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:

If( $SrcObject.Property ) { $TargObject.Property = $SrcObject.Property }

This doesn't work with a [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

$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!

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.

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...

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.

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!