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
Differencebetween a Primary Key and a Unique Constraint
Oracle SQL Where Clause to Find Date Records Older Than 30 Days
Postgresql Window Function: Partition by Comparison
Join Two Spreadsheets on a Common Column in Excel or Openoffice
Regular Expressions Inside SQL Server
Count Rows Per Hour in SQL Server with Full Date-Time Value as Result
Are There Any Free Tools to Generate 'Insert Into' Scripts in Ms SQL Server
How to See Generated SQL from a Linq Query
MySQL Question - How to Handle Multiple Types of Users - One Table or Multiple
Will a SQL Server Job Skip a Scheduled Run If It Is Already Running
What Is the Data Type for Unix_Timestamp (Mysql)
How to Catch a Query Exception in Laravel to See If It Fails
SQL Update Statement to Switch Two Values in Two Rows
How to Concatenate Strings in Entity Framework Query
Update Multiple Tables in SQL Server Using Inner Join
How to Take Sum of Column with Same Id in SQL