149 lines
4.8 KiB
PowerShell
149 lines
4.8 KiB
PowerShell
function Invoke-NonQueryByConnectionString {
|
|
|
|
<#
|
|
.SYNOPSIS
|
|
Executes a T-SQL script using a given connection string, using the ExecuteNonQuery method
|
|
|
|
.DESCRIPTION
|
|
Executes a T-SQL script using a given connection string, using the ExecuteNonQuery method
|
|
|
|
.PARAMETER ConnectionString
|
|
The database connection string.
|
|
|
|
.PARAMETER QueryString
|
|
The query to execute
|
|
|
|
.PARAMETER SqlInputParameters
|
|
An optional hashtable array of input parameters which may be passed to the script. Hashtable must be supplied with properties Name and Value
|
|
|
|
.PARAMETER CommandTimeout
|
|
The query timeout in seconds
|
|
|
|
.PARAMETER WhatIf
|
|
Run the query in a wrapped rollback transaction
|
|
|
|
.PARAMETER Force
|
|
Allow potentially destructive queries to be run. Please do not use this except with approval from leadership.
|
|
|
|
.EXAMPLE
|
|
$params = @( { Name = "@IsStolen"; Value = "1"; })
|
|
Invoke-NonQueryByConnectionString -ConnectionString "data source=foo.local;Integrated Security=SSPI;Database=SuperMan2" -Query "SELECT * FROM dbo.StolenPennies WHERE IsStolen = @IsStolen" -SqlInputParameters $params
|
|
#>
|
|
|
|
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidAssignmentToAutomaticVariable', '', Justification = 'Event Handler Requirement')]
|
|
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSReviewUnusedParameter', '', Justification = 'Sender is a Required Event Handler Parameter')]
|
|
[CmdletBinding()]
|
|
param (
|
|
[Parameter(Mandatory = $true)]
|
|
[string]$ConnectionString,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[Alias('Sql')]
|
|
[Alias('Query')]
|
|
[string]$QueryString,
|
|
|
|
[Parameter(Mandatory = $false)]
|
|
[hashtable[]]$SqlInputParameters = @(),
|
|
|
|
[Parameter(Mandatory = $false)]
|
|
[int]$CommandTimeout = 30,
|
|
|
|
[Parameter()]
|
|
[switch]$WhatIf,
|
|
|
|
[Parameter()]
|
|
[switch]$Force
|
|
)
|
|
|
|
$logLead = Get-LogLeadName
|
|
|
|
if (-not $Force) {
|
|
if (-not (Assert-SqlQueryIsSafe -Query $QueryString)) {
|
|
Write-Warning "$logLead : Script was not run. Please resolve errors and re-run"
|
|
return
|
|
}
|
|
}
|
|
|
|
if ($WhatIf) {
|
|
$QueryString = ConvertTo-WhatIfQuery -Query $QueryString
|
|
}
|
|
|
|
$conn = New-Object System.Data.SqlClient.SqlConnection
|
|
|
|
try {
|
|
|
|
$conStrBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($ConnectionString)
|
|
|
|
} catch [System.Management.Automation.MethodException] {
|
|
|
|
Write-Warning "$logLead : Provided connection string [$ConnectionString] is invalid. Execution cannot continue."
|
|
return $null
|
|
}
|
|
|
|
if (-not (Confirm-DatabaseAccess -ConnectionString $conStrBuilder.ToString())) {
|
|
|
|
Write-Warning "$logLead : Unable to connect to the database specified in connection string [$ConnectionString]. Execution cannot continue."
|
|
return
|
|
}
|
|
|
|
$conn.ConnectionString = $conStrBuilder.ToString()
|
|
Write-Verbose ("$logLead : Connecting to database with connection string {0}" -f $conStrBuilder.ToString())
|
|
|
|
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
|
|
|
|
param($sender, $eventArgs)
|
|
|
|
# Handle if it's an error or informational message
|
|
$hasErrors = ($null-ne $eventArgs.Errors -and $eventArgs.Count -gt 0)
|
|
$consoleColor = ([System.ConsoleColor]::Gray)
|
|
|
|
if ($hasErrors) {
|
|
|
|
# We only want to write this as an "error" if the severity is greater than 10. This aligns with SQL/SSMS
|
|
$maxErrorLevel = ($eventArgs.Errors | Measure-Object -Property Class -Maximum).Maximum
|
|
if ($maxErrorLevel -gt 10) {
|
|
|
|
$consoleColor = ([System.ConsoleColor]::Red)
|
|
}
|
|
}
|
|
|
|
Write-Host $eventArgs.Message -ForegroundColor $consoleColor
|
|
};
|
|
|
|
try {
|
|
|
|
$conn.add_InfoMessage($handler);
|
|
$conn.FireInfoMessageEventOnUserErrors = $true
|
|
|
|
$conn.Open()
|
|
$query = New-Object System.Data.SqlClient.SqlCommand($QueryString, $conn)
|
|
$query.CommandTimeout = $CommandTimeout
|
|
|
|
if (-NOT (Test-IsCollectionNullOrEmpty $SqlInputParameters)) {
|
|
|
|
foreach ($parameter in $SqlInputParameters) {
|
|
|
|
Write-Verbose "$logLead : Adding parameter [$($parameter.Name)] with value [$($parameter.Value)] to the SqlCommand"
|
|
$query.Parameters.AddWithValue($parameter.Name, $parameter.Value) | Out-Null
|
|
}
|
|
}
|
|
|
|
$query.ExecuteNonQuery() | Out-Null
|
|
|
|
} catch {
|
|
|
|
Write-Warning "$logLead : An exception occurred while trying to execute the specified query against the database"
|
|
Write-Warning "$logLead : $($_.ToString())"
|
|
Write-Warning "$logLead : $($_.ScriptStackTrace)"
|
|
return $null
|
|
|
|
} finally {
|
|
|
|
if (($null -ne $conn) -and ($conn.State -ne [System.Data.ConnectionState]::Closed)) {
|
|
|
|
$conn.Close()
|
|
}
|
|
|
|
$conn = $null
|
|
}
|
|
} |