For a project involving Azure Security I needed to store fairly large amounts of data in an Azure PaaS database using PowerShell.
If a row already exists, I want to do an UPDATE command, otherwise an INSERT command, also known as an UPSERT in SQL.
It should also use parameters to avoid issues with quotes in fields, and should convert PowerShell null’s/empty objects to the SQL equivalent.
The following function is what resulted, it only supports a single WHERE clause, but should be easy to enhance for those looking to improve 🙂
function invoke-sqlUpsert{
Param(
[String]$tableName,
[Array]$values, # example: @(@{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar})
[PSObject]$primaryKey, # example: @{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar}
[System.Data.Common.DbConnection]$sqlConn
)
$sqlQuery = "BEGIN TRANSACTION;
UPDATE $tableName
SET "
for($i = 0;$i -lt $values.Count;$i++){
$sqlQuery = "$sqlQuery$($values[$i].column)=@$($values[$i].column)"
if($i -lt $values.Count-1){
$sqlQuery = "$sqlQuery,"
}
}
$sqlQuery = "$($sqlQuery)
WHERE $($primaryKey.column) = @$($primaryKey.column);
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO $tableName ($($primaryKey.column),"
for($i = 0;$i -lt $values.Count;$i++){
$sqlQuery = "$sqlQuery$($values[$i].column)"
if($i -lt $values.Count-1){
$sqlQuery = "$sqlQuery,"
}
}
$sqlQuery = "$($sqlQuery)
)
VALUES (@$($primaryKey.column),"
for($i = 0;$i -lt $values.Count;$i++){
$sqlQuery = "$($sqlQuery)@$($values[$i].column)"
if($i -lt $values.Count-1){
$sqlQuery = "$sqlQuery,"
}
}
$sqlQuery = "$($sqlQuery));
END
COMMIT TRANSACTION;"
$sqlCmd=new-object system.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn)
$sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($primaryKey.column)",$primaryKey.dataType))) | OUT-NULL
$sqlCmd.Parameters[0].Value = $primaryKey.value
for($i = 0;$i -lt $values.Count;$i++){
$sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($values[$i].column)",$values[$i].dataType))) | OUT-NULL
$sqlCmd.Parameters[$i+1].Value = $values[$i].value
}
for($i=0;$i -lt $sqlCmd.Parameters.count;$i++){
if($sqlCmd.Parameters[$i].Value -eq $null){
$sqlCmd.Parameters[$i].Value = [System.DBNull]::Value
}
}
if($sqlCmd.ExecuteNonQuery() -ne 1){
Throw $_
}
}
An example of how to connect from an Azure Function before using this command:
using namespace System.Data.SqlClient
using namespace System.Net
$msi_authenticationResult = Invoke-RestMethod -Method Get -Headers @{'Secret' = $env:MSI_SECRET} -Uri ($env:MSI_ENDPOINT +'?resource=https://database.windows.net/&api-version=2017-09-01')
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Data Source = yourserver.database.windows.net; Initial Catalog = yourdatabase"
$sqlConn.AccessToken = $msi_authenticationResult.access_token
$sqlConn.Open()
