Powershell: Feature Request: Handle DBNull as $null

[replicaJunction]

Summary of the new feature/enhancement

As a scripter who has recently been working with databases quite a bit, it's become a headache to check constantly for [DBNull]::Value. It has a tendency to "leak" outside of my database code, and I need to start checking for DbNull values all over the rest of my code as well.

PowerShell already handles both $null and an empty string in the same manner. Can it also treat [DBNull]::Value as $null for purposes of conditionals?

Here is a contrived example of what I mean,

$myVar = $null
if (-not $myVar) {
  # This will run
  'MyVar is null'
}

$myVar = ''
if (-not $myVar) {
  # This will also run
  'MyVar is null'
}

$myVar = [DbNull]::Value
if (-not $myVar) {
  # This will not run
  'MyVar is null'
}

The last case is what I'd like to see changed - I'd like that to be handled in the same way as the first two cases.

[mklement0]

+1, but I wouldn't frame the issue in terms of $null, but in terms of (possibly implicit) _to-Boolean conversion_.

# Explicit
PS> [bool] [DbNull]::Value
True # !! Should be $false

# Implicit
PS> if ([DbNull]::Value) { 'have value' }
have value # !! conditional was unexpectedly $true

Secondarily, the question is how _direct comparison with $null_ should be handled.

PS> $null -eq [DbNull]::Value
False   # !! Should probably be true.

Currently, there is only _one_ RHS value other than $null itself that yields $true with -eq, and that is [System.Management.Automation.Internal.AutomationNull]::Value, the "array-valued null" that commands that do not produce output technically return.

You could argue that [DbNull]::Value should similarly be considered a "subspecies" of $null, in which case the above should yield $true also.

[SteveL-MSFT]

@PowerShell/powershell-committee reviewed this, since this type comes from an assembly we already reference, we agree it makes sense as more DB users use PS we should support this

[daxian-dbw]

Just to add that currently $null -eq [NullString]::Value returns false. If we are supporting $null -eq [DbNull]::Value, [NullString]::Value should also be supported.

[vexx32]

Sounds good to me. This change can come in at the LanguagePrimitives.IsNull() method. We will also have to check the codebase to ensure we are using that method everywhere it is appropriate. I noticed during a recent cleanup PR that there were a few points where we _don't_ actually use the method and instead check manually for [AutomationNull]::Value which may complicate matters if we don't direct those to use the method as well. I believe I corrected the ones I saw, but there may be others still hiding somewhere. 😄

[daxian-dbw]

I noticed during a recent cleanup PR that there were a few points where we don't actually use the method and instead check manually for [AutomationNull]::Value

@vexx32 can you please point me to the regarding PR? [AutomationNull]::Value is handled specially in pipeline, null is passed through a pipeline, but [AutomationNull]::Value is not.

[vexx32]

True that. Let me see if I can find where it was, one moment.

EDIT: Oh, never mind. I was thinking I'd changed it, but I think I was thinking of #9735 where I was just doing formatting changes. Left those untouched as it was -- as you say -- specifically pipeline operations.

If I come across anything that might raise that question I'll ping you to make sure I don't break anything. 😄 Pretty sure I've seen a couple though.

[vexx32]

Separate comment for reasons, but this is the pattern I'm referring to @daxian-dbw. It seems a lot of cmdlets _don't utilise_ the LanguagePrimitives method for this. The pattern to search for in the code base that turns up a few matches (14, to be precise) is a regex of: == null \|\| .+? == AutomationNull.Value

A couple of entries:

• ConvertTo-Html.cs#692
• OutGridViewCommand.cs#177

[msftbot[bot]]

:tada:This issue was addressed in #9794, which has now been successfully released as v7.0.0-preview.2.:tada:

Handy links:

• Release Notes