title: SQL Insert from Powershell: null values inserted as "blank"
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Here's the code I'm using:</p>
  
  <pre><code>$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()
  </code></pre>