SQL Insert from Powershell: null values inserted as "blank"

Question

I am inserting rows into a table from a Powershell script. I loop through a collection of PSObjects ("property bags") and inserts a row for each one. Each noteProperty on the PSObject corresponds to a column in the table. Most of them are strings, a few are dates, and a few are booleans.

Properties that have a $null value in Powershell are being inserted as "blank" strings and not NULL in the table, however. $null date values show up as 1900-01-01. The table does not have any constraints beyond a couple of columns having NOT NULL specified. If I do a manual insert with NULL as a value, it works fine.

I could loop over each object looking for $null properties and then replace it with the string NULL, but that seems like a hack instead of the "right" way. As you can see below, I'm already doing this for the booleans, as their corresponding columns in the table are bits. I erroneously assumed that $null in Powershell would translate to NULL in SQL. Is there another way I can make it correctly insert NULL into the table if the property has a value in Powershell of $null? Or, is converting $null to the string "NULL" before inserting the best way to do it?

Here's the code I'm using:

$listOfLunsReport = Import-Clixml e:\powershell\myObjectCollection.xml

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=SQLSERVER\MYDATABASE;Initial Catalog=my_report;Integrated Security=SSPI;"
$conn.open()

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn

foreach ($lunObject in $listOfLunsReport)
{
    if ($lunObject.SnapsConfigured -eq $true)
    {
        $snapBit = 1
    }
    else
    {
        $snapBit = 0
    }
    if ($lunObject.isDatastore -eq $true)
    {
        $dsBit = 1
    }
    else
    {
        $dsBit = 0
    }
    $cmd.commandtext = "INSERT INTO listOfLuns (Array,lunNumber,LunUID,CapacityInMB,StorageGroup,`
    SnapsConfigured,SnapName,SnapUID,NumberOfSnaps,LatestSnap,VM,PhysicalServer,isDatastore,`
    DatastoreName,ReportDate,ArrayModel,ArrayLocation) VALUES ('{0}','{1}','{2}','{3}','{4}',`
    '{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')" -f `
    $lunObject.Array,$lunObject.lunNumber,$lunObject.LunUID,[int]$lunObject.CapacityInMB,`
    $lunObject.StorageGroup,$snapBit,$lunObject.SnapName,$lunObject.SnapUID,`
    $lunObject.NumberOfSnaps,$lunObject.LatestSnap,$lunObject.VM,$lunObject.PhysicalServer,`
    $dsBit,$lunObject.DatastoreName,$lunObject.ReportDate,$lunObject.ArrayModel,$lunObject.ArrayLocation
    $cmd.executenonquery()
}

$conn.close()

Answer 1

$DBNull = [System.DBNull]::Value 

Also see related question here: Powershell and SQL parameters. If empty string, pass DBNull

Answer 2

I've changed all blank values with PS $null and Write-SQLTableData ran successfully.

Foreach ($incident in $incidents)
    {
    if ($incident.closed_at -eq '')
        {
        $incident.closed_at = $null
        }
    }

$incident | Select "number","opened_at","short_description","closed_at","incident_state","state","u_operation_mode"|`
Write-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName dbo -TableName $Table -ConnectionTimeout 600 –Force

Answer 3

First things first: Rather than "baking" variable elements of a SQL command into the command string, it is more robust and secure to use a parameterized query instead, or, as Aaron Bertrand suggests, a stored procedure; that way, the variable elements are passed as parameters.

---

Your template string assumes that all values to be inserted are strings, given that you've enclosed the placeholders in '...' (e.g., '{0}').

Given that $null passed to -f, the format operator, is converted to the empty string^[1], you'll end up with literal '' parameter values in the command string, whereas what you truly need is verbatim NULL.

You can define a helper function:

function Expand-SqlQueryTemplate ($template, $values) {
  # Transform the values to SQL syntax.
  $transformedValues = $values | ForEach-Object {
    if ($null -eq $_) {
      'NULL'
    } elseif ($_ -is [string] -or $_ -is [datetime]) { # string or date
      # Enclose in '...', escaping embedded ' as ''
      "'{0}'" -f ($_ -replace "'", "''")
    } else { # other types, such as numbers
      # Use them as-is, though you may need to tweak this,
      # depending on what types you pass.
      $_
    }
  }
  # Expand the template string with the transformed values
  # and output the result.
  $template -f $transformedValues
}

You can then invoke it as follows (I'm using a simplified example):

# Define the command-string template.
$template = 'INSERT INTO SomeTable (Name, Age, Other) VALUES ({0}, {1}, {2})'

# Invoke the function, passing the values to substitute as an array.
Expand-SqlQueryTemplate $template  'J. Doe', 42, $null

The above yields the following string:

INSERT INTO SomeTable (Name, Age, Other) VALUES ('J. Doe', 42, NULL)

---

^{[1] The same applies to PowerShell's expandable strings ("..."); e.g., "[$null]" yields verbatim [].}