Dealing with System.Dbnull in Powershell

Powershell and SQL parameters. If empty string, pass DBNull

In PowerShell, you can treat null/empty strings as a boolean.

$x = $null
if ($x) { 'this wont print' }

$x = ""
if ($x) { 'this wont print' }

$x = "blah"
if ($x) { 'this will' }

So.... having said that you can do:

$Parameter.Value = $(if ($x) { $x } else { [DBNull]::Value })

But I'd much rather wrap this up in a function like:

function CatchNull([String]$x) {
if ($x) { $x } else { [DBNull]::Value }
}

Update or Insert SQL with Powershell NULL values

Ok I think i got it now :)

$users=Import-csv C:\temp\users.csv

$SQLServer = "localhost\DB"
$SQLDBName = "UDB"
$SQLTableName="dbo.users"

$SQLConn = New-Object System.Data.SQLClient.SQLConnection
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand

$SQLConn.ConnectionString = "Server=$SQLServer;Database=$SQLDBName; Integrated Security= True"
$SQLConn.Open()

$SQLCmd = New-Object System.Data.SqlClient.SqlCommand
$SQLCmd.Connection = $SQLConn

foreach ($user in $users){
$SqlCmd.Parameters.Clear()

$SQLCmd.CommandText = "
IF EXISTS (SELECT * FROM $SQLTableName WHERE id = @id)
BEGIN
UPDATE
$SQLTableName
SET
first_name = @firstname,
last_name = @lastname,
middle_name = @middlename,
termination_date = @terminationdate,
WHERE
id = @id
END
ELSE
BEGIN
INSERT INTO
$SQLTableName(
id,
first_name,
last_name,
middle_name,
termination_date)
VALUES
(@id,
@firstname,
@lastname,
@middlename,
@terminationdate)
END"

$SQLCmd.Parameters.Add("@id", [System.Data.SqlDbType]::Int).Value = $user.id
$SQLCmd.Parameters.Add("@firstname", [System.Data.SqlDbType]::NVarChar).Value = $user.fname+[DBNull]::Value
$SQLCmd.Parameters.Add("@lastname", [System.Data.SqlDbType]::NVarChar).Value = $user.lastname+[DBNull]::Value
$SQLCmd.Parameters.Add("@middlename", [System.Data.SqlDbType]::NVarChar).Value = $user.middlename+[DBNull]::Value
$SQLCmd.Parameters.Add("@terminationdate", [System.Data.SqlDbType]::Date).Value = $user.terminationdate+[DBNull]::Value

$SQLCmd.ExecuteNonQuery()

Inserting NULL Into Database from PowerShell

I think I did what Mike Shepard described above and my NULL values now get inserted.

I changed my query and insert statements to parameters instead:

$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

I insert the data using :

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

Unable to cast object of type 'System.DBNull' to type 'System.Type'

In case anyone reading this is looking for other conclusions, I had exact same issue of PowerShell same error and it grinding down to a single geo column. I was simply writing some code to grab the table and export once a week for easy user reference. The Add-Type solution didn't change my outcome. What worked for me was, in the SQL cmd, using CAST(mygeocol AS varchar(100)). Conveniently, in SQL & PS, that brought it back to a format like "POINT (-90.1234 30.1234)" and worked great. Hope it helps someone!



Related Topics



Leave a reply



Submit