来自 Powershell 的 SQL 插入:作为“空白"插入的空值； [英] SQL Insert from Powershell: null values inserted as "blank"

问题描述

我正在从 Powershell 脚本向表中插入行.我遍历一组 PSObject(财产包")并为每个对象插入一行.PSObject 上的每个 noteProperty 对应于表中的一列.其中大部分是字符串，少数是日期，还有一些是布尔值.

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.

但是，在 Powershell 中具有 $null 值的属性将作为空白"字符串插入表中，而不是 NULL.$null 日期值显示为 1900-01-01.除了指定了 NOT NULL 的几列之外，该表没有任何约束.如果我使用 NULL 作为值进行手动插入，它工作正常.

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.

我可以遍历每个对象以查找 $null 属性，然后将其替换为字符串 NULL，但这似乎是一种 hack 而不是正确"的方式.正如你在下面看到的，我已经为布尔值做了这个，因为它们在表中的对应列是位.我错误地认为 Powershell 中的 $null 会转换为 SQL 中的 NULL.如果该属性在 Powershell 中的值为 $null，是否有另一种方法可以使其正确地将 NULL 插入表中?或者，是否在插入之前将 $null 转换为字符串NULL"?

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()

推荐答案

$DBNull = [System.DBNull]::Value 

另请参阅此处的相关问题:Powershell 和 SQL 参数.如果为空字符串，则传递 DBNull

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

这篇关于来自 Powershell 的 SQL 插入:作为“空白"插入的空值；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！