Public/New-SqlQuery.ps1
|
function New-SqlQuery { [CmdletBinding()] param ( [Object] $SqlSettings, [Object] $Object, [Object] $TableMapping ) $ArraySQLQueries = New-ArrayList if ($Object -ne $null) { ## Added fields to know when event was added to SQL and by WHO (in this case TaskS Scheduler User) ## Only adding when $Object exists foreach ($O in $Object) { $ArrayMain = New-ArrayList $ArrayKeys = New-ArrayList $ArrayValues = New-ArrayList if (-not $O.AddedWhen) { Add-Member -InputObject $O -MemberType NoteProperty -Name "AddedWhen" -Value (Get-Date) } if (-not $O.AddedWho) { Add-Member -InputObject $O -MemberType NoteProperty -Name "AddedWho" -Value ($Env:USERNAME) } foreach ($E in $O.PSObject.Properties) { $FieldName = $E.Name $FieldValue = $E.Value foreach ($MapKey in $TableMapping.Keys) { if ($FieldName -eq $MapKey) { $MapValue = $TableMapping.$MapKey $MapValueSplit = $MapValue -Split ',' if ($FieldValue -is [DateTime]) { $FieldValue = Get-Date $FieldValue -Format "yyyy-MM-dd HH:mm:ss" } if ($FieldValue -like "*'*") { $FieldValue = $FieldValue -Replace "'", "''" } #if ($FieldValue -eq '') { $FieldValue = 'NULL' } Add-ToArray -List $ArrayKeys -Element "[$($MapValueSplit[0])]" if ([string]::IsNullOrWhiteSpace($FieldValue)) { #if ($FieldValue -eq '') { Add-ToArray -List $ArrayValues -Element "NULL" } else { Add-ToArray -List $ArrayValues -Element "'$FieldValue'" } } } } if ($ArrayKeys) { Add-ToArray -List $ArrayMain -Element "INSERT INTO $($SqlSettings.SqlTable) (" Add-ToArray -List $ArrayMain -Element ($ArrayKeys -join ',') Add-ToArray -List $ArrayMain -Element ') VALUES (' Add-ToArray -List $ArrayMain -Element ($ArrayValues -join ',') Add-ToArray -List $ArrayMain -Element ')' Add-ToArray -List $ArraySQLQueries -Element ([string] ($ArrayMain) -replace "`n", "" -replace "`r", "") } } } # Write-Verbose "SQLQuery: $SqlQuery" return $ArraySQLQueries } |