PowerShell: There are null values and then there are SQL null values

I was reading some data out of a SQL Server table, and wanted to do an operation if one of the string values returned, MAC, was either blank or null. I was using the following code:

$NewServerName = "SomeServerName"
$DBServerName = "RCMSQL01"
$SQLConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Server=$DBServerName;Database=Build;Integrated Security=SSPI"
$SQLConnection.Open()
$SQLCommand = $SQLConnection.CreateCommand()
$SQLCommand.CommandText = "SELECT Host,OU,MAC FROM [Build].[dbo].[Main] WHERE ServerName like '$NewServerName'"
$SQLReader = $SQLCommand.ExecuteReader()
if($SQLReader.Read()){
    $HyperVHost = $SQLReader["Host"]
    $OU = $SQLReader["OU"]
    $MAC = $SQLReader["MAC"]
    $SQLReader.Close()
} else {
    Write-Host "VM record missing in Build DB"
}

Due to the nature of the SQL database, the values in the $MAC column might be any of null (the SQL Server default), blank (i.e. some text had been added but removed), or “some actual text”.

If the MAC column was showing as Null, the following code did not work as expected:

if($MAC -eq $null){
    Write-Host "MAC is null"
}

It seems as though this is because the value being returned is actually of the type System.DBNull rather than being an actual PowerShell null value ($null). You can see this by piping the variable into Get-Member.

There are several ways to work with this:

1) Test the $MAC variable against the “special” DBNull variable

if($MAC -eq [System.DBNull]::Value)
    Write-Host "MAC is null"
}

2) Ensure that the variable is created as a string, then test for it being blank rather than null. At the top of the script:

[string]$MAC = ""

Then to test, once the value has been retrieved from SQL Server:

if($MAC -eq ""){
    Write-Host "MAC is blank (might actually be Null in SQL Server)"
}

3) Convert the variable into a string, then test for it being blank

$MACString = $MAC.ToString()
if($MACString -eq ""){
    Write-Host "MAC is blank (might actually be Null in SQL Server)"
}
This entry was posted in PowerShell, Scripting and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s