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

Answer 1

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