Working with System.DBNull in PowerShell

Question

UPDATE: Starting with PowerShell 7 Preview 2, -not [System.DBNull]::Value evaluates to $true , thanks to Joel Sallow through unload request 9794

Spend more time retrieving SQL data in PowerShell. Problems with [System.DBNull] :: Value and how PowerShell behaves with this during comparisons.

Here is an example of the behavior that 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 something or not a “simple” workaround allowing people with little experience to use PowerShell comparisons, as expected?

I sent a Connect proposal and I have a temporary workaround from Dave Wyatt that converts datarow to psobject objects and dbnulls converts to zeros, but this adds a bit of overhead , It seems like something needs to be handled undercover , considering the existing "free" PowerShell behavior?

Any advice or have I exhausted my options?

Thanks!

Answer 1

I think you're wrong here. As documented , the DBNull class represents a nonexistent value, so comparisons such as -gt or -lt make no sense. A value that does not exist is neither greater nor less than any given value. The Value field has an Equals() method, although it allows you to check whether this value is DBNull or not:

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

Answer 2

The easiest way is $var -isnot [DBNull] .

I checked this in my own scripts and it works as expected

Answer 3

What I usually do is:

 [String]::IsNullOrWhiteSpace($Val.ToString()) 

Or that:

 [String]::IsNullOrEmpty($Val.ToString()) 

Or that:

 $Val.ToString() -eq [String]::Empty 

This often works just fine, because [System.DBNull]::Value.ToString() returns an empty string, so both [String]::IsNullOrWhiteSpace([System.DBNull]::Value) and [System.DBNull]::Value.ToString() -eq [String]::Empty evaluates to True.

Obviously, they are not logically equivalent, since your data may have legal empty strings or may be a data type that does not make sense as an empty string (for example, an integer). However, since you often want to treat DBNulls in the same way as empty lines and spaces only for spaces, this can be useful if you know your data well.

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

Answer 4

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

Answer 5

When working with SQL data in PS, I enable this function and call if necessary:

 function Check-IsNullWithSQLDBNullSupport ($var) { if ($var -eq [System.DBNull]::Value -or $var -eq $null) { return $true } else { return $false } } 

It can be used as follows:

 if (Check-IsNullWithSQLDBNullSupport -var $VarToBeTested) { write-output "Is Null" } 

Answer 6

some team | where FieldOfInterest -is DBNull seems to work for me. DBNull is a type, and the -is operator checks to see if the type on the left has a value.

You can also use the opposite of some command | where FieldOfInterest -is is not DBNull