从PowerShell将NULL插入数据库

Question

我有通过API获取的数据，API给了我一个json文件。我使用PowerShell将其转换为PowerShell对象。

$allusers = Get-Content 'test.txt' | Out-String | ConvertFrom-Json

我从这个对象获取数据并将其插入Server数据库。我遇到的问题是，对于对象中不存在的键，我想将一个NULL插入到数据库中。

我在这里探索了其他解决方案，大多数建议使用[DBNull]::Value

如果数组中的特定用户的值不存在，则将变量设置为这样：

$oa_email_id = [DBNull]::Value
$oa_email = [DBNull]::Value
$oa_sms_id = [DBNull]::Value
$oa_sms_phone = [DBNull]::Value

但是对于空INT值，我得到一个插入的0，对于空的VARCHAR(max)值，插入一个空字符串。我是不是错过了什么或者做错了什么？

插入数据库：

$sql_insert_cmd = New-Object System.Data.SqlClient.SqlCommand
$sql_insert_cmd.CommandText = "
    INSERT INTO
        TEMP_OMNIALERT_CURRENT_SUBSCRIBERS
            (ID_NUM, OA_ID, OA_VALIDATED, OA_ACCOUNT_EXPIRATION_DATE, OA_OPTOUT_DATE, OA_EMAIL_ID,OA_EMAIL, OA_SMS_ID,OA_SMS_PHONE)
    VALUES
            ('"+    $id_num + "', '" +
                    $oa_id + "', '" +
                    $oa_validated + "', '" +
                    $oa_account_expiration_date + "', '" +
                    $oa_optout_date + "', '" +
                    $oa_email_id + "', '" +
                    $oa_email + "', '" +
                    $oa_sms_id + "', '" +
                    $oa_sms_phone + "')"

$sql_insert_cmd.Connection = $connection
$execute_insert = $sql_insert_cmd.ExecuteNonQuery()

表创建：

$sql_insert_cmd.CommandText = "
USE TmsEPly;
DROP TABLE IF EXISTS TEMP_OMNIALERT_CURRENT_SUBSCRIBERS;
CREATE TABLE
    TEMP_OMNIALERT_CURRENT_SUBSCRIBERS
    (
        SUBMISSION_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        ID_NUM VARCHAR(MAX) NOT NULL,
        OA_ID INT NOT NULL,
        OA_VALIDATED INT NOT NULL,
        OA_ACCOUNT_EXPIRATION_DATE VARCHAR(MAX) NOT NULL,
        OA_OPTOUT_DATE VARCHAR(MAX) NOT NULL,
        OA_EMAIL_ID INT NULL,
        OA_EMAIL VARCHAR(MAX) NULL,
        OA_SMS_ID INT NULL,
        OA_SMS_PHONE VARCHAR(MAX) NULL
    )"

Answer 1

我想我做了上面描述的事情，现在我的NULL值被插入。

我将查询和insert语句改为参数：

$sql_insert_cmd.CommandText = "
    INSERT INTO
        TEMP_OMNIALERT_CURRENT_SUBSCRIBERS
            (ID_NUM, OA_ID, OA_VALIDATED, OA_ACCOUNT_EXPIRATION_DATE, OA_OPTOUT_DATE, OA_EMAIL_ID, OA_EMAIL, OA_SMS_ID, OA_SMS_PHONE) 
    VALUES
            (@id_num, @oa_id, @oa_validated, @oa_account_expiration_date, @oa_optout_date, @oa_email_id, @oa_email, @oa_sms_id, @oa_sms_phone)"


$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@id_num",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_id",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_validated",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_account_expiration_date",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_optout_date",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_email_id",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_email",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_sms_id",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_sms_phone",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL

我使用以下方法插入数据：

$sql_insert_cmd.Parameters[0].Value = $user.username
$sql_insert_cmd.Parameters[1].Value = $user.id
$sql_insert_cmd.Parameters[2].Value = $user.validated
$sql_insert_cmd.Parameters[3].Value = $user.account_expiration_date
$sql_insert_cmd.Parameters[4].Value = $user.optout_date
$sql_insert_cmd.Parameters[5].Value = $oa_email_id
$sql_insert_cmd.Parameters[6].Value = $oa_email
$sql_insert_cmd.Parameters[7].Value = $oa_sms_id
$sql_insert_cmd.Parameters[8].Value = $oa_sms_phone

$sql_insert_cmd.ExecuteScalar()

Answer 2

与其构建字符串，不如考虑加载datatable，然后将其写入数据库中吗？

使用您的表创建，我将以下测试组合在一起，该测试成功地将数据加载到本地测试数据库的TEMP_OMNIALERT_CURRENT_SUBSCRIBERS中。定义和构建datatable的代码很大，但只需要发生一次，然后在遍历json数据时可以根据需要将行添加到其中。需要注意的一点是，我在datatable中为SUBMISSION_ID创建了一个列，并且从未填充过它--这样它就跳过了第一个标识列，而不是试图插入到其中而失败了。

###import the sqlserver module
ipmo sqlserver 

###your values
$oa_email_id = [DBNull]::Value
$oa_email = [DBNull]::Value
$oa_sms_id = [DBNull]::Value
$oa_sms_phone = [DBNull]::Value

###my temp values for the other columns
$ID_NUM = "a"
$OA_ID = 1 
$OA_VALIDATED = 1
$OA_ACCOUNT_EXPIRATION_DATE = get-date
$OA_OPTOUT_DATE = get-date

###define the datatable structure
$testTable = New-Object system.data.datatable "FileTable"
$col0 = New-Object system.Data.DataColumn SUBMISSION_ID,([int])
$col1 = New-Object system.Data.DataColumn ID_NUM,([string])
$col2 = New-Object system.Data.DataColumn OA_ID,([int])
$col3 = New-Object system.Data.DataColumn OA_VALIDATED,([int])
$col4 = New-Object system.Data.DataColumn OA_ACCOUNT_EXPIRATION_DATE,([string])
$col5 = New-Object system.Data.DataColumn OA_OPTOUT_DATE,([string])
$col6 = New-Object system.Data.DataColumn OA_EMAIL_ID,([int])
$col7 = New-Object system.Data.DataColumn OA_EMAIL,([string])
$col8 = New-Object system.Data.DataColumn OA_SMS_ID,([int])
$col9 = New-Object system.Data.DataColumn OA_SMS_PHONE,([string])

$testTable.columns.add($col0)
$testTable.columns.add($col1)
$testTable.columns.add($col2)
$testTable.columns.add($col3)
$testTable.columns.add($col4)
$testTable.columns.add($col5)
$testTable.columns.add($col6)
$testTable.columns.add($col7)
$testTable.columns.add($col8)
$testTable.columns.add($col9)

###add a new row and populate it
$row = $testTable.NewRow()
$row.ID_NUM = $ID_NUM
$row.OA_ID = $OA_ID
$row.OA_VALIDATED = $OA_VALIDATED
$row.OA_ACCOUNT_EXPIRATION_DATE = $OA_ACCOUNT_EXPIRATION_DATE
$row.OA_OPTOUT_DATE = $OA_OPTOUT_DATE
$row.OA_EMAIL_ID = $oa_email_id
$row.OA_EMAIL = $oa_email
$row.OA_SMS_ID = $oa_sms_id
$row.OA_SMS_PHONE = $oa_sms_phone
$testTable.Rows.Add($row)

###write the datatable to our target table
$testTable | write-sqltabledata -ServerInstance "localhost" -database "test" -schemaname "dbo" -tablename "TEMP_OMNIALERT_CURRENT_SUBSCRIBERS"