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?

Petronius

I hit this 'special case' when doing some coding to work with sql db. My solution was simply to check for it if ($val -ne [DBNull]::Value){} but that would not solve the problem with comparison. I upvoted your connect entry. – Oneirocritic

Answer 1

Simplest way is $var -isnot [DBNull].

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

Faun

This is a straightforward way to do that comparison, asking for the class type instead of the value. $var -is [DBNull] or $var -isnot [DBNull] depending of what do you want. – Superscribe

Thanks. This worked: $element contains the resultset from the database. $est_end_date=$element['Est_End_Date'] if ($est_end_date -isnot [DBNull]) { $est_end_date=$element['Est_End_Date']+' 00:00:00Z'} – Ludwigshafen

I tried comparing $est_end_date -eq $null and it failed throwing the error DBNull was missing. So thanks for fixing my issue [DBNull] worked – Ludwigshafen

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

Perusse

Ansgar - I completely agree from a purist standpoint! The issue is that PowerShell is not a language for purists. It takes many actions under the covers to provide behavior that a sysadmin would expect. Should Joe-Schmoe admin be expected to dive into the MSDN documentation when "", 0, $null and data from other types compare as expected, but data from SQL does not? I've been dealing with this until now because I was the audience. Next week I am demoing a mini-DSL that pulls from SQL - audience won't be thinking 'MSDN explains it perfectly!' they will be thinking 'why is this so complicated!' – Petronius

@CookieMonster Null isn't zero, particularly when it comes to databases. Get used to the fact. – Perusse

Agreed! DBNull isn't Null either. Just looking for consistency in PowerShell's loose 'let-me-help-you-with-that' behavior. Examples: trcm { if(0){"Y"} } -N TypeConversion -PSH; trcm { if($null){"Y"} } -N TypeConversion -PSH; trcm { if(""){"Y"} } -N TypeConversion -PSH; trcm { if([System.DBNull]::Value){"Y"} } -N TypeConversion -PSH; trcm { if("" -gt 5){"Y"} } -N TypeConversion -PSH; trcm { if($null -gt 5){"Y"} } -N TypeConversion -PSH; trcm { if([System.DBNull]::Value -gt 5){"Y"} } -N TypeConversion -PSH; Anyhow! Your post was helpful, voting it up - Cheers! – Petronius

I agree with @AnsgarWiechers, you need to write the code that works, so if you need to write if ([DBNull]::Value).Equals($var)) { "Y" } then it's not a purist or non purist approach is a work or doesn't work approach... – Varhol

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

Cannabis

Answer 4

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

Phytophagous

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

Rexer

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

Cq

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!

Roop