public/Invoke-SqlQuery.ps1
|
Function Invoke-SqlQuery { <# .SYNOPSIS Executes a SQL query. .DESCRIPTION Executes a SQL query and returns the result in a format for easy manipulation afterwards. This function is not intended for DDL statements. .EXAMPLE Invoke-SqlQuery -ServerInstance '.' -Database master -Query 'select * from sys.tables FOR JSON PATH' -QueryResultFormat JSON Invoke-SqlQuery -ServerInstance '.' -Database master -Query "select * from sys.tables FOR XML AUTO, ROOT('MyRoot')" -QueryResultFormat XML Invoke-SqlQuery -ServerInstance '.' -Database master -Query "select count(*) from sys.tables " -QueryResultFormat Scalar Invoke-SqlQuery -ServerInstance '.' -Database master -Query "select * from sys.tables " -QueryResultFormat Rows | Format-Table -AutoSize .PARAMETER Query The SQL query to execute. .PARAMETER ServerInstance The SQL server instance to execute the query against. .PARAMETER Database The SQL datanase to execute the query in. .PARAMETER QueryTimeout A timeout for the query. .PARAMETER QueryResultFormat Specifies the expected output of the query. .INPUTS TO DO .OUTPUTS Scalar: returns the scalar result, and keeps the datatype returned from the query. JSON: returns the result as a json object. XML: returns the result as a xml object. Rows: Returns each row as a pscustomobject .LINK https://github.com/DennisWagner/SQLServerDevOpsTools .NOTES Written by (c) Dennis Wagner Kristensen, 2021 https://github.com/DennisWagner/SQLServerDevOpsTools This PowerShell script is released under the MIT license http://www.opensource.org/licenses/MIT #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)]$Query, [Parameter(Mandatory=$true)]$ServerInstance, [Parameter(Mandatory=$true)]$Database, [Parameter(Mandatory=$false)]$QueryTimeout, [Parameter(Mandatory=$false)] [ValidateSet("Rows","Scalar","JSON","XML")] $QueryResultFormat = "Rows" ) BEGIN { } PROCESS { $DataTable = New-Object System.Data.DataTable $Connection = New-Object System.Data.SQLClient.SQLConnection $Connection.ConnectionString = "server='$ServerInstance';database='$Database';trusted_connection=true;" $Connection.Open() $Command = New-Object System.Data.SQLClient.SQLCommand $Command.Connection = $Connection $Command.CommandText = $Query If ($QueryTimeout) { $Command.CommandTimeout = $QueryTimeout } $Reader = $Command.ExecuteReader() If (($QueryResultFormat -eq "JSON") -or ($QueryResultFormat -eq "XML")) { $DataTable.Load($Reader) If ($DataTable.Columns.Count -gt 1) { Throw "Query results of format: $QueryResultFormat may only return a single column. Number of columns returned: $($DataTable.Columns.Count)" } # When using for xml or for json, the ExecuteReader splits the result into multiple records of 2033 characters # https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executereader?view=dotnet-plat-ext-5.0 $MergedString = "" ForEach ($record in $DataTable.Rows) { $MergedString += $record.ItemArray[0].ToString() } If ($QueryResultFormat -eq "JSON" ) { Try { $Json = ConvertFrom-Json $MergedString } Catch { Throw "The returned result did not contain valid JSON." } Write-Output $Json } ElseIf ($QueryResultFormat -eq "XML" ) { Try { $Xml = [xml]$MergedString } Catch { Throw "The returned result did not contain valid XML. Perhaps it's missing a root element? Error message: $($_.Exception.Message)" } Write-Output $Xml } } ElseIf ($QueryResultFormat -eq "Scalar" ) { $DataTable.Load($Reader) # validate result If ($DataTable.Rows.Count -gt 1) { Throw "Query results of format: $QueryResultFormat may only return a single row. Number of rows returned: $($DataTable.Rows.Count)" } ElseIf ($DataTable.Columns.Count -gt 1) { Throw "Query results of format: $QueryResultFormat may only return a single column. Number of columns returned: $($DataTable.Columns.Count)" } $Scalar = $DataTable.Rows[0].ItemArray[0] Write-Output $Scalar } ElseIf ($QueryResultFormat -eq "Rows" ) { # do not use the easier data table, to prevent looping through the rows twice - marginally faste than using a data table While ($Reader.Read()) { # convert each row to a pscustomobject - use an ordered hashtable to preserve the column order $RowObject = [ordered]@{} For ($i = 0; $i -lt $Reader.FieldCount; $i++) { $ColumnName = $Reader.GetName($i) $RowObject.add($ColumnName, $Reader.GetValue($i)) } Write-Output (New-Object -TypeName PSOBJECT -Property $RowObject ) } } } END { $Connection.Close() } } |