Update or Insert SQL with Powershell NULL values

Question

Trying to figure out how to add a MSSQL-Null value if the value isn't present.

This works so far, it will insert new rows and if match it will update the columns. But if for example termniationdate isn't specified, instead of assigning a null value in the database it will write 1900-01-01 and text fields will be blank instead of null.

Tried to add +[DBNull]::Value but made no change. Kind of run out of ideas. :)

USERS.CSV:

id,firstname,lastname,middlename,terminationdate
1,A,B,,
2,C,D,E,2019-10-12
3,F,G,,2019-11-12
4,H,I,J,
5,K,,,

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

#declare the variables
$id = $user.id
$first_name = $user.fname
$last_name = $user.lastname
$middle_name = $user.middlename
$termination_date = $user.terminationdate

$SQLCmd.CommandText = "
IF EXISTS (SELECT * FROM $SQLTableName WHERE id = '$id')
BEGIN
    UPDATE
        $SQLTableName
    SET
    first_name = '$firstname',
    last_name = '$lastname',
    middle_name = '$middlename',
    date_termination = '$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.ExecuteNonQuery()

Don't construct queries by text concatenation -- I know that's particularly convenient in PowerShell, but it's asking for trouble. Use properly parameterized queries -- `SET first_name = @firstname`, `$SQLCmd.Parameters.Add("@firstname", "NVarChar", 100).Value = &{ if (-not $user.fname) { [DBNull]::Value } else { $user.fname } }`. This assumes you want to convert all empty strings to `NULL`, but the logic is easily customized. — Jeroen Mostert, Sep 23 '19 at 13:38

score 0 · Answer 1 · answered Sep 23 '19 at 12:28

You could try nulling out the CSV object values before they are passed to the SQL command.

foreach ($user in $users){

  switch ('id','firstname','lastname','middlename','terminationdate') {
    {[string]::IsNullOrEmpty($user.$_)} {
        Set-Variable $_ -Value $null -Force
        Break
    }
    default {
        Set-Variable $_ -Value $user.$_ -Force
    }
  } # End switch

  # SQL Stuff
} # End foreach loop

This will create variables ($id,$firstname,$lastname,$middlename,$terminationdate) in the loop that match the property names. Then just simply add your SQL code after the switch block.

score 0 · Accepted Answer · answered Sep 23 '19 at 14:59

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

Update or Insert SQL with Powershell NULL values

2 Answers2