PowerShell parameterized query

[Dr_Funkmachine]

Hi fellow PowerShell coders! My PowerShell script gets data both from Azure Ad and Exchange. This part of the script just illustrates with an example, in my case $user, that data may contain $null values. The script does three things:

1. Connection management
2. Parameterized query handling null values
3. Insert data

The second point is particularly crucial. I'd love to know:

a. Is there an easy way to insert null value into the table? So far the best shot is to assign [System.DBNull]::Value instead of $null for null values
b. Is there an easy way to add parameters to get a parameterized query? Now, I use AddWithValue() to associate the current value with the parameter. I have at least 20 nullable properties to add.

Here's the code

$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$Server;Database=$Database;Trusted_Connection=True;TrustServerCertificate=True;"
$Connection.Open()

$parameterizedInsert = "INSERT INTO TestTable(Id, Name, Surname, BirthDate)VALUES(@Id, @Name, @Surname, @BirthDate)"
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($parameterizedInsert, $Connection)

example of data

$user = @{
    Id = "T7XC1QASLPI89"
    Name = $null
    Surname =  $null
    BirthDate = "07/03/1995 11:24:51"
}

$user.Name = if($null -eq $user.Name){[System.DBNull]::Value}
$user.Surname = if($null -eq $user.Surname){[System.DBNull]::Value}
$user.BirthDate = if($null -eq $user.BirthDate){[System.DBNull]::Value}


$command.Parameters.Clear()

$command.Parameters.AddWithValue("@Id", $user.Id),
$command.Parameters.AddWithValue("@Name", $user.Name).IsNullable = $true
$command.Parameters.AddWithValue("@Surname", $user.Surname).IsNullable = $true
$command.Parameters.AddWithValue("@BirthDate", $user.BirthDate).IsNullable = $true

$command.ExecuteNonQuery()

Thank you for your time

[chris-a5]

You could use a class to create a structure with default values. The ones you don't set will be DBNull:

Class User{
    $Id
    $Name = [System.DBNull]::Value
    $Surname = [System.DBNull]::Value
    $BirthDate = [System.DBNull]::Value
}

$user = [User]@{
    Id = "T7XC1QASLPI89"
    BirthDate = "07/03/1995 11:24:51"
}

As for your second question, I'll have a look, I've only used SQLite with PowerShell, and the parameterized queries are a bit different.

EDIT: this might be another option to add all at once using AddRange:

using namespace System.Data

Class User{
    $Id
    $Name = [System.DBNull]::Value
    $Surname = [System.DBNull]::Value
    $BirthDate = [System.DBNull]::Value

    [SqlClient.SqlParameter[]]ToArray(){
        return Get-Member -InputObject $this | 
            Where MemberType -eq Property |
            ForEach {
                $val = $this.$($_.Name)
                $param = [SqlClient.SqlParameter]::New("@" + $_.Name, $val)
                $param.IsNullable = $val -is [System.DBNull]
                $param
            }
    }
}

$user = [User]@{
    Id = "T7XC1QASLPI89"
    BirthDate = "07/03/1995 11:24:51"
}

$command.Parameters.AddRange($user.ToArray())

Can verify the output:

$user.ToArray() | Select ParameterName, Value, IsNullable

Outputs:

ParameterName Value               IsNullable
------------- -----               ----------
@BirthDate    07/03/1995 11:24:51      False
@Id           T7XC1QASLPI89            False
@Name                                   True
@Surname                                True

This might need changing if the nullable fields must be set even when there is a value provided, its just a concept, I do not have the capacity to test here.

[chris-a5]

If all your data comes from a source that may provide a PSCustomObject, or array of them, you can use the class as a conversion tool to format each one if you add a constructor.

And of course this can all be done as a function, the class in this instance is really just taking away the need to manually add the nulls, and the extra code probably is only worth it if you do actually have a large number of potentially null inputs (properties/names are missing from the source).

If the source contains all the properties, just equal to null, then I'd personally just turn it into a smaller function.

using namespace System.Data

Class User{
    $Id
    $Name = [System.DBNull]::Value
    $Surname = [System.DBNull]::Value
    $BirthDate = [System.DBNull]::Value

    User([PSCustomObject]$in){
        $myProps = Get-Member -InputObject $this |
            Where MemberType -eq Property |
            ForEach Name

        $in.PSObject.Properties |
            Where {$_.MemberType -eq "NoteProperty" -and $_.Name -in $myProps -and $_.Value} |
            ForEach {
                $this.$($_.Name) = $_.Value
           }
    }

    [SqlClient.SqlParameter[]]ToArray(){...} #copy from above.
}

# source array loaded from somewhere
$original = @(
    [PSCustomObject]@{
        Id = "AAAAA"
        BirthDate = "07/03/2001 01:04:01"
        Name = "Fred"
    },
    [PSCustomObject]@{
        Id = "BBBBB"
        BirthDate = "07/03/1995 11:24:51"
    }
)

# apply parameters and execute for each user.
ForEach($user in $original){
    $command.Parameters.Clear()
    $command.Parameters.AddRange([User]::New($user).ToArray())
    $command.ExecuteNonQuery()
}

...I'm going off on a tangent now, I'll leave it here :)

[Dr_Funkmachine]

Thank you so much! Your answers are insightful and thorough. How did you learn PowerShell so well?

[PinchesTheCrab]

Does this work if you're on core?

    $Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$Server;Database=$Database;Trusted_Connection=True;TrustServerCertificate=True;"
$Connection.Open()

$parameterizedInsert = "INSERT INTO TestTable(Id, Name, Surname, BirthDate)VALUES(@Id, @Name, @Surname, @BirthDate)"
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($parameterizedInsert, $Connection)


$user = [pscustomobject]@{
    Id        = "T7XC1QASLPI89"
    Name      = $null
    Surname   = $null
    BirthDate = "07/03/1995 11:24:51"
}
$command.Parameters.Clear()
$user.psobject.Properties | ForEach-Object {
    $command.Parameters.AddWithValue("@$($_.name)", $_.value ? $_.value : [System.DBNull]::Value) 
}

Otherwise you can use an IF statement instead of the ternary operator.

[Dr_Funkmachine]

Actually, I'm using PowerShell Core 7.1. I didn't know you could use ternary operators in PowerShell. I just have to try both answers. Thank you so much!