Dealing with DBNull

PowerShell Help
2

It’s a little bit of a hack, but casting the value to [string] seems to work without adding much code. DBNull and $null both become the empty string, which evaluates to $false:

Get-XXXXServer | Where-Object { [string]$_.VCServerContact }

That’s essentially the same thing that you’re doing with the -notlike “” option; PowerShell’s casting the value to a string for you before evaluating the notlike operator.

As for outputting custom objects and simultaneously scrubbing out DBNull (with a pure PowerShell implementation), here’s the fastest option I’ve found, based on some quick tests (snip of modified Invoke-SQLCMD2):

    switch ($As) 
    { 
        'DataSet'     { Write-Output ($ds) } 
        'DataTable'   { Write-Output ($ds.Tables) } 
        'DataRow'     { Write-Output ($ds.Tables[0]) }
        'PSObject' {
            $properties = New-Object System.Collections.Specialized.OrderedDictionary

            foreach ($row in $ds.Tables[0].Rows)
            {
                $properties.Clear()
                   
                foreach ($property in $row.PSAdapted.PSObject.Properties)
                {
                    if ($property.Value -is [System.DBNull])
                    {
                        $value = $null
                    }
                    else
                    {
                        $value = $property.Value
                    }

                    $properties[$property.Name] = $value
                }

                [pscustomobject] $properties
            }
        }
        'SingleValue' { Write-Output ($ds.Tables[0] | Select-Object -Expand $ds.Tables[0].Columns[0].ColumnName ) }
    }

This seems to take about 10x as long as the ‘DataRow’ option. Note that the [pscustomobject] syntax requires at least PowerShell 3.0, but going with the 2.0-compatible syntax of New-Object psobject -Property $properties more than doubled the execution time in my tests. Likewise, using Write-Output also added a pretty significant amount of time as compared to just letting PowerShell do the pipeline output itself.

There’s quite a bit of overhead involved in simply accessing members of objects in PowerShell. Taking the logic from that block and moving it out into a C# function is much, much faster:

$cSharp = @'
    using System;
    using System.Data;
    using System.Management.Automation;

    public class DBNullScrubber
    {
        public static PSObject DataRowToPSObject(DataRow row)
        {
            PSObject psObject = new PSObject();

            if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
            {
                foreach (DataColumn column in row.Table.Columns)
                {
                    Object value = null;
                    if (!row.IsNull(column))
                    {
                        value = row[column];
                    }

                    psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
                }
            }

            return psObject;
        }
    }
'@

Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data','System.Xml'

# Snip from Invoke-SqlCmd2

    switch ($As) 
    { 
        'DataSet'     { Write-Output ($ds) } 
        'DataTable'   { Write-Output ($ds.Tables) } 
        'DataRow'     { Write-Output ($ds.Tables[0]) }
        'PSObject' {
            foreach ($row in $ds.Tables[0].Rows)
            {
                [DBNullScrubber]::DataRowToPSObject($row)
            }
        }
        'SingleValue' { Write-Output ($ds.Tables[0] | Select-Object -Expand $ds.Tables[0].Columns[0].ColumnName ) }
    }